Click here to Skip to main content
Click here to Skip to main content

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
<!-- Download Links -->
  • Download demo project - 15 Kb
  • <!-- Article image -->

    Sample Image - rsPaging.gif

    <!-- Main HTML starts here -->

    Introduction

    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 growth 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 implements 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 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.

    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 ">
    </FORM>
    ...
    

    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 have 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 last 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 determent by checking the EOF property of the recordset:

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

    The number of pages we have is 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 will the user 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:

    http://..../results.asp?Keyword=BlahBlahBlah&Page=3
    

    With that said we can figure out the number of the page that is requested by the user by simple 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>"
        RS.MoveNext
    Loop
    RS.Close
    

    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 is being used in every link. All the code concerning displaying of the results of the search is in the ShowResults() function.

    License

    This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

    A list of licenses authors might use can be found here

    Share

    About the Author

    Konstantin Vasserman
    Web Developer
    United States United States
    No Biography provided

    Comments and Discussions

     
    Suggestionthis post was very helpful PinmemberMember 93616556-Dec-12 3:21 
    GeneralObject Required Pinmemberasprajesh19-Jun-06 3:24 
    QuestionWhat about adp? PinmemberI.T. Innovations30-Aug-05 9:33 
    GeneralSearch Form on results page Pinmemberflotec7-Apr-05 7:20 
    GeneralShowResults() what can I put Call? Private?... Pinmembergilgalbiblewheel20-Aug-04 6:10 
    GeneralPaging not working properly PinsussAnonymous27-Mar-04 0:19 
    I have a problem in paging. The same records(.htms) are displayed in all the pages.Even though there is one record matching the search string,the page numbers(1,2 3.....) are displayed.
     
    <%
    Dim currentPage, rowcount, i, counter, startnum , pgSize,objQuery
    currentPage=trim(request("currentPage"))
    counter=trim(request("counter"))
    srchstr=trim(request("search"))
    strlen=len(srchstr)
    found="false"
    srch = Replace( srchstr, "'", "")
    if currentPage="" or currentPage="0" then
    currentPage=1
    startnum=1
    counter=10
    end if
    set con=server.createObject( "ADODB.CONNECTION" )
    con.open "PROVIDER=msidxs"
    if srchstr <> "" then
    set objQuery=Server.CreateObject("ixsso.Query")
    With objQuery
    .Catalog = "kec" ' Catalog to query
    .Query = srch
    .SortBy = "rank[d]"
    .Columns = "DocTitle, Characterization, vpath, filename, size, write, rank"
    .MaxRecords = 500
    End with

    Set RS1=objQuery.CreateRecordset("nonsequential")
    RS1.PageSize=5

    'SQLstmt = "select Characterization, Directory, HitCount, rank, DocTitle, VPATH From SCOPE() "&_
    ' "Where FREETEXT (Contents, '" &srch& "') > 0 "&_
    ' "Order By Rank DESC "
    'Set RS1 = con.execute (SQLstmt)
    if not RS1.EOF then
    ct=0
    do while not rs1.eof
    ct=ct+1
    rs1.movenext
    loop
    rs1.movefirst
     
    end if
    end if
    Const intRecordsPerPage = 5
    Set conn= server.createobject("ADODB.connection")
    conn.open application("txtDatabase")
     
    if srchstr = "" then
    call norecords()
    else
    call search()
    end if
     
    sub search()
     
    %>
    In search module, i am calling sub Description
    <%
    sub description()
    Set conn= server.createobject("ADODB.connection")
    conn.open application("txtDatabase")
    Set RS=Server.CreateObject("ADODB.Recordset")
    RS.CursorType=adOpenStatic
    RS.PageSize=5
    RS.Open "Select * from tblSearch where txtFamily like '%"&srchstr&"%'or txtPName like '%"&srchstr&"%' or txtType like '%"&srchstr&"%' or memoText like '%"&srchstr&"%' or txtSelection like '%"&srchstr&"%' order by txtPCode ", Conn
    pgSize=RS.PageSize
    RecNum=RS.RecordCount + ct
    response.write RS.RecordCount
    statPg=RecNum/pgSize
    y=fix(statPg)
    r=statPg-y
    if r > 0.0 then
    statPg=y+1
    end if
    if rs.eof and rs1.eof then
    exit sub
    end if
    if not rs.eof then
    RS.AbsolutePage=cINT(currentPage)
    end if
    ' RS1.AbsolutePage=cINT(currentPage)

    while not rs1.eof and rowcount < pgSize

    feadisp="false"
    if not rs.eof then
    if len(chkword(rs("memoText"),srchstr)) then
    feadisp="true"
    end if
    if len(chkword(rs("txtFamily"),srchstr)) then
    feadisp="true"
    end if
    if len(chkword(rs("txtPName"),srchstr)) then
    feadisp="true"
    end if
    if len(chkword(rs("txtSelection"),srchstr)) then
    feadisp="true"
    end if
    end if
    if not RS1.EOF then
    feadisp="true"
    end if
    if not rs.eof then
    select case trim(rs("txtFamily"))
    case "AC Motors"
    path="/products/ACMotors/"
    case "Alternators/ AC Generators"
    path="/products/ACGenerators/"
    case "DC Machines"
    path="/products/DCMachines/"
    case "Transformers"
    path="/products/Transformers/"
    case "Traction"
    path="/products/Traction/"
    case "Switchgear"
    path="/products/Switchgear/"
    case "Electronics"
    path="/products/Electronics/"
    case "DG Sets"
    path="/products/Gensets/"
    end select
    end if


    if feadisp="true" then
    if not rs.eof then
    select case rs("txtSelection")
    case "applications"
    aspnm="applica.asp?Product=" & rs("txtPCode") & "&Prdname=" & rs("txtPName")
    case "features"
    aspnm="feature.asp?Product=" & rs("txtPCode") & "&Prdname=" & rs("txtPName")
    case "description"
    aspnm="descript.asp?Product=" & rs("txtPCode") & "&Prdname=" & rs("txtPName")
    case "specifications"
    aspnm="stdspecs.asp?Product=" & rs("txtPCode") & "&Prdname=" & rs("txtPName")
    end select


    if rs("txtType")<>"" then
    dispstr="" & rs("txtPName") & " - " & rs("txtType") & " - " & rs("txtFamily") & "
    " & rs("memoText") & "

    "
    else
    dispstr="" & rs("txtPName") & " - " & rs("txtFamily") & "
    " & rs("memoText") & "

    "
    end if
    feadisp="false"
    end if
    end if

    if len(dispstr) or ct <> 0 then
    found="true"
    else
    found="false"
    end if

    if (mid(RS1("VPath"),len(RS1("VPath"))-3)) = ".htm" then %>
    <%=RS1("DocTitle")%>

    <% st = 1
    if (InStr(1, RS1("DocTitle"), "About Us",1) > 1) or (InStr(1, RS1("DocTitle"), "Projects",1) > 1) then
    st=Cint(InStr(1, RS1("Characterization"), "Click, to know more about.",1))+27
    end if
    response.write(mid(RS1("Characterization"),st))%>




    <% end if
    response.write dispstr
    rowcount=rowcount+1
    if not rs.eof then
    rs.movenext
    end if
    if not rs1.eof then
    rs1.movenext
    end if
    Wend
    if currentPage <> 1 and currentPage > 9 then
    startnum=mid(currentPage,1,len(currentPage)-1)&"0"
    else
    startnum=1
    end if
     
    counter=startnum+10
     
    srchstr=Server.URLEncode(srchstr)
    response.write("Pages : ")
    if currentPage > 9 then
    response.write("First ")
    response.write(" ")
    response.write("Previous ")
    response.write(" ")
    end if
    if startnum=1 then
    counter=counter-1
    end if
     
    for i=startnum to statPg
    'pg=RS1.Pagecount+RS.Pagecount
    'for i=startnum to RS.Pagecount-1
    if i < cINT(counter) then
    if i = cINT(currentPage) then
    response.write(""&i&" ")
    else
    response.write(""&i&" ")
    response.write(" ")
    end if
    else
    counter=counter+10
    response.write(" ")
    if i <> statPg then
    'if i <> RS.Pagecount-1 then
    response.write(" Next ")
    response.write(" ")
    response.write(" Last ")
    'response.write(" Last ")
    else
    response.write(" Last ")
    'response.write(" Last ")
    end if
    exit for
    end if
     
    next
    end sub
    %>
    GeneralDynamically displaying data PinsussAnonymous14-Sep-03 16:52 
    GeneralRe: Dynamically displaying data PinsussAnonymous16-Oct-05 21:24 
    GeneralRecordset Paging PinsussAnonymous10-Mar-03 4:01 
    GeneralRe: Recordset Paging PinsussAnonymous27-May-03 7:19 

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

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

    | Advertise | Privacy | Mobile
    Web02 | 2.8.140902.1 | Last Updated 22 Jun 2000
    Article Copyright 2000 by Konstantin Vasserman
    Everything else Copyright © CodeProject, 1999-2014
    Terms of Service
    Layout: fixed | fluid