Published: Wednesday, July 05, 2000
Paging Records with GetRows
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
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:
Download the code to GetRows.asp in text format
View the live demo!
Mukul Sabharwal, aged 14, male, develops in asp
(jscript and vbscript), php, cfm and c++. has a crush
on oracle db and likes to use mysql!
