[c#] Exception: There is already an open DataReader associated with this Connection which must be closed first

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."
        }
    }
}

Add MultipleActiveResultSets=true to the provider part of your connection string example in the file appsettings.json

"ConnectionStrings": {
"EmployeeDBConnection": "server=(localdb)\\MSSQLLocalDB;database=YourDatabasename;Trusted_Connection=true;MultipleActiveResultSets=true"}

Always, always, always put disposable objects inside of using statements. I can't see how you've instantiated your DataReader but you should do it like this:

using (Connection c = ...)
{
    using (DataReader dr = ...)
    {
        //Work with dr in here.
    }
}
//Now the connection and reader have been closed and disposed.

Now, to answer your question, the reader is using the same connection as the command you're trying to ExecuteNonQuery on. You need to use a separate connection since the DataReader keeps the connection open and reads data as you need it.

You have to close the reader on top of your else condition.

Just use MultipleActiveResultSets=True in your connection string.

The issue you are running into is that you are starting up a second MySqlCommand while still reading back data with the DataReader. The MySQL connector only allows one concurrent query. You need to read the data into some structure, then close the reader, then process the data. Unfortunately you can't process the data as it is read if your processing involves further SQL queries.

You are trying to to an Insert (with ExecuteNonQuery()) on a SQL connection that is used by this reader already:

while (myReader.Read())

Either read all the values in a list first, close the reader and then do the insert, or use a new SQL connection.

This exception also happens if don't use transactions properly. In my case, I put transaction.Commit() right after command.ExecuteReaderAsync(), and did not wait to commit the transaction until reader.ReadAsync() were called. The proper order:

1. Create transaction.
2. Create reader.
3. Read the data.
4. Commit the transaction.