The Transaction property of the command has not been initialized

Question

I am trying to run ExecuteNonQuery using transaction, but I keep getting this error

OleDbException : ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

Here is my code: What is the problem?

 _dataManager = new DataManager();

        bool bTrance = false;
        _dataManager.BuildConnectionString("server", "MyId", "MyPwd");
        _dataManager.Connect();

        try
        {
            Console.WriteLine("Begin Trans");
            var res = _dataManager.BeginTransaction();              
            if (res)
            {
                bTrance = true;
                Console.WriteLine(" Trans Success");
            }
            Console.WriteLine("Query Executed");
            return _dataManager.ExecuteQuery("call SP_MySp");

        }
        catch (OleDbException objDBExc)
        {
            Console.WriteLine("OleDbException : " + objDBExc.Message);
            if (bTrance)
            {

                _dataManager.RollbackTransaction();

            }

            return false;
        }
        catch (Exception objExc)
        {
            Console.WriteLine("OleDbException : " + objExc.Message);
            if (bTrance)
            {
                bTrance = false;
                _dataManager.RollbackTransaction();

            }

            return false;
        }
        finally
        {
            Console.WriteLine("Done!");
            if (bTrance)
            {
                _dataManager.CommitTransaction();

            }
        }

 public class DataManager
{


    private OleDbConnectionStringBuilder dbConnStr;
    private OleDbConnection dbConn;
    private OleDbTransaction dbTransaction;
    private OleDbCommand dbCommand;
    private bool beginTransaction;


 public bool BeginTransaction()
    {
        try
        {

            if (beginTransaction == false)
            {
                dbTransaction = dbConn.BeginTransaction();
                dbCommand.Transaction = dbTransaction;
                beginTransaction = true;
            }
            else
                dbCommand.Transaction = dbTransaction;
        }
        catch (Exception)
        {
            throw;
        }

        return beginTransaction;
    }

 public bool ExecuteQuery(string command)
    {
        try
        {   
            dbCommand.ExecuteNonQuery();
        }
        catch (OleDbException objDbEx)
        {

            throw;
        }
        catch (Exception objEx)
        {

            throw;
        }

        dbCommand.Parameters.Clear();
        dbCommand.Connection = null;

        return true;
    }

}

Coltun

Much easier to let the Framework handle the details: using (var tx = new TransactionScope()) { your DB code goes here }. Calling tx.Commit() at the end of the block unless you want to rollback. – Lashaun

The Datamanager classcode actually belongs to a DLL. It is used by many other applications. – Coltun

Answer 1

Call the BeginTransaction method of the SqlConnection object to mark the start of the transaction. The BeginTransaction method returns a reference to the transaction. This reference is assigned to the SqlCommand objects that are enlisted in the transaction.

Assign the Transaction object to the Transaction property of the SqlCommand to be executed. If a command is executed on a connection with an active transaction, and the Transaction object has not been assigned to the Transaction property of the Command object, an exception is thrown.

Execute the required commands.

Call the Commit method of the SqlTransaction object to complete the transaction, or call the Rollback method to abort the transaction. If the connection is closed or disposed before either the Commit or Rollback methods have been executed, the transaction is rolled back.

Refer https://msdn.microsoft.com/en-us/library/2k2hy99x(VS.80).aspx

Heteronomous

That is what I am doing. Calling beginTransaction and then assigning that transaction object to dbCommand and trying to executeNonQuery – Coltun