Disconnected recordsets in ADO / classic ASP

Disconnected recordsets can be used to keep the time, in which a database connection is open, down to a minimum.

If for example you need to loop through large amounts of records, perhaps heavily manipulating data before use, the connection to the database can be open for very long. Maybe long enough to be a problem if there are many concurrent hits on the page.

One solution to this is to disconnected recordsets, that keep the database connection open just long enough for the recordset to be build, whereafter the connection is closed. You can then take your time looping through the recordset, without using database resources.

The implementation is based on 4 Guys From Rolla's article Using Disconnected Recordsets.

Function GetRS(strSQL) ' Returns a disconnected recordset
  Const adOpenStatic = 3    
  Const adUseClient = 3
  Const adLockBatchOptimistic = 4
  Dim objCon
  Dim objRS
  Set objCon = Server.CreateObject("ADODB.Connection")
  objCon.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\test.mdb;")
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.CursorLocation = adUseClient
  objRS.Open strSQL, objCon, adOpenStatic, adLockBatchOptimistic
  Set objRS.ActiveConnection = Nothing
  Set GetRS = objRS
  objCon.Close
  Set objCon = Nothing
End Function

Function GetPersonList() ' Returns formatted data from the discon. recordset
  Dim objRS
  Dim strOutput
  Set objRS = GetRS("SELECT fullName, birthDate FROM persons")
  If Not objRS.State = 0 Then
    If Not objRS.EOF Then
      Do While Not objRS.EOF
        strOutput = strOutput & objRS.Fields("birthDate").value & _
          " " & objRS.Fields("fullName").value & "<br />" & vbcrlf
        objRS.MoveNext()
      Loop
    objRS.Close
    Set objRS = Nothing
    Else
      strOutput = "No items found"
    End If
  Else
    strOutput = "No output"
  End If
  GetPersonList = strOutput
End Function

Response.Write(GetPersonList())
Tags: asp, databases
Page last updated 2007-11-14 21:43. Some rights reserved (CC by 3.0)

Search

Feeds

RSS 2.0 feed All content
RSS 2.0 feed ajax
RSS 2.0 feed asp
RSS 2.0 feed aspnet
RSS 2.0 feed bicycle
RSS 2.0 feed copenhagen
RSS 2.0 feed databases
RSS 2.0 feed denmark
RSS 2.0 feed diy
RSS 2.0 feed dotnet
RSS 2.0 feed html
RSS 2.0 feed japan
RSS 2.0 feed javascript
RSS 2.0 feed modding
RSS 2.0 feed photography
RSS 2.0 feed utilities
RSS 2.0 feed vbscript