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!
|
Effective governance for lean development isn’t about command and control. Instead, the focus is on enabling the right behaviors and practices through collaborative and supportive techniques. Hear from Scott Ambler on how it is far more effective to motivate people to do the right thing than it is to force them to do so. Learn how to form a lightweight, collaboration-based framework that reflects the realities of modern IT organizations.
FREE! Go There Now!
|
|
|
|
Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo.
FREE! Go There Now!
|
|
|
|
Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager.
FREE! Go There Now!
|
|
|
|
This tutorial shows new users of IBM WebSphere Business Monitor Version 6.0.2 how to perform the "Hello World" equivalent for monitoring business process applications. It is intended to help you get familiar with the capabilities of the product.
FREE! Go There Now!
|
|
|
|
This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”.
FREE! Go There Now!
|
|
|
|
This paper is about the critical role that a discipline called integrated requirements management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrating, building or maintaining software. It also looks at ways that automated IBM Rational® products can work together to help you use requirements in the very best way.
FREE! Go There Now!
|
|
|
|
Get a free trial download of IBM Lotus Forms V3.0 (formerly Workplace Forms), which provides a zero-footprint eForms solution to help you automate and move forms-based business processes off the desktop and onto the Web. With Lotus Forms, you can extend applications beyond the firewall by creating a single electronic form document ready for use in both thick and Web 2.0 thin client format.
FREE! Go There Now!
|
|
|
|
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
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!
|
|
|
|
The Eclipse community is constantly working to extend Eclipse's functionality. In this webcast, learn about some of the most important and feature-rich projects under development. From multi-language support to plug-in development, tune in to see what Eclipse is capable of now.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
