SQL Delete query not working properly

Question

I had a logic error in my sql delete query which would not give any error in visual studio and did not delete the record in the database

Here is a snippet of my code

SqlCommand cmd = new SqlCommand(
   @"DELETE FROM table_name 
     WHERE item_id=" + itmIDs + 
         " AND vendor_id=" + vendIDs + 
         " AND dozen=" + selectedItmDzn + 
         " AND quantity=" + selectedItmQty + 
         " AND total_price=" + selectedItmTotPrc + "",
   con);
cmd.ExecuteNonQuery();

here is my conString

SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=InvenotyBB;Integrated Security=SSPI")

I have confirmed that the other verbs (select, update, etc) work, just not the specific command for delete.

Answer 1

I can almost guarantee your connection string has:

User Instance=true;AttachDbFileName=|Data Directory|...something.mdf;

If this is the case, STOP DOING THAT. The AttachDbFileName feature actually creates a copy of your database file. So the one you have open in Management Studio or Visual Studio is different from the one your application created via the connection string. Your application deletes from the copy, there are no exceptions (because it worked), you refresh the original, and it looks like it didn't work.

See the answer from @marc_s's here:

https://stackoverflow.com/a/7222952/61305

If this isn't it, then I suspect either (a) errors are being ignored due to try/catch somewhere, or (b) your method for checking if the command worked is suspect. For example, if you are relying on a count, and the where clause matches zero rows, then the command worked but it didn't delete anything, therefore the count remains the same.

If neither of those are true, then goto line 1 of my answer. There is no magic here, a delete command will either affect 0 or more rows, or it will return an exception. Anything else can only be explained by improper troubleshooting / debugging.

Answer 2

Given this original code (via my formatting):

SqlCommand cmd = new SqlCommand(
   @"DELETE FROM table_name 
     WHERE item_id=" + itmIDs + 
         " AND vendor_id=" + vendIDs + 
         " AND dozen=" + selectedItmDzn + 
         " AND quantity=" + selectedItmQty + 
         " AND total_price=" + selectedItmTotPrc + "",
   con);
cmd.ExecuteNonQuery();

Let's change this to:

string deleteQuery = 
   @"DELETE FROM table_name 
     WHERE item_id=" + itmIDs + 
         " AND vendor_id=" + vendIDs + 
         " AND dozen=" + selectedItmDzn + 
         " AND quantity=" + selectedItmQty + 
         " AND total_price=" + selectedItmTotPrc + "";
SqlCommand cmd = new SqlCommand(deleteQuery, con); /* set a breakpoint here */
cmd.ExecuteNonQuery();

Set the breakpoint and copy-paste that query to a comment here so we can see it.