ExecuteNonQuery returns -1 even though the execution was successful

Question

I'm running an asp.net 2.0 web application. Suddenly this morning, ExecuteNonQuery started returning -1, even though the commands in the SP that ExecuteNonQuery is running, are executing (I see items inserted and updated in the DB), and there is no exception thrown.

This happens only when I am connected to our production DB. When I'm connected to our development DB they return the correct number of rows affected.

Also, interestingly enough, ExecuteDataSet doesn't have problems - it returns DataSets beautifully.

So it doesn't seem like a connection problem. But then, what else could it be?

Answer 1

-1 means "No rows effected".
SELECT statement will also return -1.

Check the effect of the SP on dev and live environments.

Taken from SqlCommand::ExecuteNonQuery Method

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Answer 2

I encounter almost same problem (in Postgresql though) with NpgsqlDataAdapter, the Update method always returns 1.

Perhaps your connection string to your development db is different from your production db. Or if there's really no difference between their connection strings, perhaps your production db's service pack is different from your development db.

Answer 3

You can also check the procedure to see if there's this line of code in there -

SET NOCOUNT ON

This will cause the ExecuteNonQuery to always return -1.