ExecuteNonQuery requires the command to have a transaction error in my code

Question

I get the following error on cmd.ExecuteNonQuery.

"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:

  //if (hdRefresh.Value.Length > done.Value.Length || done.Value == "1")
    //{
    //    //Write Your Add Customer Code here > Response.Write("true") 
    //    done.Value = hdRefresh.Value;
    //}
    //else
    //{
    //    Response.Redirect("~/Cashier/BTBill.aspx");
    //    return;
    //}

    if (IsClosedToDay())
    {
        ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "Warning", "<script>alert('Day Closing has been Performed ')</script>", false);
        return;
    }

    DateTime dateFeomDB = getdate();
    // by atizaz
    if (HDD.Value == "" || HDD.Value == null)
    {
        ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "Warning", "<script>alert('No Transaction Found')</script>", false);
        return;
    }
    //
    SqlConnection scon = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ToString());
    Common.BillTransaction bill1 = new Common.BillTransaction();
    ProcessUpdateBalandUnAuthBal insertBalance = new ProcessUpdateBalandUnAuthBal();
    Common.Currency currencyy = new Common.Currency();
    ProcessAuthorizeTokenByBillNo authorize = new ProcessAuthorizeTokenByBillNo();
    BillTransaction bill = new BillTransaction();
    scon.Open();
    SqlTransaction sqlTrans = scon.BeginTransaction();
    try
    {
        string strforxml = HDD.Value;
        XmlDocument docXml = new XmlDocument();


        #region Read In To Sender Controlls

        #region Common Information
        Contact con = new Contact();
        con.Title = ddlTitle.SelectedItem.Text;
        con.FirstName = TextBox1.Text.Trim();
        con.LastName = TextBox9.Text.Trim();
        con.ConTactNo = txtCell.Text == "" ? SqlString.Null : txtCell.Text;
        con.Country = ddlCountry.SelectedItem.Text;
        con.CustomerType = ddlCustomerType.SelectedItem.Text;
        con.CustTypeID = int.Parse(ddlCustomerType.SelectedValue);
        con.CountryID = Int32.Parse(ddlCountry.SelectedValue);
        con.sqlTransaction = sqlTrans;
        if (Scitytxt.Value != "")
        {
            try
            {
                con.City = Scitytxt.Value;
                con.CityID = Int32.Parse(Scityval.Value);
            }
            catch (Exception)
            { }
        }
        else
        {
            con.City = SqlString.Null;// Scitytxt.Value;
            con.CityID = SqlInt32.Null;// Int32.Parse(Scityval.Value);
            con.Address = "";
        }
        //con.City = ddlCity.SelectedItem.Text;
        //con.CityID = int.Parse(ddlCity.SelectedValue);
        con.Address = TextBox10.Text;
        #endregion

        #region Check For NIC and Passport

        if (txtNIC.Text != "" || txtPassport.Text != "")
        {
            SqlDataReader rdrsender;

            if (txtNIC.Text != "")
            {
                con.NIC = txtNIC.Text;
            }
            else
            {
                con.NIC = SqlString.Null;
            }
            if (txtPassport.Text != "")
            {
                con.Passport = txtPassport.Text;
            }
            else
            {
                con.Passport = SqlString.Null;
            }
            ProcessSearchContactInContactInfo srchSender = new ProcessSearchContactInContactInfo();
            srchSender.Contact = con;
            srchSender.Invokewith5parameters();
            rdrsender = srchSender.ResultSet;

            #region If record Doesnot Exist In response of NIC Passport
            if (!rdrsender.Read())
            {
                rdrsender.Close();
                rdrsender.Dispose();
                //  con.sqlTransaction = sqlTrans;
                ProcessAddContact InsertnewSenderInfo = new ProcessAddContact();
                // InsertnewSenderInfo.sqlTransaction = sqlTrans;
                InsertnewSenderInfo.Contact = con;
                InsertnewSenderInfo.Invoke();

                //  sender1 = InsertnewSenderInfo.ResultSet;
                //  Sender_ID.Value = sender1[13].ToString();
            }
            #endregion
            #region If Record Exists
            else
            {
                con.CustomerID = Int32.Parse(rdrsender["Customer_ID"].ToString());
                rdrsender.Close();
                rdrsender.Dispose();
            }
            #endregion
        }
        #endregion

        #region If Customer Donot Have NIC And/OR Passport
        else// this executes when both Pasport and NIC are Null
        {
            con.NIC = SqlString.Null;
            con.Passport = SqlString.Null;
            ProcessAddContact InsertnewSenderInfo = new ProcessAddContact();
            InsertnewSenderInfo.Contact = con;
            InsertnewSenderInfo.Invoke();

            DataSet ds = new DataSet();
            int a = con.CustomerID;
            StringReader inforeader = new StringReader("<CusTable><CusInfo><Relation_Type></Relation_Type><HusbandFather_Name></HusbandFather_Name><Address_Present></Address_Present><Address_Other></Address_Other><Phone_No_Office></Phone_No_Office><Cell_No></Cell_No><Fax_No></Fax_No><Date_Of_Birth></Date_Of_Birth><NTN_No></NTN_No><Nationality></Nationality><Occupation></Occupation><Relation_With_Financial_Institution></Relation_With_Financial_Institution><Other_Relation_With_Financial_Institution></Other_Relation_With_Financial_Institution><Business_Relation></Business_Relation></CusInfo></CusTable>");
            ds.ReadXml(inforeader);
            ds.GetXml();
            SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() + " WHERE Customer_ID=" + a + "", scon);
            cmd.ExecuteNonQuery();

            //  sender1 = InsertnewSenderInfo.ResultSet;
            //  Sender_ID.Value = sender1[13].ToString();
        }

