[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