Error: ExecuteReader requires and open and available Connection. The connection's current status is open, Fetching.

Error: ExecuteReader requires and open and available Connection. The connection's current status is open, Fetching.

• Venki HR

  but isnt that what this statement does:

  dr = myCom.ExecuteReader(CommandBehavior.CloseConnection);

  
  

• Dustpan Dave

  Hi boban.s

  I tried what you said, to move around my code and it did work to get rid of the error message. The code runs fine, but it does not do what I want it.
  Because I move the select statement outside of the while loop, it captures the value of myList where it is at the first point the statement is executed (ie, i = 0) and it will always execute that query for that value. How do I make it take a different value everytime it goes through the while loop

  This is the code I have now:

  int i = 0;

  try
  {
  string strOleDb;
  strOleDb = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + myPath;

  OleDbConnection cn = new OleDbConnection(strOleDb);
  OleDbCommand myCom = new OleDbCommand();
  myCom.Connection = cn;
  OleDbDataReader dr;
  cn.Open();

  try
  {
  myCom.CommandText = @"SELECT Employee.EmpEmail
  FROM Employee, EmpInterests
  WHERE ((Employee.EmpID = EmpInterests.EmpID) AND
  ((EmpInterests.EntityID = "+ myList.bookID + ") AND "
  + "((EmpInterests.Interest = '0') OR "
  + " (EmpInterests.Interest = thisWord))))";

  while (i < fullIndex
  {
  if (myList.bookID > 0)

  {
  dr = myCom.ExecuteReader();

  while (dr.Read())
  {
  try
  {
  MessageBox.Show("Email is: " + dr.GetString(0));
  }///end try
  catch (Exception ex)
  {
  MessageBox.Show(ex.Message);
  }//end catch
  }//end while

  dr.Close();
  }//end if
  i++;
  }//end while

  cn.Close();
  }//end try

  catch (Exception ex)
  {
  MessageBox.Show(ex.Message);
  }//end catch

  }//end try

  catch (Exception ex)
  {
  MessageBox.Show(ex.Message);
  }//end catch
  

  
  

• JavaBoy

  Yes, that isexactly what you see, you close connection after first command is executed and after that connection is closed and on second execution it fails. Please change the source, thoroughly, like moving creating of command out of loop, like closing data reader, removing CloseConnection Behaviour and put Close method to be executed in finally blok.
  
  

• Kimo9909

  User parameterized query and change parameter value in the loop.

  
  
  

• vandewst

  Try this source:
  

  int i = 0;

  string strOleDb;

  strOleDb = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + myPath;

   

  OleDbConnection cn = new OleDbConnection(strOleDb);

  OleDbCommand myCom =

      new OleDbCommand(@"SELECT Employee.EmpEmail

  FROM Employee, EmpInterests

  WHERE ((Employee.EmpID = EmpInterests.EmpID) AND

  ((EmpInterests.EntityID = AND "

                       + "((EmpInterests.Interest = '0') OR "

                       + " (EmpInterests.Interest = ))))");

  myCom.Parameters.Add("@EntityID", OleDbType.Integer);

  myCom.Parameters.Add("@Interest", OleDbType.VarChar, 20).Value = thisWord;

  myCom.Connection = cn;

   

  try

  {

      cn.Open();

      while (i < fullIndex)

      {

          if (myList.bookID > 0)

          {

              myCom.Parameters[" EntityID"].Value = myList.bookID;

              OleDbDataReader dr = myCom.ExecuteReader();

   

              while (dr.Read())

              {

                  try

                  {

                      MessageBox.Show("Email is: " + dr.GetString(0));

                  } ///end try

                  catch (Exception ex)

                  {

                      MessageBox.Show(ex.Message);

                  } //end catch

              } //end while

   

              dr.Close();

          } //end if

          i++;

      } //end while

  } //end try

  catch (OleDbException oldbex)

  {

      MessageBox.Show(oldbex.Message);

  }

  catch (Exception ex)

  {

      MessageBox.Show(ex.Message);

  } //end catch

  finally

  {

      cn.Close();

  }

   

  When you have a loop and you want to use same command but just to change some parameter then you define the command out of the loop and in the loop just set the value of parameters that should be changed in the loop. I put also interest word also as parameter because it is the right way every changeable value the be parameter and you will not wory about type and format of that value. Closing connection should be in finally blok. I just start the exception handling but if you like to easy the way of solving problems you need to have good exception handling, especially when working with database.

  
  
  

• Gene Best

  Consider closing each reader after you done reading from it to free up connection and use parameterized command (or at least dispose of commands you no longer need).

  
  
  
• Error: ExecuteReader requires and open and available Connection. The connection's current status is open, Fetching.