c#: ExecuteNonQuery() returns -1

c# executenonquery
Funky picture Funky · Nov 14, 2011 · Viewed 41.4k times · Source

Ive used this method before to return the amount of rows changed. I am it to run an insert method, the insert runs fine in the stored procedure, but the return value from the ExecuteNonQuery always returns -1.

Here is my C# code:

int ret = 0;

using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand(QueryName, conn))
    {
        conn.Open();

        if (Params != null)
            cmd.Parameters.AddRange(Params);

        cmd.CommandType = CommandType.StoredProcedure;

        ret = cmd.ExecuteNonQuery();

        conn.Close();
    }
}

return ret;

Why do I get -1 instead of the actual number of rows changed?

Answer

kol picture kol · Nov 14, 2011

If you use this method to call a store procedure that perform UPDATE/INSERT in a table the method return -1 if the stored procudere has the SET NOCOUNT at ON value.

--source

Related questions

Inserting values into a SQL Server database using ado.net via C#

I have created a simple program to insert values into the table [regist], but I keep getting the error Incorrect syntax near ')' on cmd.ExecuteNonQuery();: private void button1_Click(object sender, EventArgs e) { SqlConnection cn = new SqlConnection("Data …

Read More
Get affected rows on ExecuteNonQuery

I am currently working on a C# project and I am running an insert query which also does a select at the same time, e.g.: INSERT INTO table (SELECT * FROM table WHERE column=date) Is there a way I …

Read More
ExecuteScalar vs ExecuteNonQuery when returning an identity value

Trying to figure out if it's best to use ExecuteScalar or ExecuteNonQuery if I want to return the identity column of a newly inserted row. I have read this question and I understand the differences there, but when looking over …

Read More