Some strange errors occurring in my asp.net website.
I was using the MS ApplicationBlocks.data.dll to connect o my SQL database. Once I started testing my website on IIS as opposed to the ASP.NET development server, I started getting connection pool errors.
So, like anyone would, I searched for every instance of opening a recordset and ensured that each one was closed right after being done with it.
Still no luck.
So, I wrote my own data wrapper function, very basic, which returns a DataReader and explicitly closes the connection each time.
Then, I started getting an "ExecuteReader requires an open and available Connection. The connection's current state is closed.." error. I've modfied my function as below, which catches the error, clears the Connection Pools an re-opens the connection. This function hasn't caused any problems in my testing, except that every 9-11 clicks I notice a much longer page load as the exception is caught and handled.
I really don't want to leave this code as is. Can anyone shed some light onto the possible root cause?
Private Function _getRS(ByVal strSql As String) As System.Data.SqlClient.SqlDataReader Dim conn As New System.Data.SqlClient.SqlConnection(strSQLconn) Dim cmd As New System.Data.SqlClient.SqlCommand(strSql, conn) Dim rs As System.Data.SqlClient.SqlDataReader Try conn.Open() rs = cmd.ExecuteReader Catch ex As Exception System.Data.SqlClient.SqlConnection.ClearPool(conn) System.Data.SqlClient.SqlConnection.ClearAllPools() System.GC.Collect() conn.Open() rs = cmd.ExecuteReader Finally End Try Return rs cmd.Dispose() cmd = Nothing conn.Close() conn.Dispose() conn = Nothing End Function