I've been tearing my hair out over this one and I can't seem to find a solution :(
Here is the error message in question:
ExecuteScalar requires an open and available Connection. The connection's current state is closed. at System.Data.OleDb.OleDbConnection.CheckS
I get this error on certain functions from time to time - not always - simply refreshing the page fixes the problem.
We are using MS SQL 2005 and 2000 on another machine where the behavior is the same. I'm having a hard time pin pointing the cause of the problem since a refresh of the exact same page will provide the expected results with no errors. I'm not sure if it's related or not, but another issue we are having are sessions that seem to expire for no reason at random time rather then the pre-set 25 minutes. This issue is quite annoying but again, almost impossible to pin point the reason for it.
There are no changes being made to the bin folder nor the web.config file. The system error & application log doesn't show any unusual entry. So my question I guess is how to find the reason for the 2 problems or just the first one if it's not related to each other :) Thanks in advance.
We are using ASP.NET (VB) 2.0 (not beta) on IIS 6.0 (and 5.5 on another machine), together with the MS SQL server (2005 and 2000).
Here is just a sample page how I structure the page:
Start the page on the by calling biding the datagrid if we don't have a post back.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Call BIND()
End If
End Sub
Here is the sub that I call when I need to open the connection:
Public Sub OPEN_DB()
Try
MyConn = New OleDb.OleDbConnection(ConfigurationManager.AppSettings("ConnectionString"))
If MyConn.State <> ConnectionState.Open Then MyConn.Open()
Catch ex As Exception
MyConn.Dispose()
HttpContext.Current.Response.Write(ex.Message)
HttpContext.Current.Response.End()
End Try
End Sub
Here is where the datagrid would be called and bind, DB connection would be
opened with the object name MyConn.
Sub BIND()
Try
Call OPEN_DB()
Try
Dim DALIndex As New DataAccessLayerSpecific
dgGRID.DataSource = DALIndex.ListIndex(SORT)
dgGRID.DataBind()
Catch ex As Exception
Call READ_EXCEPTION(ex)
End Try
Catch ex As Exception
' Read Exception
Finally
MyConn.Dispose()
End Try
End Sub
On DataGrid Item bind, a function (below) is called to calculate something, still using
the opened MyConn connection.
Private Sub dgGRID_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgCLIENTS.ItemDataBound
Dim item As ListItemType = e.Item.ItemType
Select Case item
Case ListItemType.Item, ListItemType.AlternatingItem, ListItemType.SelectedItem
' Call the GetSomething procedure, with the connection that has been opened
' in the BIND() procedure.
tdCELL.text = GetSomething(e.Item.DataItem("Useranme"))
End Select
End Sub
Function that's called from the dgGRID_ItemDataBound, uses the MyConn connection object.
Public Function GetSomething(ByVal strUSERNAME As String) As String
Dim RES As String = ""
Try
Dim SQL As String = "SELECT field FROM MySampleTable WHERE ID = 1"
Dim MyCMD As New OleDb.OleDbCommand(SQL, MyConn)
RES = CType(MyCMD.ExecuteScalar(), String) ' < Here is where it'd break
MyCMD.Dispose()
Catch ex As Exception
Call READ_EXCEPTION(ex)
End Try
Return RES
End Function
Sudden DB connection failures & session expiry problems - How to find the cause
Sven De Bont
From the code snippet you gave, it looks like the connection is declared inside your module, but not inside your class. If so, that's bad. :)
If it's not inside your class, it's global to your module, which means that all page requests are using the same instance, potentially at the same time. If this is the only error you are seeing, you are probably getting lucky. The worst case scenario is that under stress you would start to see all kinds of other errors, because the OleDbConnection class is not threadsafe.
At a minimum you should declare it inside your class, but it would be even better to only declare connections locally within your methods. The best practice is to keep them as locally scoped as possible, and only have the connection open for the period of time you need it. Connection pooling will prevent this from creating a lot of overhead back and forth to the database.
If you declare the variable inside of your class (and don't mark it Shared), then each instance of the class will have its own connection, and if you don't share your class instances in session state or anything like that, then you will prevent the connections from being used across multiple page requests at the same time.
Thanks,
Sarah
Mark Costello
Hi Sarah, thanks for your reply. The MyConn object is declared in a module that's part of the project. The MyConn variable is shared by the application, almost every page uses it.
Public Module Database
Public MyConn As OleDb.OleDbConnection
... etc ....
End Module
No, I don't store anything like that in the sessions.
kirke
M3talh
What is the scope of MyConn, and how is it declared if you are sharing this among multiple page requests, you could be closing it while another request is using it. If it's set up as a non-shared instance variable on your Page class, you should be okay as far as that goes.
If you are not already doing this, it would be better to declare MyConn locally, instead of sharing it among different pages.
I don't know if this is related to the session problem or not. If you are not storing any database objects (particularly the connection) in session state, I doubt they are related.
Thanks,
Sarah
jcmikkelsen