Paging Records with
By Mukul Sabharwal
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
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
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:
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
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!)
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.
Next, we need to create our array and use
GetRows to populate the array.
At this point we're done using our Connection and Recordset objects, so we can go ahead and
these objects and set them to
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).
Finally we need possibly need to display the Next/Previous links. A Previous link is needed if our starting
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
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.
GetRows.aspin text format