Click here to Skip to main content
14,032,595 members
Click here to Skip to main content
Add your own
alternative version


61 bookmarked
Posted 21 Jun 2000
Licenced CPOL

ADO Recordset Paging in ASP

, 21 Jun 2000
Rate this:
Please Sign up or sign in to vote.
How to display multiple pages of records in ASP

Sample Image - rsPaging.gif


Every once in a while, I come across the task of displaying a large number of records in ASP pages. The good example is displaying the results of a search. Most of the time, I do not know the number of records that I have to display in advance. In addition to this, as the usage of the application grows, the size of the database will grow accordingly. That leaves me as well as anyone with the similar application requirements no other choice, but to develop some kind of algorithm to display records in the smaller chunks - pages.

Everyone is familiar with the way search results are displayed by Internet search engines. You get the first page of results that are limited to some number of records (20 for example) and some navigational links to go to the first, previous, next or the last page. Some sites give you the ability to go directly to specific page number, some use a mixture of both.

So how does one implement record paging mechanism in ASP? Specifically, how do we implement record paging using ADO?

Let’s pretend that we have a database with the table called tblItem that is used to store information about our Items (whatever they are?). Let me also imagine that one of the fields in tblItem is called ItemName. We are given a task of creating a set of pages to give a user an ability to search for the items by the ItemName field. We decided to make a set of two pages. One page will display the search form and one for the results of the search.

Please excuse me, but I will skip all the variable declarations and HTML formatting.

The first page should be easy. It’s a standard HTML form that could look something similar to this:

<FORM ACTION="results.asp" METHOD="GET">
Item Name: <INPUT TYPE="text" NAME="Keyword"> <INPUT TYPE="submit" VALUE=" Find ">

The second page is where all the magic should happen. This is what the second page (results.asp) should be able to do:

  1. Receive the Keyword that user has entered.
  2. Search the database for records containing Keyword.
  3. Display a page of resulting records.
  4. Provide user with some navigation links to display more pages of results if needed.

1. Receive the Keyword

Receiving the Keyword is as easy as:

Keyword = Trim(Request.QueryString("Keyword"))

2. Search the Database for Records Containing Keyword

Now we have everything we need to get an ADO recordset with the items that contain our keyword in their ItemName.

First, we create a SQL statement that will do the search:

SQL = "SELECT * FROM tblItem WHERE ItemName LIKE '%" & Replace(Keyword, "'", "''") & "%'"

Notice that I’ve used Replace function to double single quotes in the search string. Without it, if user enters a single quote in his/her Keyword, you will receive an error.

' Create and open ADO recordset
Set RS = Server.CreateObject("ADODB.Recordset")
RS.CursorLocation = 3 ' adUseClient
RS.Open SQL, strConnectionString

Notice that I set CursorLocation property of the recordset to adUseClient (3). We have to do it in order to be able to use some of the properties of this recordset later. I left out the CursorType and LockType parameters, because the defaults adOpenForwardOnly and adLockReadOnly are exactly what we need in this case to get the best performance.

4. Provide Navigation Links

Yes it is a fourth step. I did leave the third step (displaying of the results) for the end because in order for us to display the records, we need to figure some things out. I also think it is better to create and display navigation links on the top of the page before the results.

At this point, we have to figure out couple of things: do we have any results from our search and if so how many pages of results do we have?

Presence of the results is easily determined by checking the EOF property of the recordset:

If RS.EOF Then
    ... ' Clean up
    ... ' Do the no results HTML here
    Response.Write "No Items found."
    ... ' Done
End If

The number of pages we have obviously depends on the number of items we want to display per page.

' Set the page size of the recordset
RS.PageSize = nItemsPerPage
' Get the number of pages
nPageCount = RS.PageCount

Now we need to talk about the current page number. Since we want this page (results.asp) to be able to display any one of the pages of results, we have to have a way to specify which page the user will see right now. We will do it by passing an additional parameter to our results.asp script that we will call "Page". So the link to our page could look like this:


With that said, we can figure out the number of the page that is requested by the user by simply checking Page parameter:

nPage = CLng(Request.QueryString("Page"))

We need to make sure that the number of page requested by user is within the acceptable range and fix it if needed:

If nPage < 1 Or nPage > nPageCount Then
	nPage = 1
End If

