ASPkey - A Resource Site for Web Developers

	Services
	Portfolio
	Products
	Sample Code
	Quotes
	Blog
	Feedback
	Basket
	Checkout
	Tools
	.net Assemblies

Your basket has: Total items: 0
Subtotal: $0.00

Article
Paging through a recordset

When there's a large database of information available to the viewer it sometimes helps to limit the number of records rendered per page.

The user may also have a preference for how many records to show, depending upon their monitor size and resolution. Here is a script example that will render a user-selected number of records per page, and allow the user to step through pages.

<%@ LANGUAGE="VBSCRIPT" %>
<%

'-------------------------------------------------------------------
'Set up usual headers, define constants and database
'connection for script
'-------------------------------------------------------------------

    Response.Buffer=true
    Response.AddHeader "cache-control", "private"
    Response.AddHeader "pragma", "no-cache"
    Response.ExpiresAbsolute = #January 1, 1990 00:00:01#
    Response.Expires=Now()-1
    Response.AddHeader "Cache-Control", "must-revalidate"
    Response.AddHeader "Cache-Control", "no-cache"

    Const strDatabaseName = "Northwind.mdb"
    Const strTableName = "Employees"

    Const adOpenKeySet = 1
    Const adLockReadOnly = 1

    strScriptName = Request.ServerVariables("SCRIPT_NAME")
    db = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= " & Server.MapPath(strDatabaseName)
    set RS = Server.CreateObject("ADODB.RecordSet")

'-------------------------------------------------------------------
'If this is first call to script then get user preference for
'number of records per page
'-------------------------------------------------------------------

If Request("RecsPerPage") = "" then%>
<HR color="#0000FF">
<BIG>Paging Through a Recordset</BIG>
<HR color="#0000FF">
<%
Call (selectRecordCount)
Response.end
End if

'-------------------------------------------------------------------
'If returning to script use submitted parameter for records per page
'and retrieve recordset
'-------------------------------------------------------------------

intRecsPerPage = Request("RecsPerPage")
sSQL = "SELECT * from " & strTableName
rs.open sSQL,db,adOpenKeySet,adLockReadOnly

'-------------------------------------------------------------------
'Use following select case to page through records to display for page
'-------------------------------------------------------------------

rs.PageSize = intRecsPerPage
intPageCount = rs.PageCount

Select Case Request("Action")
    case "<< First"
        intpage = 1
    case "< Prev"
        intpage = Request("intpage")-1
        if intpage < 1 then intpage = 1
    case "Next >"
        intpage = Request("intpage")+1
        if intpage > intPageCount then intpage = IntPageCount
    Case "Last >>"
        intpage = intPageCount
    case else
        intpage = 1
end select

'-------------------------------------------------------------------
'Set up for rendering output to page and loop through recordset
'-------------------------------------------------------------------

%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Visual N++">
<TITLE>Paging Through a Recordset</TITLE>
</HEAD>
<BODY bgColor=White text=Black>

<HR color="#0000FF">
<BIG>Paging Through a Recordset</BIG>
<HR color="#0000FF">

<%rs.AbsolutePage = intPage%>

<FONT color="red" ><B><I>Page: <%=Intpage & " of " & intpagecount%></I></B></FONT><BR>
<HR color="#C0C0C0">
<%
For intRecord = 1 To rs.PageSize
    Response.Write "<FONT color='blue' >Record: " & rs("EmployeeID") & "</FONT> "
    if intRecord mod 2 then Response.write ("<FONT color=""#008080"" >")
    Response.Write rs.Fields("FirstName") & " "
    Response.Write rs.Fields("LastName") & "<br>"
    if intRecord mod 2 then Response.write ("</FONT>")
    rs.MoveNext
If rs.EOF Then
    Response.write "<FONT color=""#FF0000"" >-- End of Records --</FONT>"
    Exit For
end if

Next

rs.Close

'-------------------------------------------------------------------
'Show form with buttons to move through pages
'-------------------------------------------------------------------

%>
<HR color="#C0C0C0">

<form name="MovePage" action="<%=strScriptName%>" method="post">
<input type="hidden" name="intpage" value="<%=intpage%>">
<input type="submit" name="action" value="<< First">
<input type="submit" name="action" value="< Prev">
<input type="submit" name="action" value="Next >">
<input type="submit" name="action" value="Last >>">
<input type="hidden" name="RecsPerPage" value="<%=intRecsPerPage%>">
<BR>
</form>

<%

'-------------------------------------------------------------------
'Give user option to change records per page at any time
'-------------------------------------------------------------------

Call (selectRecordCount)

set rs = Nothing
%>

</BODY>
</HTML>
<%

'-------------------------------------------------------------------
'subroutine for generating droplist
'Input parameter is name of table to be used
'Counts records and lists by step increment
'If large number of records exist adjust step size simply predefine
'an array of records per page (5,10, 20) etc.
'-------------------------------------------------------------------

Sub droplist(strTableName)

    SQL = "SELECT * FROM " & strTableName

    rs.open SQL,db,3
    numrecords = rs.recordcount

Response.write "Records per page:"
Response.write "<SELECT Name = 'RecsPerPage' SIZE='1'>"
Response.write "<OPTION SELECTED>1</OPTION>"
     For i = 2 to numrecords step 1
                 Response.write "<OPTION>" & i & "</OPTION>"
     next
Response.write "</SELECT>"

rs.Close
set rs = Nothing

end Sub

'-------------------------------------------------------------------
'subroutine to display user selection droplist form
'-------------------------------------------------------------------

Sub SelectRecordCount()
    response.write ("<Table>")
    response.write ("<Form name='SelectRecordCount' action= '"& strScriptName & "' method='post'>")
    response.write ("<TD>")
    Call droplist(strTableName)
    response.write ("</TD>")
    response.write ("<TD><input type='submit' name='submit' value='Submit'></TD>")
    response.write ("</Table>")
end sub

%>

That's the end. Enjoy!

Return To Top

Article