Delete from two tables?

Question

I am trying to delete two items in two different tables but it is not working.

my code:

protected void btnRemove(object sender, EventArgs e)
{
    conn.Open();
    SqlCommand cmd = new SqlCommand("delete from Car where CarID=" + lstCar.SelectedValue, conn);
    SqlCommand cmd1 = new SqlCommand("delete from Orders where OrderID=" + lstOrders.SelectedValue, conn);
    cmd.ExecuteNonQueryAsync();
    cmd1.ExecuteNonQueryAsync();
    conn.Close();
}

Define "now working" - do you get an exception? No data changes? Note that using parameters reduces the types of errors that can occur by using string concatenation. — D Stanley, Oct 26 '15 at 12:53
You might also try using the non-`Async` versions since you close the connection immediately afterwards. — D Stanley, Oct 26 '15 at 12:54
Possible duplicate of [How to run multiple SQL command in single SQL connection?](http://stackoverflow.com/questions/13677318/how-to-run-multiple-sql-command-in-single-sql-connection) — Pawel Maga, Oct 26 '15 at 12:54
Could you please provide more specific information about the error you get. "Not working" can be said by the user, not by the developer... — Gnqz, Oct 26 '15 at 12:58
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Oct 26 '15 at 12:58
This is like taking your car to the mechanic and telling them it doesn't work. When they ask what is wrong you say, it just isn't working. My response would be, I just can't help you then. — Sean Lange, Oct 26 '15 at 13:58

score 2 · Answer 1 · answered Oct 26 '15 at 12:59

2

Use parameters and DO NOT concatenate values into your SQL, Try something like.....

protected void btnRemove(object sender, EventArgs e)
{
    conn.Open();

    SqlCommand cmd = new SqlCommand("delete from Car where CarID= @CarID", conn);
    cmd.Parameters.AddWithValue("@CarID", lstCar.SelectedValue);

    SqlCommand cmd1 = new SqlCommand("delete from Orders where OrderID= @OrderID" , conn);
    cmd1.Parameters.AddWithValue("@OrderID", lstOrders.SelectedValue);

    cmd.ExecuteNonQuery();
    cmd1.ExecuteNonQuery();

    conn.Close();
}

answered Oct 26 '15 at 12:59

M.Ali

67,945
13
101
127

I got this error message when I use your code: An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code Additional information: The DELETE statement conflicted with the REFERENCE constraint – abc Oct 26 '15 at 13:03
The error you have mentioned has nothing to do the with the code in my answer but everything to do with a Foreign Key constraint in your database. – M.Ali Oct 26 '15 at 16:29

score 1 · Answer 2 · answered Oct 26 '15 at 13:04

Something like that

protected void btnRemove(object sender, EventArgs e) {
  // Do not use external connection, create your own instead 
  // Put IDisposable into using
  using (SqlConnection c = new SqlConnection(YourConnectionString)) {
    c.Open();

    // Make SQL Readable  
    String sql =
      @"delete 
          from Car
         where CarID = @prm_CarId; -- Note ';'

        delete 
          from Orders 
         where OrderID = @prm_OrderId";  

    // Put IDisposable into using
    using (SqlCommand cmd = new SqlCommand(sql, c)) {
      // use parameters
      cmd.Parameters.AddWithValue("@prm_CarId", lstCar.SelectedValue); 
      cmd.Parameters.AddWithValue("@prm_OrderId", lstOrders.SelectedValue);    

      // Your error:
      // No "Async"!: wait for query completion and only then close the connection
      // ... or use await 
      cmd.ExecuteNonQuery(); 
    }
  }
}

score 0 · Answer 3 · answered Oct 26 '15 at 13:56

I fixed it.

conn.Open();
            string sql1 = "delete from Orders where OrderID='" + lstOrders.SelectedValue + "'";
            SqlCommand cmd1 = new SqlCommand(sql1, conn);
            cmd1.ExecuteNonQuery();
            string sql = "Delete from Car where CarID='" + lstCar.SelectedValue + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();

Delete from two tables?

3 Answers3