CODEWITHSUNDEEP
c#sqloledb
Noam650
Noam650

Reputation: 113

DELETE row from different tables using SQL and C#

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.

Upvotes: 0

Views: 2110

Answers (2)

D Stanley
D Stanley

Reputation: 152501

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.

Upvotes: 1

Davio
Davio

Reputation: 4737

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.

Upvotes: 0

Related Questions