Now we can create our navigation links by simply linking to this page with the same value for Keyword and the specific page number. For example:

' First page
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=1"">First Page</A>"
' Previous page:
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=" & nPage - 1 & """>Prev. Page</A>"
' Next page
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=" & nPage + 1 & """>Next Page</A>"
' Last page
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=" & nPageCount & """>Last Page</A>"
' 15th page:
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=15"">15th Page</A>"

3. Display a Page of Resulting Records

All we have left is to display one page of results:

' Position recordset to the correct page
RS.AbsolutePage = nPage

' Display the page of results
Do While Not ( RS.Eof Or RS.AbsolutePage <> nPage )
    Response.Write RS("ItemName") & "<br>"

That is it. Sample that I have included here is a bit more complex, because I've combined both HTML search form and displaying of the results in one ASP page. That is why there is an additional Mode parameter being used in every link. All the code concerning displaying of the results of the search is in the ShowResults() function.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Konstantin Vasserman
Web Developer
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

Suggestionthis post was very helpful Pin
Member 93616556-Dec-12 3:21
memberMember 93616556-Dec-12 3:21 
GeneralObject Required Pin
asprajesh19-Jun-06 3:24
memberasprajesh19-Jun-06 3:24 
QuestionWhat about adp? Pin
I.T. Innovations30-Aug-05 9:33
memberI.T. Innovations30-Aug-05 9:33 
GeneralSearch Form on results page Pin
flotec7-Apr-05 7:20
memberflotec7-Apr-05 7:20 
GeneralShowResults() what can I put Call? Private?... Pin
Member 128233920-Aug-04 6:10
memberMember 128233920-Aug-04 6:10 
GeneralPaging not working properly Pin
Anonymous27-Mar-04 0:19
memberAnonymous27-Mar-04 0:19 
GeneralDynamically displaying data Pin
Anonymous14-Sep-03 16:52
memberAnonymous14-Sep-03 16:52 
GeneralRe: Dynamically displaying data Pin
Anonymous16-Oct-05 21:24
memberAnonymous16-Oct-05 21:24 
GeneralRecordset Paging Pin
Anonymous10-Mar-03 4:01
memberAnonymous10-Mar-03 4:01 
GeneralRe: Recordset Paging Pin
Anonymous27-May-03 7:19
memberAnonymous27-May-03 7:19 
GeneralRe: Recordset Paging Pin
Anonymous4-Aug-03 2:38
memberAnonymous4-Aug-03 2:38 
GeneralRe: Recordset Paging Pin
Anonymous4-Nov-03 9:41
memberAnonymous4-Nov-03 9:41 
GeneralRe: Recordset Paging Pin
Anonymous16-Jun-05 0:42
memberAnonymous16-Jun-05 0:42 
GeneralRe: Recordset Paging Pin
vuinbox2-Mar-05 21:55
membervuinbox2-Mar-05 21:55 
GeneralPaging Newsletter send Pin
fabioweb27-Jan-03 22:30
memberfabioweb27-Jan-03 22:30 
Generalyou rule Pin
Anonymous27-Dec-02 3:52
memberAnonymous27-Dec-02 3:52 
GeneralMultiple keywords Pin
6-Mar-02 12:56
suss6-Mar-02 12:56 
Generaltable size very large Pin
19-Feb-02 20:08
suss19-Feb-02 20:08 
GeneralRe: table size very large Pin
Konstantin Vasserman20-Feb-02 4:33
memberKonstantin Vasserman20-Feb-02 4:33 
GeneralRe: table size very large Pin
14-Apr-02 22:12
suss14-Apr-02 22:12 
GeneralRe: table size very large Pin
25-Apr-02 21:57
suss25-Apr-02 21:57 
GeneralRe: table size very large Pin
26-Jun-02 5:52
suss26-Jun-02 5:52 
GeneralRe: table size very large Pin
Dave McRae9-Jul-03 19:19
memberDave McRae9-Jul-03 19:19 
GeneralRe: table size very large Pin
kamala kannan16-Nov-04 21:14
memberkamala kannan16-Nov-04 21:14 
GeneralRe: table size very large Pin
seyd6-Jul-03 18:47
sussseyd6-Jul-03 18:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.190419.4 | Last Updated 22 Jun 2000
Article Copyright 2000 by Konstantin Vasserman
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid