Cause of 'SqlTransaction has completed' error

Question

I am developing a small desktop application using VS.net and SQL Server. I am using SqlTransaction in my code:

SqlTransaction tran = null;
SqlCommand cmd = new SqlCommand();
int lstInsSubjId = -1;

try
{
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();

        tran = con.BeginTransaction("Transaction1");//Transaction begin
        tran.Save("Savepoint_1");//Savepoint 1

        string sql1 = @"insSubject";
        cmd = new SqlCommand(sql1, con, tran);

        cmd.Parameters.Add("@lstInsSubjId", SqlDbType.Int).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@sub_name", SqlDbType.VarChar).Value = txtSubjectName.Text.Trim();

        cmd.CommandType = CommandType.StoredProcedure;
        tran.Save("Savepoint_2");//Savepoint 2

        cmd.ExecuteNonQuery();

        lstInsSubjId = Convert.ToInt32(cmd.Parameters["@lstInsSubjId"].Value);

        for (int i = 0; i < clbClasses.CheckedItems.Count; i++)
        {
            int clsId = Convert.ToInt32(((DataRowView)clbClasses.CheckedItems[i]).Row["c_id"].ToString());
            cmd.CommandText = @"INSERT INTO tblClassSubjectMap_mavis(c_id, sub_id) 
                                VALUES(@c_id, @sub_id)";
            cmd.Parameters.Add("@c_id", SqlDbType.Int).Value = clsId;
            cmd.Parameters.Add("@sub_id", SqlDbType.Int).Value = lstInsSubjId;

            cmd.CommandType = CommandType.Text;
            //tran.Save("Savepoint_3");//Savepoint 3

            cmd.ExecuteNonQuery();
        }

        tran.Commit();//Transaction commit
        MessageBox.Show("Records added", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);

        txtSubjectName.Text = "";
        txtSubjectName.Focus();
        frmSubjectBrows.subList.bindListView();
    }
}
catch (SqlException ex)
{
    if (tran != null)
    {
        tran.Rollback();
        MessageBox.Show(ex.Message.ToString() + "\nTransaction Rolledback", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Stop);
    }
}

Towards the end of the code, in the for loop, if there are more than one checked items i.e. if it iterates/loops more than once then it throws an error and the Transaction is never committed. However, If it loops only once then there is no error and the transactions commits.

The error message is :

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: This SqlTransaction has completed; it is no longer usable.

While looking for an answer on the internet, some solutions say that such error may occur if the transaction is abruptly closed before being committed. But I don't understand how here the transaction is getting closed.

Can anyone please tell what is going wrong? Thanks in advance.

Answer 1

use for each command a new instance of sqlcommand, see example at
https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqltransaction?view=netframework-4.8

try this code:

int lstInsSubjId = -1;

try
{
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();
        SqlCommand cmd = connection.CreateCommand();
        SqlTransaction tran = con.BeginTransaction("Transaction1");//Transaction begin
        cmd.Connection = cmd;
        cmd.Transaction = tran;

        tran.Save("Savepoint_1");//Savepoint 1

        cmd.CommandText = @"insSubject";
        cmd.Parameters.Add("@lstInsSubjId", SqlDbType.Int).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@sub_name", SqlDbType.VarChar).Value = txtSubjectName.Text.Trim();

        cmd.CommandType = CommandType.StoredProcedure;
        tran.Save("Savepoint_2");//Savepoint 2

        cmd.ExecuteNonQuery();

        lstInsSubjId = Convert.ToInt32(cmd.Parameters["@lstInsSubjId"].Value);

        for (int i = 0; i < clbClasses.CheckedItems.Count; i++)
        {
            int clsId = Convert.ToInt32(((DataRowView)clbClasses.CheckedItems[i]).Row["c_id"].ToString());
            SqlCommand cmd2 = connection.CreateCommand();
            cmd2.Connection = cmd2;
            cmd2.Transaction = tran;

            cmd2.CommandText = @"INSERT INTO tblClassSubjectMap_mavis(c_id, sub_id) 
                                VALUES(@c_id, @sub_id)";
            cmd2.Parameters.Add("@c_id", SqlDbType.Int).Value = clsId;
            cmd2.Parameters.Add("@sub_id", SqlDbType.Int).Value = lstInsSubjId;

            cmd2.CommandType = CommandType.Text;
            //tran.Save("Savepoint_3");//Savepoint 3

            cmd2.ExecuteNonQuery();
        }

        tran.Commit();//Transaction commit