Paging Records with GetRows

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.

Happy Programming!

Attachments: