INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- Turn Off Ad Banners
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...I have tons of books, have book marked tons of tutorials, which have helped, but this forum has answered those "impossible to find" solutions. I am thrilled with this site..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft: Active Server Pages (ASP) FAQ
|
ASP 101
|
How do I page through a Recordset?
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: Active Server Pages (ASP) FAQ Index
Back to Microsoft: Active Server Pages (ASP) Forum
My FAQ Archive
 Email This FAQ To A Friend |
|
 |
|
