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

Question

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       lt      THIS LINE THROWS  C  mySQL There is already an open DataReader associated with this Connection which must be closed first

User · Answer

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   Create transaction  Create reader  Read the data  Commit the transaction

User · Answer

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

User · Answer

Just use MultipleActiveResultSets True in your connection string

User · 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

User · Answer

Add MultipleActiveResultSets true to the provider part of your connection string example in the file appsettings json  quot ConnectionStrings quot      quot EmployeeDBConnection quot    quot server  localdb   MSSQLLocalDB database YourDatabasename Trusted Connection true MultipleActiveResultSets true quot

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to c#

Examples related to mysql