0

In my understanding, the execute nonquery command in C# SQLCommand has to return the number of affected records, I have used this SQLCommand with SQL Transaction, it return a negative one but when I check the database the record has been inserted.

What I want to actually achieve is, to commit only if a record has been inserted into the database successfully, now I tried to achieved this by checking the return value of the executeNoneQuery command but as like I said it returns a negative one, to me that says the query was perhaps unsuccessfully however the database records seems to suggest otherwise.

Is there somewthing that I am missing here,am I perhaps implementing my SQlCommand and SQLTransaction incorrectly

Here is my current code 

using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.AppSettings["SQLConnString"]))
{
    sqlConn.Open();
    using (sqlTrans = sqlConn.BeginTransaction())
    {
        using (SqlCommand command = new SqlCommand("dbo.sp_InsertWizardImport", sqlConn, sqlTrans))
        {
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@GL_ACC", GL_ACC));
            command.Parameters.Add(new SqlParameter("@DOC_REF_NUM ", DOC_REF_NUM));                                     

            int record = command.ExecuteNonQuery();
            sqlTrans.Commit();

            str2 = _reader.ReadLine();
        }
    }
}

There is of course more variables to the stored prod but I only included 2 and other peace of of code that I think is problematic

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
Mronzer
  • 399
  • 1
  • 7
  • 18
  • 1
    Check that you don't have `SET NOCOUNT ON` in your stored procedure. This will stop the number of affect rows be returned. – Rahul Hendawe Oct 02 '17 at 09:55
  • 3
    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx .... **For all other types of statements, the return value is -1** ....this is why you get -1, you are executing a stored procedure. – Arran Oct 02 '17 at 09:56
  • @S.Akbari I dont think this is a dublicate, executeNonQuery and executeScalar are two different functions, both should work for their respective purposes, using executeScalar instead of executeNonquery does not answer the question as to why the latter is not working but its just a workaround ,Rahul Hendawe is spot on, the set nocount on on the stored proc itself is the cause of the failure – Mronzer Oct 02 '17 at 10:42
  • @Ronny Check the *Arran* comment and MSDN documentation about `ExecuteNonQuery` return values. The duplicate question is exactly about this reason not `ExecuteScalar`. – Salah Akbari Oct 02 '17 at 10:45

0 Answers0