CODEWITHSUNDEEP
c#sql-server-2005transactions
Pankouri
Pankouri

Reputation: 676

A transaction problem while executing a C# code

In M code, I am getting an error, "This SqlTransaction has completed; it is no longer usable." The line of code that is generating this code is this sqlTransaction

MSSqlConnectionHandler.CommitTransaction();

This CommitTransaction function is 

public static void CommitTransaction()
    {
        try
        {
            _theTran.Commit();
        }
        catch (Exception Ex)
        {
            try
            {
                _theTran.Rollback();
            }
            catch (Exception InnerEx)
            {
                throw InnerEx;
            }
            throw Ex;
        }
    }

But, I Comment out this MSSqlConnectionHandler.CommitTransaction(); line, then no error occured but no data is saved either. What's going wrong, What should I provide here to make it more clear? Thanx in advance

Upvotes: 0

Views: 197

Answers (2)

Jon Raynor
Jon Raynor

Reputation: 3892

A possbility is the connection that the transaction was on was closed. If that was the case, when this was called:

_theTran.Commit();

The error would occur.

Whoever is calling 

CommitTransaction

Should do some checking prior to commit, like maybe something like this:

if (conn.State == ConnectionState.Open) 
{
     if (_theTran != null)
     {
          CommitTransaction();
     }
     conn.Close();
}

As far as the exception handling is concerned, the exception handler is doing nothing but rethrowing. IF exception occurs on commit, then try the rollback and always throw. I don't think you need the inner try catch.

try
{             
    _theTran.Commit();         
}         
catch (Exception)
{                        
    _theTran.Rollback();             
    throw;
}

Upvotes: 1

CodingGorilla
CodingGorilla

Reputation: 19842

The error indicates that the transaction is being committed somewhere else in your code, is _theTran an instance variable that could be used and committed somewhere else?

Upvotes: 1

Related Questions