Paging Records using GetRows

[note: originally written for 4GuysFromRolla.com (an internet.com company) focusing on Microsoft ® ASP & .NET Framework content. Written by Mukul Sabharwal]

Original Copy can be found here

As databases grow in size, displaying information in organized, consumable "chunks" becomes more and more difficult. For example, if a database table contains thousands or even hundreds of rows, simply dumping the entire contents of the table can overwhelm the user and be difficult to pick through. As developers it is our job to provide information in digestible quantities. One common solution is to page database table results. That is, only a small number of database records are shown per screen, accompanied by Next and Previous buttons allowing the user to step through the database results in sensible steps.

There are already a number of great 4Guys articles on paging database records. If you are using SQL Server it is strongly recommended that you use a stored procedure-based approach, as outlined in the article Paging through Records Using a Stored Procedure. A script that uses AbsolutePage, PageSize, and PageCount (three ADO properties designed to allow for paging) can be seen at Paged Table Display, authored by Charles Carroll and Jeff Emrich.

This article provides an alternative way to page through database records. This technique uses the GetRows method of the Recordset object. GetRows dumps the Recordset data into a two-dimensional array. The first dimension of the array stores the column information while the second dimension stores the row information. The following snippet of code illustrates how to use GetRows: 


'... assumes a Connection object (objConn) has been created and opened

'    and that a recordset object (objRS) has already been instantiated ...

objRS.Open "SELECT * FROM Table1", objConn

Dim aArray

aArray = objRS.GetRows

'Now let's retrieve the total number of columns and rows in our array

Dim iColumnCount, iRowCount

iColumnCount = UBound(aArray, 1)

iRowCount = UBound(aArray, 2)

'Output the first column of the first row

Response.Write aArray(0,0)
Pretty simple, eh? Note that the array returned is zero-based. This is because arrays in modern versions of the VBScript Scripting Engine are zero-based. If you are using an older scripting engine version these arrays might be one-based. To find out what scripting engine version you have, be sure to read: Determining the Server-Side Scripting Language and Version.

Now, to page records successfully we need to know two things: the starting index in our array and how many records per page we'd like to display. We'll let this information be passed in through the QueryString. For example, the URL:

http://www.yourserver.com/GetRows.asp?Start=0&Offset=15

would display 15 records starting from record 0 (i.e., records 0 through 14). Our script is fairly straight-forward: we start by reading in the QueryString values Start and Offset. (For the remainder of the article I will present this script piece-by-piece. If you just want to get your hands on the script you can download the working script (also available from a link at the end of the article). You can also try out the live demo!) 

<%@ Language = VBSCRIPT %>
<% Option Explicit %>
<%
  Dim iStart, iOffset
  iStart = Request("Start")
  iOffset = Request("Offset")

  if Not IsNumeric(iStart) or Len(iStart) = 0 then
    iStart = 0
  else
    iStart = CInt(iStart)
  end if

  if Not IsNumeric(iOffset) or Len(iOffset) = 0 then
    iOffset = 10
  else
    iOffset = Cint(iOffset)
  end if

  Response.Write "Viewing " & iOffset & " records starting at record " & _
                 iStart & "<BR>"
Next we need to create our Connection and Recordset objects. We then need to open our connection to the database and retrieve information into our Recordset object via a SQL statement. 

  Dim objConn, objRS
  Set objConn = Server.CreateObject("ADODB.Connection")
  objConn.Open "DSN=DSN Name"

  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Open "SELECT * FROM TableName", objConn

Next, we need to create our array and use GetRows to populate the array. 
  Dim aResults
  aResults = objRS.GetRows

At this point we're done using our Connection and Recordset objects, so we can go ahead and Close these objects and set them to Nothing. 

  objRS.Close
  Set objRS = Nothing

  objConn.Close
  Set objConn = Nothing

We are now ready to display the correct subset of rows from our array aResults. First we find the total number of columns and rows using a pair of UBound statements. Next we loop from the starting row index (iStart) through the next iOffset records (iStart + iOffset - 1). 

  Dim iRows, iCols, iRowLoop, iColLoop, iStop
  iRows = UBound(aResults, 2)
  iCols = UBound(aResults, 1)

  If iRows > (iOffset + iStart) Then
    iStop = iOffset + iStart - 1
  Else
    iStop = iRows
  End If

  For iRowLoop = iStart to iStop
    For iColLoop = 0 to iCols
      Response.Write aResults(iColLoop, iRowLoop) & " "
    Next
    Response.Write "<BR>"
  Next

Finally we need possibly need to display the Next/Previous links. A Previous link is needed if our starting array index, iStart, is greater than the beginning index, 0. We need to display a Next link if the last record we displayed is less than the total number of rows in the array aResults. 

  Response.Write "<P>"
  if iStart > 0 then
    'Show Prev link
    Response.Write "<A HREF=""GetRows.asp?Start=" & iStart-iOffset & _
                   "&Offset=" & iOffset & """>Previous " & iOffset & "</A>"
  end if

  if iStop < iRows then
    'Show Next link
    Response.Write " <A HREF=""GetRows.asp?Start=" & iStart+iOffset & _
                   "&Offset=" & iOffset & """>Next " & iOffset & "</A>"
  end if
%>

Well, that about wraps it up, a neat way to page through records. There is a lot of customization that can go into this script, such as adding new options to the search, making the output look all nice and pretty, and other fun tweaks. Be sure to take a moment and view the live demo.



Microsoft ASP is a registered trademark of the Microsoft Corporation. .NET Framework is a registered trademark of the Microsoft Corporation. This website does not endorse or claim to be related to Microsoft Corporation in anyway. 4GuysFromRolla.com is a registered trademark of the Internet.com Company.