INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ASP 101

How do I page through a Recordset? by jfriestman
Posted: 17 Aug 00

Wonder how to page through a large Recordset only showing X number of records at a time?  So do a lot of folks, this question gets asked so many times here, I decided to put together a VBScript Class that will allow people to page a recordset.  Here's the code and a page that will do it, just copy paste it into your favorite editor and start experimenting.  You can definitely make the table prettier and should probably add some error handling... anyway, happy coding!

<%@ Language=VBScript %>
<% Option Explicit %>

<%
'include a reference to the ADO type library
'so we can use the ADO constants
%>
<!-- METADATA
        TYPE="typelib"
        FILE="C:\Program Files\Common Files\System\ADO\msado20.tlb"
-->

<%
Response.Buffer = True
Dim iCurrentPage
iCurrentPage = Request.QueryString ("pg")
If iCurrentPage = "" Then iCurrentPage = 1

Dim oPg
Set oPg = New DbPager

'Set public properties of the Pager Class

With oPg
        .DataSource = "Provider=SQLOLEDB; Data_Source=(local); Initial Catalog=Northwind; User Id=sa; Password=;"
        .RecsPerPage = 10
        .SQL = "SELECT * FROM Customers ORDER BY CompanyName"
        .ShowPageNumbers = True
        .ShowPage (iCurrentPage)
End With


%>


<%
Class DbPager

        'Set a few module level variables
        Private m_sDSN
        Private m_sSQL
        Private m_iRecsPerPage
        Private m_bShowPageNumbers

        'datasource for the Data Page
        Public Property Let DataSource(NewValue)    'As String
            m_sDSN = NewValue
        End Property

        'sql statement to generate the page
        Public Property Let SQL(NewValue)    'As String
            m_sSQL = NewValue
        End Property

        'number of records you want per page
        Public Property Let RecsPerPage (NewValue)    'As Integer
            m_iRecsPerPage = NewValue
        End Property

        'Boolean to indicate whether or not to show all the page numbers
        Public Property Let ShowPageNumbers(NewValue)    'As Boolean
            m_bShowPageNumbers = CBool(NewValue)
        End Property


        'set default values
        Private Sub Class_Initialize()
            ShowPageNumbers = True
            m_iRecsPerPage = 10
        End Sub


        'INPUT: Page you want to show
        'OUTPUT: Html
        Public Sub ShowPage (iCurrentPage)


            Dim Rs
            Set Rs = Server.CreateObject ("ADODB.Recordset")

            With Rs
                .CacheSize = m_iRecsPerPage
                .PageSize = m_iRecsPerPage    '# of records / page
                .CursorLocation = adUseClient
                'Absolute Page requires Keyset or Static cursor
                .CursorType = adOpenKeyset
                .Open m_sSQL, m_sDsn
                .AbsolutePage = iCurrentPage    'Set the page
            End With

            'Number of fields, number of pages
            Dim iNumFields, iNumPages
            iNumFields = Rs.Fields.Count
            iNumPages = Rs.PageCount

            Dim i, iFld, iRec

            Response.Write "<TABLE Border=1 Width='100%'>"

            'a routine to write the page navigation
            WriteNavigation iCurrentPage, iNumPages, iNumFields


            'Write all the field names
            'as column headings
            Response.Write "<TR>"
            For iFld = 0 To iNumFields - 1
                Response.Write "<TD>" & Rs.Fields(iFld).Name & "</TD>"
            Next
            Response.Write "<TR>"


            'Write the cell values
            For i = 1 To m_iRecsPerPage
                If Rs.EOF Then Exit For
                Response.Write "<TR>"
                For iFld = 0 To iNumFields - 1
                    Response.Write "<TD>" & Rs.Fields(iFld).Value & "</TD>"
                Next
                Response.Write "</TR>"
                Rs.MoveNext
            Next

            Response.Write "</TABLE>"
            Rs.Close
            Set Rs = Nothing

        End Sub



        'INPUT: Current Page, Number of total pages, number of fields
        'OUTPUT: Html for navigating the recordset
        Private Sub WriteNavigation(iCurrentPage, iNumPages, iNumFields)

            Dim i, sScriptName

            sScriptName = Request.ServerVariables ("SCRIPT_NAME")

            iCurrentPage = CInt(iCurrentPage)

            Dim iPreviousPage, iNextPage
            'at the first page so set the previous page to the last page
            If iCurrentPage = 1 Then
                iPreviousPage = iNumPages
            Else
                iPreviousPage = iCurrentPage - 1
            End If

            'at the last page so set the next page to the first page
            If iCurrentPage = iNumPages Then
                iNextPage = 1
            Else
                iNextPage = iCurrentPage + 1
            End If

            Response.Write "<TR>"
            Response.Write "<TD Colspan='" & iNumFields & "' Align=Center>"

            'previous page
            Response.Write "<A HREF='" & sScriptName & "?pg=" & iPreviousPage & "'>Prev</A> "

            'if show all page numbers (clickable)
            'this can get out of hand with big recordsets
            If m_bShowPageNumbers Then
                For i = 1 To iNumPages
                    If i = iCurrentPage Then
                        Response.Write "<B>" & i & "</B>"
                    Else
                        Response.Write "<A HREF='" & Request.Servervariables("SCRIPT_NAME") & "?pg=" & i & "'>"
                        Response.Write i & "</A>"
                    End If
                    Response.Write " "
                Next
            End If

            'next page navigation
            Response.Write " <A HREF='" & sScriptName & "?pg=" & iNextPage & "'>Next</A>"
            Response.Write "</TD>"
            Response.Write "</TR>"
        End Sub


End Class
%>
  

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close