Does setting an ADO Command's ActiveConnection = Nothing close the underlying SQL Connection?

Question

I have a customer who's Classic ASP application is generating ASP_0147 errors. The first thing I'm checking is that they're closing and releasing SQL/ADO resources timeously.

Their code has the following pattern:

Function GetXXXXRecordSet()
  Set objConn = Server.CreateObject("ADODB.Connection")
  With objConn 
    .CursorLocation = 3 ''adUseServer (default)
    .ConnectionString = strConnectionString
    .Open
  End With

  Set objCmd = Server.CreateObject("ADODB.Command")
  Set objCmd.ActiveConnection = objConn

  '' Build command object to call SQL stored proc, snipped for brevity

  Set objRs = Server.CreateObject("ADODB.RecordSet")
  objRs.Open objCmd, ,3,4 '' Cursor=adOpenStatic, Locktype=adLockBatchOptimistic

  '' Return Recordset
  Set GetXXXXRecordSet = objRs

  If Not objCmd Is Nothing Then
    objCmd.ActiveConnection = Nothing  '' Should this use a Set statement?
    Set objCmd = Nothing
  End If
  If Not ObjRs Is Nothing The Set objRs = Nothing
End Function

Does setting an ADO Command's ActiveConnection = Nothing close the underlying SQL Connection or does that have to be closed explicitly?

Also should the line:

objCmd.ActiveConnection = Nothing

be:

Set objCmd.ActiveConnection = Nothing

Oddly the first version doesn't generate an error which is why I ask.

It's been so long since I looked at ADO and my knowledge is somewhat rusty.

Answer 1

Yes, you are correct that the setting of an object to nothing releases memory by using 'Set'...

Set objCmd.ActiveConnection = Nothing

Hope this helps, Best regards, Tom.

Answer 2

Closing a ADODB RS + CONN:

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

So should be: Set objCmd.ActiveConnection = Nothing indeed

GL!

source:

http://www.aspwebpro.com/tutorials/asp/dbconnectionclose.asp

Answer 3

Been awhile for all this but wouldn't setting it to nothing just clear the object. I bet if you monitored SQL Server the connection isn't terminated then.

Answer 4

My understanding was always that setting ActiveConnection to Nothing did not close the connection it just removed it from that object, this is useful for things like Recordsets where you want a fixed read-only snapshot of the Recordset (combined with settings the correct cursor options) and so don't need to keep the connection live for that Recordset (but may need the connection still open for other operations)

AFAIK only actually calling objConn.Close closes the connection and Set objConn = Nothing frees up the memory

Answer 5

VBScript is garbage-collected, and even provides very clear and explicit guarantees regarding the GC's timing. Setting a local variable to nothing right before it goes out of scope is completely redundant because the end of the function will do the same thing, and the GC will clean up the object.

The only question is whether the ADODB.Connection's destructor frees the database resources. I'm 99% certain that it does. If so, just letting the Connection object go out of scope will free up all associated resources.