C# – Exception: There is already an open DataReader associated with this Connection which must be closed first

cMySQL

I have below code and I am getting exception:

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

I am using Visual Studio 2010/.Net 4.0 and MySQL for this project. Basically I am trying to run another SQL statement while using data reader to do my other task. I am getting exception at line cmdInserttblProductFrance.ExecuteNonQuery();

SQL = "Select * from tblProduct";

//Create Connection/Command/MySQLDataReader
MySqlConnection myConnection = new MySqlConnection(cf.GetConnectionString());
myConnection.Open();
MySqlCommand myCommand = new MySqlCommand(SQL, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
myCommand.Dispose();

if (myReader.HasRows)
{
    int i = 0;
    // Always call Read before accessing data.
    while (myReader.Read())
    {
        if (myReader["frProductid"].ToString() == "") //there is no productid exist for this item
        {
            strInsertSQL = "Insert Into tblProduct_temp (Productid) Values('this istest') ";
            MySqlCommand cmdInserttblProductFrance = new MySqlCommand(strInsertSQL, myConnection);
            cmdInserttblProductFrance.ExecuteNonQuery(); //<=====THIS LINE THROWS "C# mySQL There is already an open DataReader associated with this Connection which must be closed first."
        }
    }
}

Best Answer

You are using the same connection for the DataReader and the ExecuteNonQuery. This is not supported, according to MSDN:

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

Updated 2018: link to MSDN

Related Topic