Presently I am attempting to create some coding short cuts for the website. Now to query a database and return an archive set 2 functions need to be known as:

GetDBConn returns a wide open ado connection resist the bond string passed in.

GetRS returns an archive set object in line with the ado connection and sql passed in.

set objConn = GetDBConn(connString, commandTimeout, connTimeout, cursorType)
set objRs   = GetRS(objConn, sql)

I wish to basically write individuals two like a single function but my question really becomes this... I think that you are designed to close and destroy your ado connections when completed with them. Basically dim the bond within the new function, query the database and return the record set, I can not close and destroy the bond within the function otherwise the record set came back through the function becomes useless (i.e. that connection object isn't clearly closed/destroyed). Is the fact that ok? Does it have negative effect on the SQL/Web Servers?


Not closing connections may cause issues with your SQL server's assets. Based on your hardware and just how many connections are becoming established, you might not watch a problem. Or it might result in the server inaccessible...

Utilizing it for record sets is most likely not recommended (too simple to forget to shut them), however opening / closing an association should not be considered a problem. I personally use the next two functions on my legacy Classic ASP sites:


Dim oConn

Sub openConn()
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open sConn 'connection string here'
End Sub

Sub closeConn()
    If IsObject(oConn) Then
        On Error Resume Next
        If oConn.State = 1 Then
        End If
        Set oConn = Nothing
        On Error Goto 0
    End If
End Sub

I Quickly include conn.asp and employ openConn() once towards the top of the page and closeConn() at the end.

The On Error Resume Next is generally regarded as like a "bad coding" practice, in this situation I believe it's appropriate otherwise you will be getting errors on all of the pages you utilize closeConn() on for individuals edge cases when oConn.State fails unconditionally.

I have always used the dbhelper.asp supplied by the MS reference application FmStocks The functions always return a disconnected recordset which means you never get in danger

The functions are just like that one:

Function RunSQLReturnRS(sqlstmt, params())
    On Error Resume next

    ' Create the ADO objects'
    Dim rs , cmd
    Set rs = server.createobject("ADODB.Recordset")
    Set cmd = server.createobject("ADODB.Command")

    ' Init the ADO objects  & the stored proc parameters'
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = sqlstmt
    cmd.CommandType = adCmdText
    cmd.CommandTimeout = 900 ' 15 minutes

    collectParams cmd, params

    ' Execute the query for readonly'
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
    If err.number > 0 then
        exit function
    end if

    ' Disconnect the recordset'
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
    Set rs.ActiveConnection = Nothing

    ' Return the resultant recordset'
    Set RunSQLReturnRS = rs

End Function