Home > SQL Server > ExecuteNonQuery always returns -1

ExecuteNonQuery always returns -1

In a web-application I used checking the affected rows in each transaction to avoid unexpected situations which data can be changed by other sources. This is the case that your application is not the only one which reads and writes to the database, and using lock on the records is not possible (matter of performance or personal preference).

However, after installing in the production environment we found that application does not work. The ADO ExecuteNonQuery always returns -1 instead of actual affected records count.

Further search showed that there is a T-SQL command that can disable returned affected record counts. According to Microsoft:

SET NOCOUNT { ON | OFF } : Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results” Reference: http://msdn.microsoft.com/en-us/library/aa259204(SQL.80).aspx

The NOCOUNT command can be used in Store Procedures and Triggers, but the application did not have either of them, so it must be something else.

Finally I found the cause of the problem in the following forum post:

http://www.developersdex.com/sql/message.asp?p=581&ID=%3C%23Guu6msqIHA.3568%40TK2MSFTNGP04.phx.gbl%3E

Default NoCount parameter is in SERVER PROPERTIES > CONNECTIONS > DEFAULT CONNECTION OPTIONS LIST

The following screen shot is from SQL Server 2000. To fix the problem and make ExecuteNonQuery returns the correct affected records number be sure that the NoCount is not ticked.

image

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: