SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete


Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.


Why is that?


 


Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.


When checking the documentation we can see that there are some conditions that return -1.


 


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.  


 


“.NET Framework Class Library – SqlCommand.ExecuteNonQuery Method”


http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx


 


Now, sometimes all you do is an INSERT but that still returns -1, how come?


 


I would say the most common reason is that the INSERT is done via stored procedures and that the SET NOCOUNT options is set to ON.


What this option does is the following:


 


Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.


 


“SQL Server 2008 Books Online (December 2008) – SET NOCOUNT (Transact-SQL)”


http://msdn.microsoft.com/en-us/library/ms189837.aspx


 


So basically, if the SQL does not return the number of affected rows, how could the SqlCommand.ExecuteNonQuery know? Well, it doesn’t and you’ll have your -1.


 


Being a fan of exemplifying everything, I’ll do it here as well.


Startup SSMS and create a table and a stored procedure in SQL Server, like so:


 


create table ENQ (id int identity, aName nvarchar(20))


insert into ENQ values (‘John’)


select * from ENQ


go


— create procedure for insert


create procedure InsertToENQ (@pNewName nvarchar(20)) as


            set nocount on


            insert into ENQ values (@pNewName)


return


go


— Insert a name vis stored proce to see that all works as expected.


exec InsertToENQ ‘Peter’


select * from ENQ


 


— drop table ENQ


— drop procedure InsertToENQ


 


You know have to rows in the database, John and Peter.


Create a new C# console application in Visual Studio and replace the Main method with this:


 


        static void Main(string[] args)


        {


            String cs = @”Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI”;


            try


                    {  


                using (SqlConnection con = new SqlConnection(cs))


                {


                    con.Open();


                    // Using sql


                    SqlCommand cmd = new SqlCommand(“insert into ENQ values (‘Chuck’)”, con);


                    int retVal = cmd.ExecuteNonQuery();


                    Console.WriteLine(“{0, -2} returned by {1}”, retVal, cmd.CommandText);


 


                    // Using sp


                    cmd.CommandText = “InsertToENQ”;


                    cmd.CommandType = System.Data.CommandType.StoredProcedure;


                    cmd.Parameters.AddWithValue(“@pNewName”, “Robert”);


                    retVal = cmd.ExecuteNonQuery();


                    Console.WriteLine(“{0, -2} returned by {1}\n”, retVal, cmd.CommandText);


 


                    // Print rows to verify that both cmd’s actually inserted the rows.


                    cmd.CommandText = “select aName from ENQ”;


                    cmd.CommandType = System.Data.CommandType.Text;


                    SqlDataReader rdr = cmd.ExecuteReader();


                    while (rdr.Read())


                    {


                        Console.WriteLine(“{0}”, rdr[0].ToString());


                    }


                    con.Close();


                }


            }


            catch (Exception ex)


            {


                Console.WriteLine(ex);


            }


        }


 


And run it. The output should be:


 


1  returned by insert into ENQ values (‘Chuck’)


-1 returned by InsertToENQ


 


John


Peter


Chuck


Robert


 


So here we can see that the two added rows are there (Chuck, Robert) but the stored procedure call returned -1 for the number of affected rows because the SET NOCOUNT option is set to ON.


 


You can set the SET NOCOUNT directly in the command text, then it makes more sense why it does not return any affected rows:


This will return -1 as well. But it is more obvious here, if you disable row counting, how could you count the rows….


 


SqlCommand cmd = new SqlCommand(“set nocount on; insert into ENQ values (‘Chuck’)”, con);


int retVal = cmd.ExecuteNonQuery();


 


I believe that some of the confusion comes from stored procedures templates being generated differently depending on from where you create them.


If you create a new stored procedure from Visual Studio, the SET NOCOUNT is OFF, however, if you create it from SQL Server Management Studio, it is set to ON.


 


VISUAL STUDIO DEFAULT:


 


CREATE PROCEDURE dbo.StoredProcedure2


           /*(


           @parameter1 int = 5,


           @parameter2 datatype OUTPUT


           )*/


AS


           /* SET NOCOUNT ON */


           RETURN


 


SSMS DEFAULT:


 


CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>


            — Add the parameters for the stored procedure here


            <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,


            <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>


AS


BEGIN


            — SET NOCOUNT ON added to prevent extra result sets from


            — interfering with SELECT statements.


            SET NOCOUNT ON;


 


            — Insert statements for procedure here


            SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>


END


 


HTH


 

Comments (4)

  1. Rizwan Gazi says:

    Right dear, I am inserting data via Stored Procedure and I have set SET NOCOUNT ON. I was very confused for -1 value of ExecuteNonQuery(). Your answer helped. Thanks a lot.

  2. Anonymous says:

    Thanks for this explanation. Helped a lot.

  3. Jodes says:

    I know this is an old article but it's just saved my day. Thank you for such a crystal clear explanation.

  4. Srinivas says:

    Thank you very much ! This helped me a lot.It didn't even occurred to me that NO COUNT ON is causing the problem.