When needing to display a large number of records a useful technique is to use recordset paging to present the data as a number of pages showing X number of records per page. Although most sites that do this allow you to select the record count per page before you submit the search I often miss the ability to dynamically adjust the record count and simultaneously jump to a certain page. Okay, I lie, I missed it once while using Dejanews. But the minor inconvenience coupled with the general fustration that comes with trying to find anything on Deja these days pushed me into coding a solution, so here goes...
You can view the demo here
I'll go through the code a section at a time, firstly demonstrating the basic paging technique. If you're only after the basics you can stop at that point and your code will work fine, however if you choose to read on I'll show you how we can use a little Javascript to provide some rarely seen flexibility.
Basic recordset paging
<% @ LANGUAGE="VBScript" %>
<%
' Good Things To Do ™
Option Explicit
Response.Buffer = True
' Constants we'll use for opening the recordset.
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adUseClient = 3
Const adCmdText = 1
' connection object, recordset, connection string, SQL string. I'll leave you to guess which...
Dim conn, rs, connString, qString
' Open the database connection
connString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("demo.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connString
%>
<HTML>
<HEAD> <TITLE> Database paging example </TITLE> </HEAD>
<BODY>
The next bit of code allows us to pass page and record count requests via form elements or as part of the URL (eg paging.asp?page=5). We'll be using the URL method for our page links and later, a form for submitting the desired record count per page.
<%
' Variables for the page number we want to show, and the total number of pages
Dim mypage, numpages
' Variables for the total number of records, and the number of records we should show on one page
Dim numrecs, pagesize
' Get the page number we need to display from Form or QueryString
mypage = CInt( Request("page") )
' If we aren't given one display page 1
If mypage=0 Then mypage=1
' Get the requested number of records per page
pagesize = CInt( Request("recs") )
' Change 10 to whatever you want the default display to be
If pagesize = 0 Then pagesize = 10
Next, the few lines of code it takes to make paging work. Setting CacheSize is optional, though the default value is 1 line so it's good to change it.
' The code we need to set up a recordset object for paging
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.PageSize = pagesize
rs.CacheSize = pagesize
rs.CursorLocation = adUseClient
We're opening the recordset as read only, pass through once for efficiency. The adCmdText constant gives another slight performace boost by saving the system having to figure out which type of command we're using.
' Open the recordset
qString = "SELECT * FROM Table1"
rs.Open qString, conn, adOpenForwardOnly, adLockReadOnly, adCmdText
The recordset automatically processes the records into pages for us, all we have to do is tell it which page we want (using rs.AbsolutePage)
' Get the number of pages and records
numpages = rs.PageCount
numrecs = rs.RecordCount
' Just in case we have a bad request
If mypage > numpages Then mypage = numpages
If mypage < 1 Then mypage = 1
' This line sets the current page
rs.AbsolutePage = mypage
Now we output a few variables and begin writing our records.
' Output the total number of records in the recordset
Response.Write("<P> <B>" & numrecs & " records found.</B>" )
' Write the current page number and number of pages (eg "Page 4 of 7")
Response.Write("<BR> Page " & mypage & " of " & numpages & "</P>")
' Center align the display of records and links
Response.Write("<CENTER>")
' Loop through the records for this page writing each on a new line
Dim i
For i=1 To pagesize
' Make sure we don't go past the last record
If NOT rs.EOF Then
' Write a couple of fields from each record separated with a |
Response.Write(rs("Name") & " | " & rs("Data") & " <BR>")
' Point to the next record
rs.MoveNext
End If
Next
Response.Write("<P>")
And now a little menu with links to the other pages (eg 1-10 | 11-20 | 21-30 etc). Each link will refer back to our file, passing values for "page" and "recs" to be picked up by the code we wrote earlier. You could easily modify this section to produce Back and Next links if desired.
' Loop through the number of pages writing a link for each
Dim x, lb, ub
For x=1 To numpages
' lb is the lowest record number on that page, ub is the highest
lb = (x-1) * pagesize + 1
ub = x * pagesize
' Makes sure the final link doesn't extend past to the # of records
If ub > numrecs Then ub = numrecs
' Don't display the current page as a hyperlink
If x <> mypage Then
' Example output: <A HREF=paging.asp?page=4;recs=25>75-100</A>
Response.Write("<A HREF=paging.asp?page=" & x & "&recs=" & pagesize & ">" & lb & "-" & ub & "</A>")
Else
' The active page
Response.Write(lb & "-" & ub)
End If
' Writes | as a separator if we're not at the last link
If x <> numpages Then Response.Write(" | ")
Next
Response.Write("</CENTER>")
' Clean-up
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>
At this point you can finish the code with </BODY> </HTML> and you'll have a working paging system. However if you carry on I'll demonstrate how we can improve the functionality.
Selecting the record count per page
We can do this easily with a simple form:
<FORM NAME=myform METHOD=post ACTION=paging.asp>
<!-- A drop down box for selecting the number of records to display -->
Show
<SELECT NAME=recs>
<OPTION VALUE=5> 5 <!-- Change values to suit. -->
<OPTION VALUE=10> 10 <!-- You can add/remove options -->
<OPTION VALUE=25> 25 <!-- if required, just make sure -->
<OPTION VALUE=50> 50 <!-- each has a numeric VALUE -->
<OPTION VALUE=100> 100 <!-- parameter. -->
</SELECT>
records per page
<BR>
<INPUT TYPE=submit VALUE="GO!">
</FORM>
When the form is submitted it passes the VALUE parameter of the option selected to our paging file (paging.asp), keyed to the name "recs". We already have the code in place to get the value:
pagesize = CInt(Request("recs"))
It's for this reason we're using Request() rather than Request.QueryString(), which wouldn't be able to find values coming from the form.
Selecting the page number
As above we'll be using another <SELECT> element to allow the user to change the page being displayed. The complication here is that the number of pages available needs to change on the fly as the user selects various record per page values. For this we can use a little Javascript to dynamically calculate and fill the second drop down box based on the value in the first.
Again, you can alternatively finish off here by ending the file with </BODY> </HTML> and skipping the rest of the code.
To start with we'll need to add a new select box to our form.
<!-- A dropdown box for selecting the page to display -->
Display page
<SELECT NAME=page>
<OPTION>
<OPTION>
<OPTION>
<OPTION>
<OPTION>
<OPTION>
<OPTION>
<OPTION>
</SELECT>
Why the empty <option> tags? Well we need those to get around a bug in Netscape Navigator (a bug in Navigator? Neverrrrr). The number of tags you put in here determines the number of options displayed when you click on the box, even after we later add/remove/replace the contents. Take that as a general hint to use more than one...
Next, the script:
<SCRIPT LANGUAGE="JavaScript">
// The total number of records in the recordset
NUM_RECORDS = <%=numrecs%>; // Note how we generate the value
// A function that populates the 'page' box based on the 'recs' box
function updateBoxes(theFormObj)
{
// Get the records per page selected from the dropdown box
var selectedRecs = theFormObj.recs.options[theFormObj.recs.selectedIndex].value;
// Calculate the number of pages available for the value selected
var numpages = Math.ceil(NUM_RECORDS / selectedRecs);
// Wipe the current contents of the page box
var numOptions = theFormObj.page.length;
for(var i=0 ; i<numOptions ; i++) {
theFormObj.page.options[0] = null;
}
// Fill the box with successive page numbers
for(var j=0 ; j<numpages ; j++) {
theFormObj.page.options[j] = new Option(j+1,j+1);
}
// Select the first option in the box
theFormObj.page.selectedIndex = 0;
}
</SCRIPT>
Now we must go back and modify the first select element so that it calls the function every time the user selects a new option:
<SELECT NAME=recs OnChange="updateBoxes(myform)">
Finally, we call the function manually to fill the page box the first time the page is loaded.
<SCRIPT> updateBoxes(document.myform); </SCRIPT>
</BODY>
</HTML>
That's it! All the code for the file is listed above, but you can get the cleaner (ie without my waffle) version here. Feel free to use it as you wish; it's a pretty simple matter to incorporate the javascript into existing pages, you just need to change a few variables/element names and you're all set.
Tom Smith
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By aspfree
 developerWorks - FREE Tools!
|
The IBM DB2 Deep Compression ROI tool is designed for DBA’s and IT management personnel to perform a clinical analysis of the cost savings gained from the Storage Optimization feature of DB2 9 for Linux, UNIX and Windows. The feature, also known as Deep Compression, compresses data that lies within a database by up to 80% at times.
FREE! Go There Now!
|
|
|
|
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
FREE! Go There Now!
|
|
|
|
Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download IBM DB2 Express-C 9.5, a no-charge version of DB2 Express 9 database server. DB2 Express-C offers the same core data server base features as other DB2 Express editions and provides a solid base to build and deploy applications developed using C/C++, Java, .NET, PHP, and other programming languages.
FREE! Go There Now!
|
|
|
|
Download a free trial version of IBM Rational Developer for System z, software that can help you deliver core development capabilities; the power of Java Platform, Enterprise Edition (Java EE); and rapid application development support to diverse enterprise application development teams. With comprehensive development tools to help create, deploy and maintain traditional enterprise and composite applications, Rational Developer for System z enables developers with different technical backgrounds to easily participate in important technology projects.
FREE! Go There Now!
|
|
|
|
In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset.
FREE! Go There Now!
|
|
|
|
Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services.
FREE! Go There Now!
|
|
|
|
Try the latest version of IBM Rational Manual Tester V7.0.1 by downloading a free trial from IBM developerWorks. This manual test authoring and execution tool promotes test step reuse to reduce the impact of software change on testers and business analysts and addresses the needs of teams performing at least a portion of their testing manually.
FREE! Go There Now!
|
|
|
|
Join the IBM Watchfire team for an informative discussion on techniques and best practices to proactively manage Web application security and how to effectively build application security testing into the software development lifecycle (SDLC). In this Software Delivery Platform webcast you will learn: How to better understand potential web application security vulnerabilities, best practices and how to effectively integrate application security testing into the software development lifecycle, the importance of detecting and removing software vulnerabilities during application development.
FREE! Go There Now!
|
|
|
|
IBM Lotus Notes 8 provides a wide range of developers the ability to provide customized, integrated user interfaces via composite applications and via custom sidebar and toolbar plug-ins. This webcast provides you with tips and techniques to use with out-of-the-box capabilities of Lotus Notes 8, and survey how you can share useful components within your own company and within a larger community.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
