Disconnected Recordsets in ASP

I am a huge fan of disconnected recordsets especially when we are displaying reports or any other database driven content on a web page. Once the data is returned to the recordset object, why maintain a connection to the database? Let it go do database things and let the web server play with the data. Here is my function. I call it getrs() because I am lazy. Most of it I copied from somewhere. I also assumes you have an open global database connection called db and a table called SQLError (for logging purposes).

Function GetRS(strSQL)

Const adOpenStatic = 3
Const adUseClient = 3
Const adLockBatchOptimistic = 4


Dim oRS
Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.CursorLocation = adUseClient
on error resume next
err.Clear
oRS.Open strSQL, db, adOpenStatic, adLockBatchOptimistic
if err <> 0 then
dim rsx
set rsx=server.CreateObject("adodb.recordset")
rsx.open "select * from sqlerror where 1=2",db,3,2
rsx.addnew
rsx("page")=request("page")
rsx("sql")=strsql
rsx("username")=session("uname")
rsx.update
rsx.close
set rsx=nothing
response.write "Your request created a system error. The error has been logged. Please make sure any fields you entered are the correct data types. "
response.end
end if
Set oRS.ActiveConnection = Nothing
Set GetRS = oRS
End Function

And you use it like this:

set rs=getrs("select username from users")
while not rs.eof
response.write rs("username") & "<br>"
rs.movenext
end
%>

It is simple and makes reading your code pretty damned easy. An added benefit is that you can move to the end of the recordset with rs.movelast and then move to the first with rs.movefirst as often as you’d like.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>