

Why is that?









".NET Framework Class Library - SqlCommand.ExecuteNonQuery Method"


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:




"SQL Server 2008 Books Online (December 2008) - SET NOCOUNT (Transact-SQL)"


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


-- create procedure for insert

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

            set nocount on

            insert into ENQ values (@pNewName)



-- 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";



                using (SqlConnection con = new SqlConnection(cs))



                    // 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());





            catch (Exception ex)






And run it. The output should be:


1  returned by insert into ENQ values ('Chuck')

-1 returned by InsertToENQ







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.




CREATE PROCEDURE dbo.StoredProcedure2


           @parameter1 int = 5,

           @parameter2 datatype OUTPUT



           /* SET NOCOUNT ON */





CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

            -- Add the parameters for the stored procedure here





            -- SET NOCOUNT ON added to prevent extra result sets from

            -- interfering with SELECT statements.

            SET NOCOUNT ON;


            -- Insert statements for procedure here