tell me what is problem in my code and how to solve it.

Vintage

The problem is outside the code snippet you've pasted. You are doing something strange with the SqlConnection variable scon before reaching this code block and then trying to reuse it in a new SqlCommand. Can you paste any other applicable code for the SqlConnection? – Peraea

Answer 1

You need to change this line

SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() + 
                                " WHERE Customer_ID=" + a + "", scon);

in this way

SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() + 
                  " WHERE Customer_ID=" + a + "", scon, sqlTrans);

The error message states exactly the problem. Before code reaches that line you have opened a transaction and it is still open at the point of error

.....
scon.Open();       
SqlTransaction sqlTrans = scon.BeginTransaction();
.....       

Now, every SqlCommand executed when the connection has an opened transaction need to be informed of this. The transaction is not automatically set by the Framework.

You can use the SqlCommand constructor, as explained above, or you can set the cmd.Transaction property before executing the command.

Warning 1

The need to set the transaction for the current command is true even if you create the SqlCommand directly from the connection itself.

   SqlCommand cmd = scon.CreateCommand();
   cmd.Transaction = sqlTrans; // Required when inside a transaction 

Warning 2

Avoid at all cost the use of string concatenation when using query text to update/insert/delete/select on a database. Use parameters. This will remove problems with strange or invalid characters and, most important, will prevent SqlInjection Attacks

string sqlText = "update Contact_Info set CustInfo=@info WHERE Customer_ID=@id";
SqlCommand cmd = new SqlCommand(sqlText, scon, sqlTrans);  
cmd.Parameters.AddWithValue("@info", ds.GetXml());
cmd.Parameters.AddWithValue("@id",a);
cmd.ExecuteNonQuery();  

Also, another recommendation is to NOT use AddWithValue, while handy, this method has many problems as explained in my answer here

Extravagance

Answer 2

You have started a transaction that is not commited before you called cmd.ExecuteNonQuery().

Just write down cmd.Transaction = sqlTrans; just before cmd.ExecuteNonQuery();

it will ensure that Now ExecuteNonQuery() will be executed in same transaction and also will be able to see all the modification done to database in the same transaction.

Rsfsr

Brilliant !!! I am doing two diff operations (one from EF & one using Ado.Net) & mine was failing, till I incorporate your suggestion. Thanks a lot :) – Bangka

Answer 3

If you have this code:

SqlTransaction sqlTrans = scon.BeginTransaction();

then you should also have this:

cmd.Transaction = sqlTrans;

they work together.

Muscovite

Answer 4

For me dapper's ExecuteAsync takes 2 optional parameters and transaction wasn't being recognized so I had to name the parameters like this:

var result = await a_mConn.ExecuteAsync(sql: a_sSqlQuery,transaction: a_mTransaction);

Cross