Test for Connection or Recordset open?

[+Bob+]

Is there a reliable way to test to see if a recordset or connection is
still in existence? Some way of testing for the object?

I have some conditions such that the RS or Conn may still exist at the
end of some runs. Rather than setting a series of indicators, I'd like
to just test directly for the object if that's reasonable and
reliable.

Thanks,

 

[Bob Barrows]

Is there a reliable way to test to see if a recordset or connection is
still in existence? Some way of testing for the object?

The ADO documentation can be found here:
http://msdn2.microsoft.com/en-us/library/ms675532.aspx

Look up the State property.

I have some conditions such that the RS or Conn may still exist at the
end of some runs. Rather than setting a series of indicators, I'd like
to just test directly for the object if that's reasonable and
reliable.

Hmm, it sounds as if you are doing a little more than should be done in
an ASP page ...

Hopefully, you are following the good practice of delaying the opening
of your connection until just before you need it and closing it as soon
as the last database activity is complete. And hopefully you're not
trying to persist ADO objects in Application or Session:
http://www.aspfaq.com/show.asp?id=2053

 

[+Bob+]

The ADO documentation can be found here:
http://msdn2.microsoft.com/en-us/library/ms675532.aspx

Look up the State property.

OK - according to this code:
http://msdn.microsoft.com/en-us/library/ms675960.aspx

you can do this:
ErrorHandler:
' clean up
If Not Cnxn1 Is Nothing Then
If Cnxn1.State = adStateOpen Then Cnxn1.Close
End If
Set Cnxn1 = Nothing

They test for the connection object and remove it if it exists and set
it equal to nothing unconditionally. Does that imply that setting an
object equal to nothing does not require a test i.e. it will not throw
an error if the object does not exist?

Also, wouldn't setting the object to nothing accomplish the same
thing? Or will closing the object first be better in terms of
releasing memory and the d/b connection (real world, not theoretical)

Hmm, it sounds as if you are doing a little more than should be done in
an ASP page ...

Nothing to funky. I just prefer to test objects directly if possible
rather than tracking/passing extra indicators just to check for
existence.

 

[Daniel Crichton]

+Bob+ wrote on Tue, 07 Apr 2009 12:00:14 -0400:

On Tue, 7 Apr 2009 11:08:29 -0400, "Bob Barrows"

OK - according to this code:
http://msdn.microsoft.com/en-us/library/ms675960.aspx

you can do this:
ErrorHandler:
' clean up
If Not Cnxn1 Is Nothing Then
If Cnxn1.State = adStateOpen Then Cnxn1.Close
End If
Set Cnxn1 = Nothing

They test for the connection object and remove it if it exists and set
it equal to nothing unconditionally. Does that imply that setting an
object equal to nothing does not require a test i.e. it will not throw
an error if the object does not exist?

You can set any variable to Nothing without an error being thrown.

As to error handlers, note that ASP cannot use labels to jump to like the
example you posted. You could however use something like this:

On Error Resume Next
If Not Cnxn1 Is Nothing Then
If Cnxn1.State = adStateOpen Then Cnxn1.Close
End If
Set Cnxn1 = Nothing
Err.Clear

which will let you run those statements without an error being thrown.
However, you then have to watch out for code following throwing errors
without you knowing about it. In my own applications I tend to use On Error
Resume Next right near the top of the application code, and then at critical
intervals within the ASP test for the value of Err.Number, eg,.

If Err.Number <> 0 Then
'call my error handling function here
End If

and the function I have tends to display a curt message, log the error to
the event log for me to check later (I have a number of apps that monitor
the event log for messages and then let me know via various means), and
terminate running the page if required.

Also, wouldn't setting the object to nothing accomplish the same thing?
Or will closing the object first be better in terms of releasing memory
and the d/b connection (real world, not theoretical)

I always make sure I .close an object (in the case of ADO objects that
support that method) before setting it to Nothing. I've never looked
in-depth into whether this makes a huge difference, but I prefer to try and
clean up anything I use to be on the safe side.

 

[+Bob+]

You can set any variable to Nothing without an error being thrown.

As to error handlers, note that ASP cannot use labels to jump to like the
example you posted. You could however use something like this:

On Error Resume Next
If Not Cnxn1 Is Nothing Then
If Cnxn1.State = adStateOpen Then Cnxn1.Close
End If
Set Cnxn1 = Nothing
Err.Clear

Yes... point taken. That was stolen from the MS code bank.

which will let you run those statements without an error being thrown.
However, you then have to watch out for code following throwing errors
without you knowing about it. In my own applications I tend to use On Error
Resume Next right near the top of the application code, and then at critical
intervals within the ASP test for the value of Err.Number, eg,.

If Err.Number <> 0 Then
'call my error handling function here
End If

Yea... I have samples like that around here. Of course, every web app
I code plans for the unexpected (ahem .

I always make sure I .close an object (in the case of ADO objects that
support that method) before setting it to Nothing. I've never looked
in-depth into whether this makes a huge difference, but I prefer to try and
clean up anything I use to be on the safe side.

I hear you. I'm just wondering if there's really a need. I do close
objects (e.g. record sets) and then reuse them with a new open. But,
does it really make sense to even execute the extra operations at the
end of the stream ? If you can set to nothing and blow it all away
in one operation, it would seem like the way to go.

 

[Bob Barrows]

I hear you. I'm just wondering if there's really a need. I do close
objects (e.g. record sets) and then reuse them with a new open. But,
does it really make sense to even execute the extra operations at the
end of the stream ? If you can set to nothing and blow it all away
in one operation, it would seem like the way to go.

Yes, there have been memory leaks reported caused by failure to close
child ADO objects before their parents were closed. Sometimes this
failure prevents the connection from closing, causing it to stay in
memory. It is much more important to close these objects than it is to
set them to nothing. If I was going to skip one step, it would be that
one since objects are cleaned up automatically once they go out of scope
.... if they are able to! Closing them in the right order guarantees that
they will be able to be cleaned up by the garbage collector.

You're really trying to save yourself from typing a single line of code?
Please... use a colon and put them both on the same line ...