VB6.0 ADO question

[Guest]

Most of the following code was obtained from Microsoft. (Unsure what I've
changed.) However, it does not work correctly. I'm hoping someone can tell
me why. It actually works until the "GetObjectContext.SetComplete" line us
unremarked. It also bothers me that neither the recordset nor the connection
are closed/terminated nicely.

Public Function ListAll() As ADODB.Recordset

Dim strSQL As String
Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection

On Error GoTo ErrorHandler

'Form the SQL SELECT statement
strSQL = "SELECT CategoryId, Name, ModifiedDate, CreatedDate " & _
"FROM Categories ORDER BY Name"

set rs = new ADODB.Recordset
rs.CursorLocation = adUseClient

'Execute the SQL command to the database, using the recordset Open method.
rs.Open strSQL, "FILEDSN=" & fileDSN, adOpenKeyset, adlLockReadOnly,
adCmdText

GetObjectContext.SetComplete

Set ListAll=rs

Exit Function

ErrorHandler:
If Not rs Is Nothing Then
Set rs=Nothing
End If

'Roll back MTS transaction set.
Err.Raise Err.Number, SetErrSource(modName, "ListAll"), Err.Description

GetObjectContext.SetAbort
Err.Raise Err.Number, SetErrSource(modName, "ListAll"), Err.Description

End Function

Thanks in advance,

Christmas May

 

[Guest]

Hi Christmas May

You can close like this before Exit Sub/Function
and the error handler

'close connection
conn.Close
Set ocnn = Nothing
Set rs = Nothing

Exit Sub/Function

I don't have server but if you would like a sample to test that
connects to Access's Northwind that does work post back and
I'll post you one. It will work on server you will need to change
connection and cat # ect.

Good Luck
TK

 

[Jamie Collins]

You can close like this before Exit Sub/Function
and the error handler

'close connection
conn.Close
Set ocnn = Nothing
Set rs = Nothing

TK's right: you *can* explicitly close and release your objects if it
bothers you. But you shouldn't let it worry you if you don't.

My experience of ADO is that if you let an active/open connection go
out of scope the connection gets closed and released without any
problems. That said, it's probably a good idea to close a connection
when you are finished with it; for this reason I usually use
disconnected recordsets.

Setting to Nothing is a different matter. Unless you have a specific
reason (e.g. reusing the variable and testing it for Nothing) there is
no need to set the local variables to Nothing. VB's garbage collector
will take care of them when they do out of scope. If you happened to
use a With conn in your code it would create an 'invisible' Connection
variable which you would be unable to explicitly set to Nothing. Would
that bother you? Again, it shouldn't.

I recently came across the following in Matt Curland's book, Advanced
VB (p110): "[DAO provides] another example of poor teardown code. DAO
has Close methods that must be called in the correct order, and the
objects must be released in the correct order as well (Recordset before
Database, for example). This single poor object model behavior has led
to the misconception that VB leaks memory unless you explicitly set all
the local variables to nothing at the end of a function. This is a
completely false notion in a well-designed object model. VB can clear
the variables faster at the End Sub line than you can from code, and it
checks the variables even if you explicitly release your references.
Any effort you make is duplicated."

As I said before, my experience is that ADO *is* a well-designed object
model meaning it doesn't matter in which order the Close methods are
called or that they are explicitly called at all.

Jamie.

--