C# .NET - closing the reader - Asked By SVK N on 13-Oct-11 04:23 AM

qry = " Select * from mttable where tempname= '"+  txttemp.Text +"'";
       SqlCommand cmd = new SqlCommand(qry, conn);
        reader1= cmd.ExecuteReader();

       while (m_reader1.Read())
       {
         qry = " UPDATE temptbl set  SEL = " + m_reader["SEL"] + " ,[ORDER] = " + m_reader["ORDER"] + " ;
         SqlCommand cmdm = new SqlCommand(qry, conn);
          reader2 = cmdm.ExecuteReader();
          reader2.Close();
        cmdm.Dispose();    
       }

It should be outside of loop....
You need to close the reader or dispose the command once you have completed with complete While loop not for each step.
try to move these outside of while loop:
reader2.Close();
cmdm.Dispose(); 

Thanks

i had done that but still its giving error as reader opnen

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

Hi,

You should always call the Close method when you have finished using the DataReader object.

The following example creates a http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx, a SqlCommand, and a http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx. The example reads through the data, writing it out to the console window. The code then closes the http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx. The http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx is closed automatically at the end of the using code block.

Change the code to

reader1= cmd.ExecuteReader();

     while (m_reader1.Read())
     {
     qry = " UPDATE temptbl set  SEL = " + m_reader["SEL"] + " ,[ORDER] = " + m_reader["ORDER"] + " ;
     SqlCommand cmdm = new SqlCommand(qry, conn);
      reader2 = cmdm.ExecuteReader(); 
     }

      reader2.Close();
      cmdm.Dispose();

because if your operation not entered in while loop then reader become open and next time you call the method you will get error like reader is opened.

i have the same code
reader1= cmd.ExecuteReader();

   while (m_reader1.Read())
   {
   qry = " UPDATE temptbl set  SEL = " + m_reader["SEL"] + " ,[ORDER] = " + m_reader["ORDER"] + " ;
     SqlCommand cmdm = new SqlCommand(qry, conn);
    reader2 = cmdm.ExecuteReader(); 
   }

    reader2.Close();
    cmdm.Dispose();

1) i get the same erroro msg as reader already opened

2) i cannot close cmdm.Dispose(); as its declared inside the block

 need to update data from one table to another

Check connection opened or not using condition like'
if connection opened
// then close then connection

after
open connection
do update

connection is opened
i get error as reader already opened there is no connection error

Definitely it willl gives you the error of DataReader already open.. from your code what i am seeing is that in the While loop , you are just going to update your table's "SEL, Order" value only.

So i believe you don't need to use ExecuteReader( ) method to update the values in table. ExecuteReader( ) method is only used for Selecting/pulling a data from the table..

You can use ExecuteNonQuery( ) method for Insert/Update/Delete operation..

try to use below modified code.. see my changes in red bolded part...

qry = " Select * from mttable where tempname= '"+  txttemp.Text +"'";
     SqlCommand cmd = new SqlCommand(qry, conn);
      reader1= cmd.ExecuteReader();

     while (m_reader1.Read())
     {
     qry = " UPDATE temptbl set  SEL = " + m_reader["SEL"] + " ,[ORDER] = " + m_reader["ORDER"] + " ;
     SqlCommand cmdm = new SqlCommand(qry, conn);
      cmdm.ExecuteNonQuery( );
      cmdm.Dispose();    
     }
m_reader1.Close();
cmd.Dispose( );

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

error at
  cmdm.ExecuteNonQuery( );

In order to  open multiple data reader at same time  you neeed to add MultipleActiveResultSets=true in connection string, otherewise you can't use multiple data reader
like this way'
ConString = "xxxxx;Integrated Security=True;MultipleActiveResultSets=true";

remember this will not work up to ADO.Net 1.0

Better to use the dataAdapter:

// Close data reader object if it is open as below
if (rdr != null)
  rdr.Close();

before reading the data from datareader

Thanks
Venkat