Organizational Research By

Surprising Reserch Topic

exception there is already an open datareader associated with this connection w


exception there is already an open datareader associated with this connection w  using -'c#,mysql'

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

    

asked Oct 6, 2015 by patilkiran.101
0 votes
6 views



Related Hot Questions

5 Answers

0 votes

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.

answered Oct 6, 2015 by amit_cmps
0 votes

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.

answered Oct 6, 2015 by mtabakade
0 votes

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.

answered Oct 6, 2015 by bhavin
0 votes

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.

answered Oct 6, 2015 by devkumargupta
0 votes

As stated by David Suarez, you are trying to read in parallel. I had the same problem and found a quick and easy solution.

Before starting your read do a waiting loop

private void WaitForEndOfRead()
{
    int count = 0;
    while (_connection.State == ConnectionState.Fetching)
    {
        Thread.Sleep(1000);
        count++;
        if (count == 10)
        {
            break;
        }
    }
}

Then before you start your SqlCommand call the function above. Something like:

using (SqlCommand command = new SqlCommand("GetData", _connection))
{
    command.CommandType = CommandType.StoredProcedure;
    using (SqlDataReader dr = command.ExecuteReader())
    {
        // do your thing here
    }
}
answered Oct 6, 2015 by r3tt

...