This SqlTransaction has completed; it is no longer usable, why?

Question

Code:

I am trying to run this function but it throws error: This SqlTransaction has completed; it is no longer usable.

I have used every other way but not working. I have used it without USING but still same error.

Why?

public Boolean AddWorkProgress(int WorkID, int ContractorID, float PhysicalProgress, 
  decimal FinancialProgress, int UserID, int OrgID, float FinancialProgressPecentage)
{
   SqlCommand SqlCom = new SqlCommand("AddWorkProgress", DataBaseConnection.OpenConnection());
   SqlCom.CommandType = CommandType.StoredProcedure;

   using (SqlTransaction sqlTrans = SqlCom.Connection.BeginTransaction()) 
   {
      SqlCom.Transaction = sqlTrans;

      try
      {
         SqlCom.Parameters.AddWithValue("@Work_ID", WorkID);
         SqlCom.Parameters.AddWithValue("@Contractor_ID", ContractorID);
         SqlCom.Parameters.AddWithValue("@PhysicalProgress", PhysicalProgress);
         SqlCom.Parameters.AddWithValue("@FinancialProgress", FinancialProgress);
         SqlCom.Parameters.AddWithValue("@OrgID", OrgID);
         SqlCom.Parameters.AddWithValue("@fk_WebUsers_UserID", UserID);
         SqlCom.Parameters.AddWithValue("@FinancialProgressPercentage", FinancialProgressPecentage);
         SqlParameter SqlParamReturnStatus = new SqlParameter("@ReturnStatus", SqlDbType.Bit);
         SqlCom.Parameters.Add(SqlParamReturnStatus);
         SqlParamReturnStatus.Direction = ParameterDirection.Output;
         SqlParameter SqlParamReturnStatusMessage = new SqlParameter("@ReturnStatusMessage", SqlDbType.VarChar, -1);
         SqlCom.Parameters.Add(SqlParamReturnStatusMessage);
         SqlParamReturnStatusMessage.Direction = ParameterDirection.Output;
         SqlCom.ExecuteNonQuery();
         DataBaseConnection.CloseConnection();

         string ReturnStatusMessage = Convert.ToString(SqlParamReturnStatusMessage);
         Boolean ReturnStatus = Convert.ToBoolean(SqlParamReturnStatus.Value);
         // ProgressID = Convert.ToInt64(SqlParamReturnProgressID.Value);

         sqlTrans.Commit();
         return ReturnStatus;
      }

      catch (Exception ex)
      {
         sqlTrans.Rollback();
         sqlTrans.Dispose();

         throw ex;
      }
   }
}

looks to me like you close the connection before you commit the transaction — Dave Becker, Mar 10 '17 at 11:37
You should probably be putting your connection in a `using` block as well. — David, Mar 10 '17 at 11:39
The catch block is redundant too. The `using` block will rollback *and* dispose the transaction. — Panagiotis Kanavos, Mar 10 '17 at 11:44
Why you using transaction at all? You executes only one procedure — Fabio, Mar 10 '17 at 11:44
Also, the connection should be placed in a `using` statement too. Right now, any error will leave the connection open — Panagiotis Kanavos, Mar 10 '17 at 11:44
BTW, closing the connection without commiting the transaction results in a rollback. Whatever that stored procedure did, disappeared when `CloseConnection()` was called — Panagiotis Kanavos, Mar 10 '17 at 11:45

Andrey Korneyev · Answer 1 · 2017-03-10T11:45:43.267

4

You're closing connection before commiting transaction:

DataBaseConnection.CloseConnection();
// skipped code
sqlTrans.Commit();

As from MSDN:

Both Commit and Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

This is exactly your case - connection gets closed before commit/rollback of transaction.

edited Mar 10 '17 at 11:45

answered Mar 10 '17 at 11:38

Andrey Korneyev

26,353
15
70
71

I doubt it will be comitted. Will make more sense if it will be rolled back in that case. – Evk Mar 10 '17 at 11:41
@Evk I agree, it was incorrect wording. I've modified the answer to remove possible mislead. – Andrey Korneyev Mar 10 '17 at 11:47
Will be even more clear if you write that closing connection rollbacks all pending transactions started with connection.BeginTransaction :) – Evk Mar 10 '17 at 11:54

Panagiotis Kanavos · Answer 2 · 2017-03-10T12:02:14.187

You can't rollback or commit a transaction if the connection is closed. When the connection closes, SQL Server rolls back any open transactions anyway.

The catch block is redundant too. A transaction gets rolled back when it gets disposed, unless Commit() was called.

Finally, the connection is handled in an unsafe manner. If any error occurs before CloseConnection() the connection will be left open.

Using the connection and transaction in the correct manner simplifies the code a lot:

var SqlCom = new SqlCommand("AddWorkProgress");
SqlCom.CommandType = CommandType.StoredProcedure;
//... prepare the command. No need to use a connection

using(var connection=DataBaseConnection.OpenConnection())
using(var transaction= connection.BeginTransaction()) 
{
    SqlCom.Connection=connection;
    SqlCom.Transaction=transaction;

    SqlCom.ExecuteNonQuery();
    transaction.Commit();

    //Process the results
}

That's it. The connection will close when the using block finishes. The transaction will be rolled back if Commit() isn't called, which can only happen in case of an error.

Once you separate the construction of the stored procedure from its execution, you can simplify the code further. For example, you can create the command only once, when the class or application gets initialized. Each time you want to execute it, you just set the connection and parameter values, eg:

public Boolean AddWorkProgress(int WorkID, int ContractorID, float PhysicalProgress, decimal FinancialProgress, int UserID, int OrgID, float FinancialProgressPecentage)
{
    using(var connection=DataBaseConnection.OpenConnection())
    using(var transaction= connection.BeginTransaction()) 
    {
        _addProgressCmd.Parameters["@Work_ID"].Value=WorkID;
        ....
        _addProgressCmd.Connection=connection;
        _addProgressCmd.Transaction=transaction;

        _addProgressCmd.ExecuteNonQuery();
        transaction.Commit();

        var status=(bool)_addProgressCmd.Parameters["@ReturnStatus"].Value;
        var statusMsg=(string)_addProgressCmd.Parameters["@ReturnStatusMessage"].Value;

        //Process the results
        ...
    }
}

score 0 · Answer 3 · answered Jul 08 '20 at 13:57

0

If the above answers do not solve your problem then you might want to check that your database and table are set up correctly.

A missing primary key can in some cases give the same error as described in this answer.

answered Jul 08 '20 at 13:57

Danny Boy

355
3
10

This SqlTransaction has completed; it is no longer usable, why?

3 Answers3