I have a table table1 with fields id(int), name(nchar), grade(real).
The following code isn't working. There are no errors or warnings. The code executes well but the number of affected rows = 0.
MsSql Server
sqlConnection1.Open();
SqlCommand cmd = new SqlCommand("Delete from [table1] where [id] = 1", sqlConnection1);
int c = cmd.ExecuteNonQuery();
sqlConnection1.Close();
All other queries are working well.
A slight expansion of what others have already asked. Are you certain that there are records to be deleted in your target table? Moreover, are you certain you are getting the table from the right database? It's possible the default is tempdb, for instance, and that just happens to have a table with the target name and with an id column.
First do a select from the SQL prompt to insure there are items of the type you are looking for:
SELECT TOP 10 * FROM [database].[schema].[table1] WHERE [id] = 1
If that provides results, try changing your command to explicitly state the database and schema as well:
DELETE FROM [database].[schema].[table1] WHERE [id] = 1
Thoughts:
my guess would be the second... the number is after triggers have been taken into account, and is the number of rows from the last operation.
ON DELETE RESTRICT foreign keys.I know this might sound silly, but is there data in the call with an ID of 1? Can you see it executing via SQL Profiler? What happens if you execute it via SSMS?
Does the query work when you run it in Sql Management studio?
Apart from Marc Gravell's comment about triggers you should also check, if there are foreign key constraints with ON DELETE RESTRICT` in place (and the error message somehow disappears before getting to you ...)
