Error : execute reader requires an available open connection

Error : execute reader requires an available open connection

• Kaltenberg

  Responding to Ranjith_Msdn above
  
   I wouldn't use a Singleton object, but I would use something more robust like the Data Access block of the Microsoft Enterprise Library.
  
  

• Pr0g

  One common cause of this is when your connection is set up in such as way as to allow multiple page requests to all use the same connection object. See this thread for an example of this: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=374758&SiteID=1.

  How do you have your connection object defined Is it declared and opened locally inside a method whenever you need a connection, or do you have it declared at a higher scope

  Pooling is turned on by default for a reason. Is there a reason why you are turning it off If you are using the locally-scoped connection strategy as I mention above, you would definitely want to have it turned on, as that will give you the best performance. Turning it off should not be causing this error, but it's not the recommended setting.

  Thanks,
  Sarah

  
  
  

• Dribble

  The Singleton object here is allowing the same connection to be returned to multiple simultaneous callers of getConnection.

  Imagine this scenario:

  Thread 1 (i.e. one ASP.NET request) calls getConnection. A new SqlConnection is created and opened, but before it's returned from getConnection, Thread 2 (i.e. another ASP.NET request) starts executing getConnection. It creates a new SqlConnection, and reassigns the static oConn. At this stage, oConn is closed again, since it's new. Now Thread 1 finishes executing and returns the newly-created static oConn to the caller, instead of the instance that was originally created as a result of the call made by Thread 1. This can happen because until oConn is returned to the caller, there are no references to it outside of the static member variable in Singleton. Thread 1 assumes the connection is open, but it's really not. Now you will get the error you originally posted. However, things are more serious than just that error. What if Thread 2 goes ahead and opens the connection before Thread 1 tries to use it. That "closed connection" error won't occur, but now you have a situation where both threads are using the same connection. The class is not threadsafe, so this can cause all kinds of odd behavior beyond the one error you are currently seeing.

  You can use a strategy similar to what you're doing, but don't make oConn a static class variable. You can keep your static methods, but just create and open the new SqlConnection instance and return the new instance from the static method, without ever assigning it to a static member variable.

  Thanks,
  Sarah

  
  
  

• Gaara_btk

  Why do you have pooling set to false The fact that it is random makes me think you should enable pooling.
  
  You can also try to check the connection state of the connection
  
  If connectionobject.State = Closed Then connectionobject.Open
  
  

• Andrei Saygo

  Hi,

  I am using a singleton class to get a connection , the structure of which is as follows:

  public class Singleton

  {

  private static Singleton oInstance = null;

  private static SqlConnection oConn;

  private static string strConnection = ConfigurationSettings.AppSettings["ConnectionString"];

  private Singleton()

  {

  oConn = new SqlConnection(strConnection);

  oConn.Open();

  }

  public static Singleton getInstance()

  {

  if(oInstance == null)

  {

  oInstance = new Singleton();

  }

  return oInstance;

  }

  public static SqlConnection getConnection()

  {

  OpenConnection();

  return oConn;

  }

  private static void OpenConnection()

  {

  oConn = new SqlConnection(strConnection);

  oConn.Open();

  }

  This is defined inside the Data access class and connection is obtained as Singleton.getConnection();

  I have turned off the connection pooling and i am testing it.

  
  
  

• m1ch3ll3

  This example is outputting to a dropdown list

  just do a response.write("<tr><td>" & myMgrReader.GetString(0) & "</td><td>" & myMgrReader.GetString(1) & "</td></tr>")

  Dim myMgrConnection As SqlConnection

  myMgrConnection = New SqlConnection("packet size=4096;user id=user;data source=sqlserver;persist security info=True;initial catalog=database;password=pswd")

  Dim myMgrSelectQuery As String = ("select login_id, first_name+' '+last_name as fullname from ad_user where login_id in (select distinct manager from ad_user)order by 1")

  Dim myMgrCommand As New SqlCommand(myMgrSelectQuery, myMgrConnection)

  myMgrConnection.Open()

  Dim myMgrReader As SqlDataReader

  myMgrReader = myMgrCommand.ExecuteReader()

  While myMgrReader.Read

  DropDownList1.Items.Add(myMgrReader.GetString(1))

  End While

  myMgrReader.Close()

  myMgrConnection.Close()

  
  
• Error : execute reader requires an available open connection