[thelist] Re: (apparently) Unclosed database connections
Ken Schaefer
ken at adOpenStatic.com
Mon Jul 21 04:10:08 CDT 2003
Hi there,
Where do you "Dim" rs?!? The first reference I see to it is in your Sub, in
which case it would be local to the sub only...
If you are just pulling back records, the following might be a better way to
organise your code (put the subs into an include file, and just include at
the top of the page)
<%
Dim objConn ' ADODB.Connection
Dim strSQL ' String: SQL statement
Dim arrResults ' Array of strings: results of query
strSQL = _
"SELECT field1, field2 " & _
"FROM table1"
' Open Connection
Set objConn = DBConnOpen(Application("connString"))
' Return results as array
arrResults = GetArrayFromSQL(objConn, strSQL)
' Dispose of connection
Call objDispose(objConn, True, True)
' Supporting functions
Sub DBConnOpen( _
ByVal strConnectionString _
)
Dim objToOpen
Set objToOpen = Server.CreateObject("ADODB.Connection")
objToOpen.Open strConnectionString
Set DBConnOpen = objToOpen
End Sub
Function GetArrayFromSQL( _
ByRef objConn, _
ByVal strSQL _
Dim objRS
Set objRS = objConn.Execute strSQL
If not objRS.EOF then
GetArrayFromSQL = objRS.getRows
End If
Call objDispose(objRS, True, True)
End Function
Sub objDispose( _
ByRef objToDispose, _
ByVal blnClose, _
ByVal blnNothing _
)
If blnClose then
objToDispose.Close
End If
If blnNothing then
Set objToDispose = Nothing
End If
End Sub
Cheers
Ken
----- Original Message -----
From: "Bruce MacKay" <b.mackay at massey.ac.nz>
To: <thelist at lists.evolt.org>
Sent: Saturday, July 19, 2003 9:23 PM
Subject: [thelist] Re: (apparently) Unclosed database connections
: Thanks folks for the responses.
:
: An error message which I've rightly/wrongly associated with the onset of
: server lockup is of the form....
:
:
: Microsoft VBScript runtime error '800a01fb'
: An exception occurred: 'open'
: /studyhome.asp, line 50
:
: I googled this message tonight and the few answers available also
mentioned
: MDAC version
:
: For completeness,
: studyhome.asp....
: ================================
: call sbMakeAConnection(csMainDatabase,1)
: sqlstring = "SELECT messageID FROM messages WHERE
: (receiverID='"&studentID&"' AND messagedate > "&fnSQLTime(messageevent)&"
: AND message_read=False);"
: rs.open sqlstring <-------- line 50
: ==================================
:
: sub sbMakeAConnection(byVal db,byVal rset1)
: set conn = Server.CreateObject("ADODB.Connection")
: conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& csDatabasePath
: & db & ".mdb"
: if rset1=1 then
: set rs = Server.CreateObject("ADODB.Recordset")
: set rs.ActiveConnection = conn
: rs.CursorType = adOpenForwardOnly
: rs.LockType = adLockReadOnly
: end if
: end sub
:
: I set rset to 1 when I'm pulling a recordset from the database, 0
: otherwise. If I read your example correctly Ken, my code structure should
: protect me against implicitly opened connections.
:
: Thanks again - will check the MDAC version
:
: Bruce
:
: --
: * * Please support the community that supports you. * *
: http://evolt.org/help_support_evolt/
:
: Evolt.org conference in London, July 25-27 2003. Register today at
http://evolt.org.uk
:
: For unsubscribe and other options, including the Tip Harvester
: and archives of thelist go to: http://lists.evolt.org
: Workers of the Web, evolt !
More information about the thelist
mailing list