Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Close Recordset v.s. and Set Recordset=Nothing
- Thread starter asuknow
- Start date
you need to close a recordset before you can set = nothing.
always close the recordset as soon as you can. when you close the recordset, you also close it's connection to the db, helping reduce network traffic. (note - you need to close the connection to totally sever all ties with the DBMS)
set recordset = nothing helps VBScript free up the memory used by the object.
you need to close your recordsets, because if you don't, your ASP application can cause a memory leak and use up all the ASP threads.
you should set recordset = nothing, because I've heard that ASP doesn't do too great a job deallocating your objects, and that it's generally better and more efficient if you do it yourself.
hth
leo
always close the recordset as soon as you can. when you close the recordset, you also close it's connection to the db, helping reduce network traffic. (note - you need to close the connection to totally sever all ties with the DBMS)
set recordset = nothing helps VBScript free up the memory used by the object.
you need to close your recordsets, because if you don't, your ASP application can cause a memory leak and use up all the ASP threads.
you should set recordset = nothing, because I've heard that ASP doesn't do too great a job deallocating your objects, and that it's generally better and more efficient if you do it yourself.
hth
leo
DennisTheMenace
IS-IT--Management
If you need to read something from the db each time a page is loaded, I would do it something like this:
set db = server.createobject("adodb.connection"
set rs = server.CreateObject("adodb.recordset"
db.Open "DSN=yourDSN;UID=yourID;PWD=yourPwd;DATABASE=yourDB"
sql="SELECT variable1, variable2, variable3, variable4" & _
"FROM tablename " & _
"WHERE your matching criterea here"
'of course this is better with a stored procedure! ;-)
set rs = db.Execute(sql)
chrVariable1 = rs("variable1"
chrVariable2 = rs("variable2"
chrVariable3 = rs("variable3"
chrVariable4 = rs("variable4"
rs.Close
set rs=nothing
db.close
set db=nothing
chrVariable(N) can now be used thru out the ASP page and there is no connection or record set to deal with.
<HTML>
<BODY>
My First Variable = <%=chrVariable1%>
The Second one is = <%=chrVariable2%>
And of course 3rd = <%=chrVariable3%>
</BODY>
</HTML> =====================
Dennis B
set db = server.createobject("adodb.connection"
set rs = server.CreateObject("adodb.recordset"
db.Open "DSN=yourDSN;UID=yourID;PWD=yourPwd;DATABASE=yourDB"
sql="SELECT variable1, variable2, variable3, variable4" & _
"FROM tablename " & _
"WHERE your matching criterea here"
'of course this is better with a stored procedure! ;-)
set rs = db.Execute(sql)
chrVariable1 = rs("variable1"
chrVariable2 = rs("variable2"
chrVariable3 = rs("variable3"
chrVariable4 = rs("variable4"
rs.Close
set rs=nothing
db.close
set db=nothing
chrVariable(N) can now be used thru out the ASP page and there is no connection or record set to deal with.
<HTML>
<BODY>
My First Variable = <%=chrVariable1%>
The Second one is = <%=chrVariable2%>
And of course 3rd = <%=chrVariable3%>
</BODY>
</HTML> =====================
Dennis B
- Thread starter
- #4
Thank you all for your replies.
Sometimes I have big headache of closing a recordset.
An error would appear saying that the recordset cannot
be closed. (could anyone tell me why this could happen?)
In this case I have to set the recordset to Nothing to
clear it away from the server memory.
If I need to open a particular recordset everytime I load
a web page ( and I don't want to set that recordset to a
session variable), then which way is more efficient:
set that RS to NOTHING or just close it?
Sometimes I have big headache of closing a recordset.
An error would appear saying that the recordset cannot
be closed. (could anyone tell me why this could happen?)
In this case I have to set the recordset to Nothing to
clear it away from the server memory.
If I need to open a particular recordset everytime I load
a web page ( and I don't want to set that recordset to a
session variable), then which way is more efficient:
set that RS to NOTHING or just close it?
DennisTheMenace
IS-IT--Management
If you do a search and find nothing... your record set is empty and you don't need to close it:
sql="SELECT variable1, variable2, variable3, variable4" & _
"FROM tablename " & _
"WHERE your matching criterea here"
set rs = db.Execute(sql)
if rs.eof then
response.write("nothing found"
set rs=nothing
db.close
set db=nothing
else
chrVariable1 = rs("variable1"
chrVariable2 = rs("variable2"
chrVariable3 = rs("variable3"
chrVariable4 = rs("variable4"
rs.Close
set rs=nothing
db.close
set db=nothing
end if
Also
If you are simply "insert into" then you would not really have a record set to close. You would simply set it to nothing:
sql="INSERT into tblYourTable set chrVar1=3 " & _
"WHERE your matching criterea here"
set rs = db.Execute(sql)
set rs=nothing
db.close
set db=nothing
Hope this explains a little...) =====================
Dennis B
sql="SELECT variable1, variable2, variable3, variable4" & _
"FROM tablename " & _
"WHERE your matching criterea here"
set rs = db.Execute(sql)
if rs.eof then
response.write("nothing found"
set rs=nothing
db.close
set db=nothing
else
chrVariable1 = rs("variable1"
chrVariable2 = rs("variable2"
chrVariable3 = rs("variable3"
chrVariable4 = rs("variable4"
rs.Close
set rs=nothing
db.close
set db=nothing
end if
Also
If you are simply "insert into" then you would not really have a record set to close. You would simply set it to nothing:
sql="INSERT into tblYourTable set chrVar1=3 " & _
"WHERE your matching criterea here"
set rs = db.Execute(sql)
set rs=nothing
db.close
set db=nothing
Hope this explains a little...
) =====================Dennis B
- Thread starter
- #7
Thank Denis and DesperateCoder for your reply.
I am always wondering:
Why do so many tech experts suggest not to store recordset
or connection in session or application variables?
To my konwledge, open a connection and a recordset also
consumes a lot of server performance (maybe connection
pooling can help this out), under this circumstance, why
shouldn't we store a recordset or a connection in
a session or an application variable if we want to use
that recordset or connection quite often?
Thanks.
I am always wondering:
Why do so many tech experts suggest not to store recordset
or connection in session or application variables?
To my konwledge, open a connection and a recordset also
consumes a lot of server performance (maybe connection
pooling can help this out), under this circumstance, why
shouldn't we store a recordset or a connection in
a session or an application variable if we want to use
that recordset or connection quite often?
Thanks.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question
