ado.net, ms-access and releasing connections

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/i...QA/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]

Sorry, if you use OleDb, have you tried OleDbCommand.ReleaseObjectPool() to
release opened connections?

On 16 Mar 2004 04:14:56 -0800, dulcinea_wt@hotmail.com (Jon Ted) wrote:

¤ 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/i...QA/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]

I noticed that the Command object you are creating has a reference to the Connection object. Does it
make any difference if you dispose of that or Close the Connection?


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)