The Furious Bear The Furious Bear - 4 months ago 13
SQL Question

ExecuteNonQuery() returning -1

I am calling a SQL Server stored procedure from C#, consisting of a straightforward INSERT statement.
The ExecuteNonQuery() returns the number of rows affected by the statement OK unless there is no update, in which case I'm getting -1.

This doesn't make much sense to me; how can -1 rows be affected?

Answer

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

Source

So, since you are at a dead-end with turning NOCOUNT off, try this:

Use @@ROWCOUNT with ExecuteNonQuery

At the end of your SP, execute SELECT @@ROWCOUNT and use ExecuteScalar instead of ExecuteNonQuery. This will return the result that you desire.

Comments