October 04, 2006
ExecuteNonQuery Always Returns -1
My blog has moved. Please go to: http://jstawski.com/archive/2006/10/04/ExecuteNonQuery-Always-Returns-_2D00_1.aspx
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.
11:55 AM | Permalink
Oh, that should be obvious! - thanks for this, it solved my issue.
Posted by: bdragon | Oct 13, 2006 12:18:56 PM
I'm glad it helped you
Posted by: Jonas | Oct 13, 2006 12:22:31 PM
I had the same problem i search a lot and i found my answer here.
i didn't think to check the SP i tought it was my c# code.
Posted by: Patrick | Oct 23, 2006 1:03:55 PM
That simply solved my issue. Great.. thank you.
Posted by: Andrew | Nov 2, 2006 11:37:26 AM
I'm having the same problem with the ExecuteNonQuery() Always -1, the SP had been tested and executes fine, but when executing via the Windows App, it still returns -1, the stored proc was written by someone else, so somebody with minimal knowledge of SP's what should i do, It was said to remove the Line "SET NOCOUNT ON" from the stored proc, Hows does this line get added...?
Posted by: Jaques | Nov 16, 2006 2:00:53 AM
It looks like the NOCOUNT is turned on at the server level, either turn it off at the SP or at the server.
Don't forget this blog has moved to http://www.jstawski.com
Posted by: Jonas | Nov 16, 2006 8:43:53 AM
"Oh, that should be obvious!" Exactly. It should be documeneted in ExecuteNonQuery()
but it's not!
Posted by: TomB | Feb 15, 2007 10:08:51 AM
Good catch! I've been mumbling not-so-nice words for the past hour. Thanks for sharing!
Posted by: Thomas | Mar 9, 2007 6:33:20 PM
If you still want to use SET NOCOUNT ON you can execute SELECT @@ROWCOUNT inside you stored procedure and use ExecuteScalar instead of ExecuteNonQuery
Posted by: João Almeida | Apr 10, 2007 8:24:45 AM
fixed my problem. really glad this blog exists. thank you.
Posted by: monkey | Sep 2, 2007 8:50:25 PM
This is a life saver, otherwise it would have taken my lot of time to find this out. Good work. Keep it up.
Posted by: Ritesh Manglani | Jan 8, 2008 2:09:14 AM
i have commented the line SET NOCOUNT ON; in SP.it is still returinh -1 it has"nt solve my issue for sure.
Posted by: hassan | Jul 29, 2008 12:21:48 AM
Can you tell me the solution for it.
here is my procedure which is always returing -1
ALTER procedure [dbo].[insertstudent]
@std_name varchar(50) ,
--SET NOCOUNT Off;
insert into student(studentname,degree) values(@std_name,@degree)
Posted by: hassan | Jul 29, 2008 12:23:59 AM
Oh great !!
by using this suggestion, i have solved the issue.
Posted by: Suresh | Aug 19, 2008 5:46:31 AM
This is all I could find about why it was returning -1. Good find.
Posted by: Kyle | Oct 30, 2008 6:40:00 PM
Posted by: jjkkjk | Nov 30, 2008 2:40:40 PM
I was haunted with this for a couple of days,,This is really useful..Thanks a lot for sharing...
Posted by: Kiran Raveendranath | Dec 8, 2008 2:21:54 AM
Wow - sure enough! Thank you for the tip!
Posted by: Ted | Jan 23, 2009 1:57:18 AM
i have commented the line SET NOCOUNT ON; in SP.it is still returinh -1 it has"nt solve my issue for sure. please solve my problem.
Posted by: Arpit Shrivastava | Feb 2, 2009 12:19:16 AM
I've deleted SET NOCOUNT ON row from the code...then also return value is -1.
Then i wrote SET NOCOUNT OFF then also i was getting -1 only.
Posted by: sagar | Mar 17, 2009 1:37:44 AM
Muchas gracias!!! Me salvo
Posted by: Elkin | Mar 19, 2009 6:42:09 PM
Need more info about Visionary manga ? You are welcome here http://anime.goodnanoav.com .
Posted by: Greableacerie | Jun 9, 2009 1:24:27 PM
Thaaaank you.... that problem was mindboggling..me beginner off course, so you can imagine
Posted by: yugimoto2004 | Sep 15, 2009 7:04:49 PM
Thanks a lot man....
Posted by: VJ | Oct 27, 2009 11:40:43 AM
It helped me to save lots of time.
Posted by: anil indorkar | Jan 18, 2010 7:11:42 AM
The comments to this entry are closed.