0

I have a form in windows where I am doing insert statement for header and detail. I am using MySqlTransaction for the form. When there is no error in header and detail the transaction gets committed but when there is an error in insert query of detail then the following error comes while rollback

There is already an open DataReader associated with this Connection which must be closed first.

Here is my code.

public string Insert_Hardening_Measures_HdrANDDtl(BL_Vessel_Hardening_Measures objHdr, List<BL_Vessel_Hardening_Measures> objDtl)
        {
            string success = "true";
            string success1 = "";


            MySqlConnection MySqlConnection1 = new MySqlConnection(strCon);
            MySqlConnection1.Open();
            MySqlTransaction MyTransaction = MySqlConnection1.BeginTransaction();

            MySqlCommand MyCommand = new MySqlCommand();

            MyCommand.Transaction = MyTransaction;
            MyCommand.Connection = MySqlConnection1;             

            try
            {
            MyCommand.CommandText = "insert into hardening_measures_hdr (Hardening_Measures_Hdr_id,Month,Year) values (" + objHdr.Hardening_Measures_Hdr_id + ",'" + objHdr.Month + "','" + objHdr.Year + "')";
               MyCommand.ExecuteNonQuery();
                for (int i = 0; i < objDtl.Count; i++)
                {


                MyCommand.CommandText = "insert into hardening_measures_dtl (Hardening_Measures_Dtl_id,Hardening_Measures_Hdr_id,Hardening_Measures_Mst_id,Value) values (" + objDtl[i].Hardening_Measures_Dtl_id + "," + objDtl[i].Hardening_Measures_Hdr_id + ",'" + objDtl[i].Hardening_Measures_Mst_id + ",'" + objDtl[i].Value + "')";
                MyCommand.ExecuteNonQuery();
                }


                MyTransaction.Commit();
                MySqlConnection1.Close();
            }
            catch
            {

                MyTransaction.Rollback();
            }


            return success;
        }

Anybody who have come through this kind of problem please suggest something

Anuj
  • 1,496
  • 1
  • 18
  • 28
  • possible duplicate of [mysql transaction (commit and rollback)](http://stackoverflow.com/questions/14252560/mysql-transaction-commit-and-rollback) – Erti-Chris Eelmaa Jul 21 '14 at 09:57
  • You should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. And use _using statement_ to dispose your database connections. – Soner Gönül Jul 21 '14 at 09:58
  • @ Erti-Chris Eelmaa i tried your method with dedicated command object but dindn't work – Anuj Jul 21 '14 at 10:18
  • I don't see a DataReader; where does it come in? I also don't see you close the connection after the Rollback. – TaW Jul 21 '14 at 11:27

0 Answers0