SqlCommand.ExecuteNonQuery() always returns 0

Question

I'm trying to process an UPDATE statement via my c# code. I use the following...

using (SqlCommand cmd = new SqlCommand(query, sqlConn))
{
    cmd.Parameters.AddWithValue("@CUSTOMER", intCustomer);
    cmd.Parameters.AddWithValue("@CONDITION", strCondition);
    cmd.Parameters.AddWithValue("@BOOK", strBook);
    cmd.Parameters.AddWithValue("@PAGE", strPage);
    cmd.Parameters.AddWithValue("@ENDPAGE", strEndPage);

    System.Diagnostics.Debug.WriteLine("Expanded query: " + 
        query.ExpandSqlQuery(cmd.Parameters));

    int affectedRows = cmd.ExecuteNonQuery();
    System.Diagnostics.Debug.WriteLine("Number of rows affected: " + affectedRows);
}

I have tried to do this with and without params just for debugging and always get a return value of 0 for some reason.

I have made a small extension method that expands the params to show me the actual query (without variables)...

public static string ExpandSqlQuery(this String input, SqlParameterCollection sqlParams)
{
    string results = input;

    foreach (SqlParameter p in sqlParams)
        results = results.Replace(p.ParameterName, p.Value.ToString());

    return results;
}

... that I call right before executing the query to see what will be ran.

System.Diagnostics.Debug.WriteLine("Expanded query: " +
    query.ExpandSqlQuery(cmd.Parameters));

I then take that SAME EXACT query that returned 0 affected rows in c# and manually run it in Microsoft SQL Server Management Studio to have it tell me 1 row was affected!

The query with params is...

UPDATE
  BookList
SET
  Overdue=2
WHERE
  Customer=@CUSTOMER
  and Condition='@CONDITION'
  and Book='@BOOK'
  and Page='@PAGE'
  and EndPage='@ENDPAGE'
  and Overdue=1;

UPDATE
  BookInfo
SET
  Finished=0
WHERE
  Customer=@CUSTOMER
  and Condition='@CONDITION'
  and Book='@BOOK';

The query expanded is...

UPDATE
  BookList
SET
  Overdue=2
WHERE
  Customer=85
  and Condition='old'
  and Book='00103'
  and Page='00304'
  and EndPage='00304'
  and Overdue=1;

UPDATE
  BookInfo
SET
  Finished=0
WHERE
  Customer=85
  and Condition='old'
  and Book='00103';

Any ideas how I can go about debugging this issue?

Double check your connection strings, make sure you're connected to the same SQL Database? I only suggest this because I've done it before... — Adam Schiavone, Oct 05 '17 at 13:07
Are you able to run SQL Server Profiler against the server to verify that the query you're seeing from your C# code is the same thing that is being executing on the server? — Becca Dee, Oct 05 '17 at 13:07
I thought someone would ask me for my query... I didn't think it was needed as I said in the end that the EXACT same query works fine when I use it manually. But I'll post it shortly in an updated question. ;) — Arvo Bowen, Oct 05 '17 at 13:08
It looks like you are doing a replace which is an Update Query. Updates will return zero if the item is not in the database. So the solution is to do an Insert. Insert will return zero if the item is already in the database. So normally when you do an Insert and get zero result you then do an update. When you do an update and get zero you then do an insert. — jdweng, Oct 05 '17 at 13:09
@Don01001100 great idea, didn't think to test that just yet, I'll give that a shot. — Arvo Bowen, Oct 05 '17 at 13:21
@AdamSchiavone I did go through and make sure it was using the correct DB and connection string by stepping through it. — Arvo Bowen, Oct 05 '17 at 13:22
@Niladri updated question has been posted. Not sure why I received a downvote on my question... It's very clear and understandable. I also received no negative feedback on the question other than you wanted to see the query. :/ — Arvo Bowen, Oct 05 '17 at 13:24
@ArvoBowen, let me know if that's the answer that helps you debug the issue. — Becca Dee, Oct 05 '17 at 13:30
@ArvoBowen you are already passing string value to the query so why the `''` around the params `'@BOOK'` — Niladri, Oct 05 '17 at 13:33

score 4 · Accepted Answer · answered Oct 05 '17 at 13:30

You must not put quotes around your parameters. Thus:

UPDATE
  BookList
SET
  Overdue=2
WHERE
  Customer=@CUSTOMER
  and Condition='@CONDITION'
  and Book='@BOOK'
  and Page='@PAGE'
  and EndPage='@ENDPAGE'
  and Overdue=1;

UPDATE
  BookInfo
SET
  Finished=0
WHERE
  Customer=@CUSTOMER
  and Condition='@CONDITION'
  and Book='@BOOK';

should instead be:

UPDATE
  BookList
SET
  Overdue=2
WHERE
  Customer=@CUSTOMER
  and Condition=@CONDITION
  and Book=@BOOK
  and Page=@PAGE
  and EndPage=@ENDPAGE
  and Overdue=1;

UPDATE
  BookInfo
SET
  Finished=0
WHERE
  Customer=@CUSTOMER
  and Condition=@CONDITION
  and Book=@BOOK;

@Niladri touche. ;) mjwills This is perfect and exactly what was causing the issues! Thanks! — Arvo Bowen, Oct 05 '17 at 13:35

SqlCommand.ExecuteNonQuery() always returns 0

1 Answers1