Handling ADODB connections in classic ASP

Question

Hi, I'm an ASP.NET C# guy who has to go back to classic ASP, and need some help with it.

First, look at this two functions (I know in VBScript the comments are declared by ' and not by // but the syntax highlighter here messes up with ').

First version:

Function DoThing

    Dim Connection, Command, Recordset

    Set Connection = Server.CreateObject("ADODB.Connection")
    Set Command = Server.CreateObject("ADODB.Command")
    Set Recordset = Server.CreateObject("ADODB.Recordset")

    Connection.Open "blah blah blah"
    Command.CommandText = "blah blah blah"
    Recordset.Open Command, Connection

    If Recordset.EOF = False Then

        While Recordset.EOF = False Then

            // Do stuff.

            Recordset.MoveNext

        WEnd

    Else

        // Handle error.

    End If

    Recordset.Close
    Connection.Close

    Set Recordset = Nothing
    Set Command = Nothing
    Set Connection = Nothing

End Function

Second version:

Function DoAnotherThing

    Dim Connection, Command, Recordset

    Set Connection = Server.CreateObject("ADODB.Connection")
    Set Command = Server.CreateObject("ADODB.Command")

    Connection.Open "blah blah blah"

    Command.ActiveConnection = Connection
    Command.CommandText = "blah blah blah"

    Set Recordset = Command.Execute

    If Recordset.EOF = False Then

        While Recordset.EOF = False Then

            // Do stuff.

            Recordset.MoveNext

        WEnd

    Else

        // Handle error.

    End If

    Recordset.Close
    Connection.Close

    Set Recordset = Nothing
    Set Command = Nothing
    Set Connection = Nothing

End Function

Now, let's start with the questions:

Question #1:

What's best,

Connection.Open "blah blah blah"
Command.CommandText = "blah blah blah"
Recordset.Open Command, Connection

or

Connection.Open "blah blah blah"

Command.ActiveConnection = Connection
Command.CommandText = "blah blah blah"

Set Recordset = Command.Execute

?

Question #2:

When doing SELECTs should I use disconnected recordsets like

Set Recordset = Command.Execute

Command.ActiveConnection = Nothing

Connection.Close

Set Connection = Nothing

?

Question #3:

Do I have to call

Recordset.Close
Connection.Close

even though I do

Set Recordset = Nothing
Set Command = Nothing
Set Connection = Nothing

right below (i.e. the garbage collector when invoked by the Set Stuff= Nothing also .Close them before the destruction)?

Question #4:

Do I have to include the

Recordset.Close
Connection.Close

Set Recordset = Nothing
Set Command = Nothing
Set Connection = Nothing

stuff even though the function ends there, and these objects goes out of scope, and the garbage collector (should) takes care of them?

Question #5:

Do classic ASP have pooled connection, so I can open and close them with every command, or should I pass a common connection object around?

Thanks in advance for your help, Andrea.

Answer 1

Question #1.

Both.
It depends on whether you want all the parameters Recordset.Open has, and also on your personal preference.

Question #2.

You can, but it doesn't matter. It'll hardly make any difference -- your command has completed, and, though the connection object is opened, all locks and stuff are released on the server.

Question #3.

There's no garbage collector in VBScript, there's reference counting.

An object is destroyed, with all its finalizers called, when there's no more references to it.
In case of pooled connections, that can make a difference -- because the server may retain a reference to the connection, setting the variable to Nothing may not do anything actually because reference count will not reach zero (and you don't have to set it to Nothing anyway, it will be done automatically upon exiting the function).

If the server isn't going to pool the connection, it probably won't have a reference to it, at which point setting the variable to Nothing (explicitly or implicitly) will also close the connection.

My personal preference here is to call Close, so that I know where my stuff is, but to not set variables to Nothing. That will work fine on both pooled and not pooled connections.

Question #4.

See question 3.

Question #5.

In a web environment, you always want to open a new Connection object when you need it, except for the cases when several methods of yours must do their bits within the same transaction.