Database Discussion - Check if RS and Conn are open

Print Page | Close Window

Check if RS and Conn are open

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=14073
Printed Date: 28 March 2026 at 4:57pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Check if RS and Conn are open

Posted By: theSCIENTIST
Subject: Check if RS and Conn are open
Date Posted: 02 March 2005 at 11:48am

How can one check whether a recordset and connection was left open, so it can be closed dynamically in the footer file. Is there any way to know this?

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Replies:

Posted By: michael
Date Posted: 02 March 2005 at 12:05pm

Well I don't know if that's gonna work in asp, but may be worth a try. Thus in vb.net you can do something like

Dim con As New OleDb.OleDbConnection
..........
If con.State = ConnectionState.Open Then
con.Close()
End If

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: theSCIENTIST
Date Posted: 02 March 2005 at 12:16pm

Nope, that's not available in ASP, thanks anyway, I wish it was, the thing is that I know all conns and RSs should be closed as soon as no longer needed, however, I would like to cerify myself that if it wasn't closed it would be in the footer, kind of better late then never.

Also, if a conn and RS is left open what could be the problems?

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: ljamal
Date Posted: 02 March 2005 at 2:50pm

Try

If IsObject(Conn)=True then
Conn.Close
set Conn=Nothing
end if

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: theSCIENTIST
Date Posted: 06 March 2005 at 11:25am

Thanks ljamal, but it is not working, it errors out with the obvious 'Object required' on pages that have the Conn closed, IsEmpty and IsNull also don't work.

Jezzz, don't tell me there's no way to know if a Conn is open or not in ASP?

Anyone?

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: Mart
Date Posted: 06 March 2005 at 12:21pm

If you use IsNull you know you need to change the evaluation to False, right?

Posted By: Gullanian
Date Posted: 06 March 2005 at 12:40pm

It's probably more efficient if you actually go back in your code and re-write it so that you always close them when you know they are open, I had a similar problem before, it's probably only going to get worse and more complicated than it needs to be.

Posted By: theSCIENTIST
Date Posted: 06 March 2005 at 2:39pm

I realised that, nevertheless, I just added this to my footer:

<%

'/* Last chance to close objetcs */

On Error Resume Next



'/* Debug info */

'If Err.Number = 0 Then Response.Write("rsCommon state: " & rsCommon.State)

'If Err.Number = 0 Then Response.Write("<BR>")

'If Err.Number = 0 Then Response.Write("adoCon state: " & adoCon.State)



'/* Close the damn thing */

Set rsCommon = Nothing

adoCon.Close

Set adoCon = Nothing

%>

The (On Error Resume Next) allows for it to not produce errors in case conns are closed.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: ljamal
Date Posted: 06 March 2005 at 11:28pm

theSCIENTIST wrote:

Thanks ljamal, but it is not working, it errors out with the obvious 'Object required' on pages that have the Conn closed

Just use, on error resume next to escape the error. This is the closest you will get to what you are seeking.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: dpyers
Date Posted: 07 March 2005 at 12:12am

The trick to making that work - besides the error escape - is to make sure you use the same onject name for all connections.

-------------

Lead me not into temptation... I know the short cut, follow me.

Posted By: theSCIENTIST
Date Posted: 08 March 2005 at 7:58pm

Yes, I'm using the same obj name, and also came to the same conclusion as you, thx.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: sifra
Date Posted: 21 March 2005 at 12:22am

It is not necessary to use error trapping. Below is code that sets up three connections, and leaves them in various states. The cleanup code works regardless of whether the connection is closed, open or nothing.

I recommend you create a VBScript asp class to host the connection object, in the terminate event, free your objects.

<%
' set up three connections and leave them in various states
Dim oConn1
Dim oConn2
Dim oConn3

Dim strSQL
strSQL = "Provider=SQLOLEDB;Server=myserver;User ID=me;Password=mypass;Database=mydb;"

Set oConn1 = Server.CreateObject("ADODB.Connection")
oConn1.Open strSQL

Set oConn2 = Server.CreateObject("ADODB.Connection")
oConn2.Open strSQL

Set oConn3 = Server.CreateObject("ADODB.Connection")
oConn3.Open strSQL

' now close connection 1 and set to nothing
oConn1.Close
Set oConn1 = Nothing

' close connection 2 but dont free memory
oConn2.Close

' leave conneciton 3 active

<html>
<head>
<title>Connection object detection test</title>
</head>

<body>

<%
' cleanup code for each connection is exactly the same
If Not oConn1 Is Nothing Then
 Response.Write "oConn1 has not been set to nothing"
 If oConn1.State > 0 Then
 Response.Write " oConn1 is still active = " & oConn1.State
 oConn1.Close
 End If
 Set oConn1 = Nothing
Else
 Response.Write "oConn1 has already been freed"
End If

If Not oConn2 Is Nothing Then
 Response.Write "oConn2 has not been set to nothing"
 If oConn2.State > 0 Then
 Response.Write " oConn2 is still active = " & oConn2.State
 oConn2.Close
 End If
 Set oConn2 = Nothing
Else
 Response.Write "oConn2 has already been freed"
End If

If Not oConn3 Is Nothing Then
 Response.Write "oConn3 has not been set to nothing"
 If oConn3.State > 0 Then
 Response.Write " oConn3 is still active" & oConn3.State
 oConn3.Close
 End If
 Set oConn3 = Nothing
Else
 Response.Write "oConn3 has already been freed"
End If

%>
</body>
</html>