J
Jon Ted
I am a newbie developing a small .net application (without VS.NET)
that utilizes an access2000 .mdb. It is does not need /have concurrent
users.
My problem:
After using the function below a few times (by reaccessing the same
web page), the connection object refuses to release the connection,
and all subsequent connections fail.
As far as i can gather from searching online, this is because of
..net's non-deterministic garbage collection system. I have tried using
connection.dispose, connection.nothing or GC.collect, but to no avail.
Any suggestions for handling this problem? (make the connection
available for immediate reuse)
PS: I managed to find this on
http://msdn.microsoft.com/msdnmag/issues/03/12/WebQA/default.aspx
"Using the Jet database engine from ASP.NET can be difficult. Jet
works best if all threads in the same process use the same single
instance of a connection (this is the exact opposite of SQL Server™)."
Not sure if this applies to handling concurrent requests, but if its
applicable, any tips on how to ensure the above?
[CODE sample]
Public Overloads Function SendQuery(ByVal Sql As String, _
ByRef dt As DataTable, ByRef ErrMsg As String) As Integer
ErrMsg = ""
Try
If Not Me.OpenConnection(ConnectString, ConnSS) Then
Return -10 ' can't connect to db
End If
Me.dbCmdSS.CommandText = Sql
dbCmdSS.Connection = ConnSS
daSS = New OleDbDataAdapter(dbCmdSS)
daSS.Fill(dt)
CloseConnection(ConnSS)
Return dt.Rows.Count
Catch ex As System.Exception
ErrMsg = ex.ToString
Return -1
End Try
End Function
' opens a connection to the database and return true if successful
Public Overloads Function OpenConnection(ByVal ConnStr As String,
ByRef Conn As OleDbConnection) As Boolean
Dim i As Integer
Try
Conn.ConnectionString = ConnStr
Conn.Open()
Return True
Catch
Return False
End Try
End Function
' closes a connection
Public Overloads Function CloseConnection(ByRef Conn As
OleDbConnection) As Boolean
Try
Conn.Close()
Conn.Dispose()
Conn = Nothing
GC.Collect()
Return True
Catch
Return False
End Try
End Function
[end Code sample]
that utilizes an access2000 .mdb. It is does not need /have concurrent
users.
My problem:
After using the function below a few times (by reaccessing the same
web page), the connection object refuses to release the connection,
and all subsequent connections fail.
As far as i can gather from searching online, this is because of
..net's non-deterministic garbage collection system. I have tried using
connection.dispose, connection.nothing or GC.collect, but to no avail.
Any suggestions for handling this problem? (make the connection
available for immediate reuse)
PS: I managed to find this on
http://msdn.microsoft.com/msdnmag/issues/03/12/WebQA/default.aspx
"Using the Jet database engine from ASP.NET can be difficult. Jet
works best if all threads in the same process use the same single
instance of a connection (this is the exact opposite of SQL Server™)."
Not sure if this applies to handling concurrent requests, but if its
applicable, any tips on how to ensure the above?
[CODE sample]
Public Overloads Function SendQuery(ByVal Sql As String, _
ByRef dt As DataTable, ByRef ErrMsg As String) As Integer
ErrMsg = ""
Try
If Not Me.OpenConnection(ConnectString, ConnSS) Then
Return -10 ' can't connect to db
End If
Me.dbCmdSS.CommandText = Sql
dbCmdSS.Connection = ConnSS
daSS = New OleDbDataAdapter(dbCmdSS)
daSS.Fill(dt)
CloseConnection(ConnSS)
Return dt.Rows.Count
Catch ex As System.Exception
ErrMsg = ex.ToString
Return -1
End Try
End Function
' opens a connection to the database and return true if successful
Public Overloads Function OpenConnection(ByVal ConnStr As String,
ByRef Conn As OleDbConnection) As Boolean
Dim i As Integer
Try
Conn.ConnectionString = ConnStr
Conn.Open()
Return True
Catch
Return False
End Try
End Function
' closes a connection
Public Overloads Function CloseConnection(ByRef Conn As
OleDbConnection) As Boolean
Try
Conn.Close()
Conn.Dispose()
Conn = Nothing
GC.Collect()
Return True
Catch
Return False
End Try
End Function
[end Code sample]