ExecuteNonQuery always returns -1 RRS feed

  • Question

  •  Ladies and Gents,

    I have a question that is bugging the stuffing out of me.  I am executing a ExecuteNonQuery but I always get back -1.  I know the record exists and so does the db since it blows up on the insert. 

    Please give me a hand.


    1 using (SqlConnection sqlcon = new SqlConnection(connString))  
    2                 {  
    3                     using (SqlCommand cmd = new SqlCommand(cmdString, sqlcon))  
    4                     {  
    5                         sqlcon.Open();  
    6                         int resp = cmd.ExecuteNonQuery();  
    7                         if (resp != -1)  
    8                         {  
    9                             //found something  


    Thanks in Advance.


    Tuesday, January 27, 2009 12:18 AM

All replies

  • Hi John
    Not easy to tell without the sqlcommand, but one guess is that you are using a stored procedure that does an insert.
    A new stored procedure by default has NOCOUNT set to ON, this will cause ExecutNonQuery to return -1 even if a row is inserted to the table.
    "SQL Server 2008 Books Online (December 2008) - SET NOCOUNT (Transact-SQL)"
    Let me show by example, first create a table and a stored procedure that inserts a row to that table:

    create table TempTable (id int identity, aname nvarchar(10))
    create procedure InsertToTempTable as
     set nocount on;
     insert into TempTable values ('abcde')
    execute InsertToTempTable
    select * from TempTable

    Then the following C# will return -1 for the ExecuteNonQuery but a row will be inserted.

                string cs = @"Data Source=<server>;Integrated Security=SSPI;Initial Catalog=<database>";
                using (SqlConnection sqlcon = new SqlConnection(cs))
                    using (SqlCommand cmd = new SqlCommand("execute InsertToTempTable", sqlcon))
                        int ret = cmd.ExecuteNonQuery();
                        if (ret == -1)
                            Console.WriteLine("Ret == -1");




    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, January 27, 2009 8:59 AM
  •  Are you trying to check if record exists in a database? If yes, you could use SELECT COUNT(*) FROM MyTable WHERE .... SQL statement and use ExecuteScalar method of SqlCommand to get count.
    Val Mazur (MVP)
    Wednesday, January 28, 2009 11:06 AM
  • yes, Michael




    cause that problem.


    but what about the RETURN statement?

    how do we can use the return value of sproc?

    Thursday, January 29, 2009 5:08 AM

    Hi again

    Please note that I have not tested this fully, so do not take this as 'best practise'.
    Reading the SET NOCOUNT documentation shows that: "The @@ROWCOUNT function is updated even when SET NOCOUNT is ON."
    So we could potentially use this, more info here:

    "SQL Server 2008 Books Online (January 2009) - @@ROWCOUNT (Transact-SQL)"

    Then use the same table as earlier, but create a sproc for updates:

    create procedure UpdateTempTable (@pId int) as
     set nocount on;
     update TempTable set aname = 'xxx' where id >= @pId
     return @@rowcount

    Now we have the number of rows that were updated in the @@rowcount, so just return it.
    Then is code, just create a SqlParameter with the Direction set to Return.

                String cs = @"Data Source=<server>;Initial Catalog=<dataase>;Integrated Security=SSPI";  
                using (SqlConnection sqlcon = new SqlConnection(cs))  
                    SqlCommand cmd = new SqlCommand("UpdateTempTable", sqlcon);  
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;  
                    cmd.Parameters.AddWithValue("@pId", 10);  
                    SqlParameter p = new SqlParameter();  
                    p.Direction = System.Data.ParameterDirection.ReturnValue;  
                    p.SqlDbType = System.Data.SqlDbType.Int;  
                    int ret = cmd.ExecuteNonQuery();  
                    // You have to access the return value in the Parameter.Value property  
                    Console.WriteLine("p = {0}", p.Value);  
                    if (ret == -1)  
                        Console.WriteLine("Ret == -1");  




    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, January 29, 2009 2:51 PM