Classic ASP Discussion - Closing a recordset

Print Page | Close Window

Closing a recordset

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=10598
Printed Date: 31 March 2026 at 2:42pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Closing a recordset

Posted By: witje
Subject: Closing a recordset
Date Posted: 25 May 2004 at 9:14am

just a simple question, does a recordset needs to be closed eatch time it is used ?

for example
set rs = con.execute(blabla)
set rs = con.execute(blalbal)
rs.close

or should that be
set rs = con.execute(blabla)
rs.close
set rs = con.execute(blalbal)
rs.close

Replies:

Posted By: KCWebMonkey
Date Posted: 25 May 2004 at 1:31pm

set rs = con.execute(blabla)
rs.close
set rs = con.execute(blalbal)
rs.close

Posted By: dpyers
Date Posted: 25 May 2004 at 1:38pm

A good rule of thumb is "If you Set it, Close it - as soon as possible.

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

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

Posted By: Semikolon
Date Posted: 25 May 2004 at 1:47pm

if you Open, then Close
if you Set, then Set to Nothing

not all methods has Close options

Posted By: witje
Date Posted: 26 May 2004 at 3:52am

wierd.. i mean you dont close photoshop befor you open a new image now do you?

Anyway.. so i cant do i big cleanup at the bottom of the page like

  If isObject(Rs) Then
   If Rs.State = 1 Then Rs.Close
  End If
  If isObject(Rs_) Then
   If Rs_.State = 1 Then Rs.Close
  End If
  If isObject(Rs__) Then
   If Rs__.State = 1 Then Rs.Close
  End If
  Con.Close
  Set Con = Nothing

So will it be a smart idea to do somthing like this?

Sub oCon
  Set Con = Server.CreateObject("ADODB.Connection")
  Con.Open ConStr
 End Sub
 Sub oRs(Rs, SQL)
  If isObject(Rs) Then
   If Rs.State = 1 Then Rs.Close
  End If
  Set Rs = Con.Execute(SQL)
 End Sub
 Sub cCon
  If isObject(Rs) Then
   If Rs.State = 1 Then Rs.Close
   Set Rs = Nothing
  End If
  If isObject(Rs_) Then
   If Rs_.State = 1 Then Rs-.Close
   Set Rs_ = Nothing
  End If
  If isObject(Rs__) Then
   If Rs__.State = 1 Then Rs__.Close
   Set Rs__ = Nothing
  End If
  Con.Close
  Set Con = Nothing
 End Sub
Dim Rs
oCon()
oRs Rs, "select * from whatever"
  [..]
oRs Rs, "select * from somthingelse"
cCon()

It would be great if somthing like that is posible to use...

Posted By: Semikolon
Date Posted: 26 May 2004 at 4:08am

of course you can place it in functions/subs, but you must ALWAYS close a recordset/connection/file/whatever before opening a new one with the same object

Posted By: witje
Date Posted: 26 May 2004 at 4:33am

ok thx, i think im going to create a class for this :)

One more question i forgot about earlier do i also have to set the Recordset object to nothing befor using it again?

Posted By: Semikolon
Date Posted: 26 May 2004 at 6:35am

no.. you set it to Nothing before you do a redirect and at the end of the page.. or after you are finished with it.. the sooner the better

Posted By: dpyers
Date Posted: 26 May 2004 at 9:02am

Semikolon wrote:

if you Open, then Close
if you Set, then Set to Nothing

not all methods has Close options

I meant to say that - <sigh>

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

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

Posted By: Semikolon
Date Posted: 26 May 2004 at 1:12pm

okay

Posted By: witje
Date Posted: 27 May 2004 at 4:37pm

Thx for the replys...

Okey im closing all my records now automaticly once i open a new one but my website is still slow

The Open/Close process goes like this now:

Set Rs = Con.Execute("bla")
Rs.Close
Set Rs = Con.Execute("bla")
Rs.Close
Set Rs = Server.CreateObject("ADODB.Recordset")
Set Rs.ActiveConnection = Con
Rs.Open "bla"
Rs.Close
Set Rs = Server.CreateObject("ADODB.Recordset")
Set Rs.ActiveConnection = Con
Rs.Open "bla"
Rs.Close
Set Rs = Con.Execute("bla")

etc...

Set Rs = Nothing
Con.Close
Set Con = Nothing

so is that the proper way of opening and closing records?

Posted By: witje
Date Posted: 28 May 2004 at 6:06pm

anyone?

Somehow my website is really slow... ofcourse it could also be the host but i gotta make sure im doing the record-set and the connection stuff good first...

Maybe this questions are a bit noobish for you guys but i just started asp 4 weeks ago, i always use to make websites with php.

Posted By: dpyers
Date Posted: 28 May 2004 at 6:33pm

The only thing I would note that's db specific would be to make sure you're using DSN-less connections and to use the Jet4.0 driver.

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

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

Posted By: witje
Date Posted: 28 May 2004 at 7:18pm

Im using a DNS less connection with Jet4.0

SQLDBPath = Server.MapPath("db/****.mdb")
SQLDBPassword = ""
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SQLDBPath & ";Jet OLEDB:Database Password=" & SQLDBPassword & ";"

Set Con = Server.CreateObject("ADODB.Connection")
Con.Open ConStr

guess its my host then...

Posted By: Semikolon
Date Posted: 30 May 2004 at 6:14am

or your database.. it it's a big acess database you should consider moving to SQL Server

Posted By: dpyers
Date Posted: 30 May 2004 at 8:01am

Access gets out of whack pretty quickly if your site does a lot of deletions and/or inserts. I's really just one big sequential file. Doing a compact and repair on it will recover any space from deletions and restructure the inexes to work faster.

Also, make sure that any lookup fields are indexed.

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

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