ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction

Question

I have the below code which.I don't know why I get below error. "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" I am using SQL transaction Please can someone assist me

protected void RunLeave(int posID, int tot, int rowid)
{


    using (SqlConnection con = new SqlConnection(Modfunctions.configstring))
    {
        con.Open();

        SqlDataReader dr = default(SqlDataReader);


        int i = 0;
        try
        {
            SqlCommand cmd = new SqlCommand("Select ID from Employee WITH(Nolock) WHERE PositionID=@a", con);
            cmd.Parameters.Add("@a", SqlDbType.Int).Value = posID;
            //cmd.Transaction = transaction;
            dr = cmd.ExecuteReader();

            if (dr.HasRows)
                using (SqlConnection cons = new SqlConnection(Modfunctions.configstring))

                {
                    cons.Open();
                    SqlTransaction transaction = cons.BeginTransaction(DateTime.Now.ToLongTimeString());
                    SqlCommand cm = new SqlCommand();
                    cm.Transaction = transaction;
                    cm.Connection = cons;

                    while (dr.Read())
                    {
                        cm.CommandText="INSERT INTO LeaveAssignmentEntry VALUES('" + rowid + "','" + ddl.SelectedItem.Text + "','" + dr[0].ToString() + "','" + tot + "','" + tot + "','0')";
                        cm.ExecuteNonQuery();
                        i++;
                    }
                    transaction.Commit();
                }


            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", "alert('" + i + " employee(s) run successfully')", true);
        }
        catch (Exception ex)
        {

            try
            {
                //transaction.Rollback();
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", "alert('" + ex.Message.Replace("'", "") + "')", true);
            }
            catch (Exception ex1)
            {
            }
        }
    }

}

Answer 1

Just like how you set a transaction to your first command:

cmd.Transaction = transaction;

Also do that to your second command:

cm.Transaction = transaction;

Basically, when you're within the context of a transaction, all of your database interactions need to use that same transaction.