none
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  
    10  

     

    Thanks in Advance.

    JD

    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)"
    http://msdn.microsoft.com/en-us/library/ms189837.aspx
    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))
    go
    create procedure InsertToTempTable as
    begin
     set nocount on;
     insert into TempTable values ('abcde')
    end
    go
    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))
                    {
                        sqlcon.Open();
                        int ret = cmd.ExecuteNonQuery();
                        if (ret == -1)
                        {
                            Console.WriteLine("Ret == -1");
                        }
                    }
                }

    HTH

    //Michael

    --------------------------------------------------------------------------------

    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) http://www.xporttools.net
    Wednesday, January 28, 2009 11:06 AM
    Moderator
  • yes, Michael

     

    the 

    SET NOCOUNT ON

    cause that problem.

     

    but what about the RETURN statement?

    how do we can use the return value of sproc?


    Gardenia
    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)"
    http://msdn.microsoft.com/en-us/library/ms187316.aspx

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

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

    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))  
                {  
                    sqlcon.Open();  
                    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;  
                    cmd.Parameters.Add(p);  
     
                    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");  
                    }  
                    sqlcon.Close();  
                } 

     

    HTH

    --------------------------------------------------------------------------------

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

    Thursday, January 29, 2009 2:51 PM