DELETE row from different tables using SQL and C#

Question

I'm trying to delete several rows from tblOrderAA and one row from tblProInfo :(look at the picture) enter image description here

Here is the code. The error I get is:

"the record has been deleted"

        private void DeleteFromDataBase()
        {
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Projects_2012\\Project_Noam\\Access\\myProject.accdb";
            OleDbConnection myConnection = new OleDbConnection(connectionString);
            string myDeleteQuery ="DELETE tblOrderAA.*, tblProInfo.*"+
" FROM tblProInfo INNER JOIN tblOrderAA ON tblProInfo.proInfoSerialNum = tblOrderAA.orderAASerialPro" +
" WHERE (((tblProInfo.proInfoScienceName)='"+comboBox1.SelectedItem.ToString()+"'))";

            OleDbCommand myCommand = new OleDbCommand(myDeleteQuery);
            myCommand.Connection = myConnection;
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                MessageBox.Show("success");
            }
            catch (Exception e)
            {
                MessageBox.Show("error in : \n" + e.ToString());
            }

**NOTE:**When I use this code with SELECT instead DELETE it works.

Why not turn on referential integrity and enable cascade deletes in the database? Then you delete the parent record and the child records, relating to that key, are deleted automatically — iCollect.it Ltd
– iCollect.it Ltd, Commented May 15, 2012 at 13:38
Do you have access to SQL server management studio? The cascading of deletes is a property on the relationship between the tables. Easiest way to manage relationships is to add all your tables to a Database Diagram under your database. The relationships are the connecting lines/arrows. Right-click properties on those. — iCollect.it Ltd
– iCollect.it Ltd, Commented May 15, 2012 at 15:18

D Stanley · Accepted Answer · 2012-05-15 13:59:24Z

1

You cannot delete from multiple tables with one query that I know of. If cascading deletes are on, then you can just delete from the Product table and the order records will automatically be deleted. If not, my recommendation would be to run the following queries in order to get the foreign key and delete from each table:

"SELECT proInfoSerialNum "+
" FROM tblProInfo " +
" WHERE (((tblProInfo.proInfoScienceName)='"+comboBox1.SelectedItem.ToString()+"'))"

(store the result in a variable, say serialNum)

// delete the order records first so they are not orphaned
"DELETE tblOrderAA.* "+
" FROM tblOrderAA " +
" WHERE (((tblOrderAA.orderAASerialPro)='"+serialNum.ToString()+"'))"

// Delete the product info
"DELETE tblProInfo.*"+
" FROM tblProInfo " +
" WHERE (((tblProInfo.proInfoSerialNum )='"+serialNum.ToString()+"'))"

Note that I'm leaving out the actual C# code to run these queries, just giving you the SQL to give you an idea how I would do it.

answered May 15, 2012 at 13:59

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EBarr Over a year ago

@Noam650 - you can even glue those two statements together (delimited by a semi-colon) and delete both rows in a single round-trip.

D Stanley Over a year ago

True, there are a lot of alternatives. You could glue all three together in fact. My suggestion was the simplest way to accomplish what he asked for without too much of a paradigm shift. I would also recommend using parameters instead of string concatenation, but that's the OP's problem.

Davio · Accepted Answer · 2012-05-15 13:47:54Z

0

I would guess that cascading deletes are possibly already turned on. Delete only from the main table in your query or turn off the cascade in your database.

answered May 15, 2012 at 13:47

Davio

4,7473 gold badges36 silver badges63 bronze badges

1 Comment

Noam650 Over a year ago

I didnt understand. and still, how do i do this?

Collectives™ on Stack Overflow

DELETE row from different tables using SQL and C#

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related