Jonas Stawski

Everything .NET and More

ExecuteNonQuery Always Returns -1

Whenever you want to execute a sql statement that shouldn't return a value or a record set the ExecuteNonQuery should be used. So if you want to run an update, delete, or insert statement, you should use the ExecuteNonQuery. ExecuteNonQuery returns the number of rows affected by the statement. This sounds very nice, but whenever you use the SQL Server 2005 IDE or Visual Studio to create a Stored Procedure it adds a small line that ruins everything. That line is: SET NOCOUNT ON; This line turns on the NOCOUNT feature of SQL Server, which "Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results" and therefore it makes the SP always to return -1 when called from the application (in my case a web app).

In conclusion, remove that line from your SP and you will now get a value indicating the number of rows affected by the statement.

Happy Programming!

Comments (6) -

Oh, that should be obvious! - thanks for this, it solved my issue.

Reply

I'm glad it helped you

Reply

I had the same problem.
i tought about return @@rowcount but i said there must be a easier way.
Thanks.

Reply

This solved my problem, thx m8

Reply

Thanks a lot - saved me a headache.

Reply

Spent half a day pulling my hair out on this one.  Thanks for the info.

Reply

Add comment

biuquote
Loading