Asp > ADO

Database Paging

Database Paging

' Assumes:You will need to make a connec
' tion to your own database and re-name to
' records.
<%@ Language=VBScript %>
<!--#include File="_fpclass/adovbs.inc"-->
<%
    gend = CStr(Request.QueryString("gender"))
            
Dim iPageSize 'How many records To show
Dim iRecCurrent ' The page we want To show
Dim sSQL 'SQL command To execute
Dim RecSet  'The ADODB recordset object
Dim I 'Standard looping var
Dim iRecEnd ' Last Record
Dim iRecMax ' Max of record Loop
Dim J       ' Loop variabel
Dim iRecNext    ' Var of Next record To start at
Dim iRecPrev    ' Var of Previous record
Dim sGender ' Var For displaying whether Women's or Men's race
Dim iNumPage    ' Number of pages
    
' Get parameters
iPageSize = 20      
' Retrieve page to show or default to 0
If Request.QueryString("page") = "" Then
iRecCurrent = 0 ' First Record
Else
iRecCurrent = CInt(Request.QueryString("page"))
End If
    
' Assign value to race
If gend = "Male" Then
    sGender = "Men's"
Else
    sGender = "Women's"
End If
    
'    SQL statement
sSQL = "SELECT * FROM 5KResults WHERE Gender='"
sSQL = sSQL & gend & "' ORDER BY Time"
    
Set RecSet = Server.CreateObject("ADODB.Recordset") 
    
RecSet.Open sSQL,"DSN=chiledadsn",adOpenForwardOnly,adLockReadOnly
    
' Get the count of the records 
Do While Not RecSet.EOF
    J = J + 1
    RecSet.MoveNext
Loop
iRecEnd = J -1
    
' Get the number of pages
iNumPage = CInt(iRecEnd/iPageSize)
' If the request page falls outside the 
' acceptable range,
' give them the closest match (0 or max)
' 
If iRecCurrent > iRecEnd Then iRecCurrent = iRecEnd 
If iRecCurrent < 0 Then iRecCurrent = 0
If iRecCurrent < iRecEnd Then
iRecNext = iRecCurrent + iPageSize
Else
    iRecNext = iRecEnd
End If
    
If iRecCurrent > 0 Then
    iRecPrev = iRecCurrent - iPageSize
Else
    iRecPrev = 0
End If
    
' Do this so when calling the las page w
' e only loop through 
' the number of records we have if less 
' than the iPageSize
If (iRecNext - iRecEnd ) > 0 Then
    iRecMax = iRecEnd - iRecCurrent
Else
    iRecMax = iPageSize
End If
    
    
' Start at the beginning of the database
' 
RecSet.MoveFirst
'Move to the record we want to start at
RecSet.Move(iRecCurrent)
    
' use this when creating links 
' doesn't matter what this page is named
' 
strScriptName = Request.ServerVariables("SCRIPT_NAME")
    
%>
<%
Sub NavBar()
    Dim iPage
    Dim iVue
    Dim sNumbers
    Dim sPrev
    Dim sNext
    Dim sFirst
    Dim sLast
    Dim sNavBar
    Dim iLastPage
    iLastPage = iRecEnd - iPageSize
            
    For i = 0 To (iNumPage - 1)
        iPage = i * iPageSize
        iVue = i + 1
                
        sNumbers = sNumbers & NavLink(strScriptName,iPage,gend,iVue)
                                    
    Next
                
    If iRecCurrent <> 0 Then
        sFirst = NavLink(strScriptName,0,gend,"First")
        sPrev = NavLink(strScriptName,iRecPrev,gend,"Previous")
    End If
                
    If (iRecCurrent + iRecMax) < iRecEnd Then
        sNext = NavLink(strScriptName,iRecNext,gend,"Next")
        sLast = NavLink(strScriptName,iLastPage,gend,"Last")
    End If
                
    sNavBar = sNumbers & "<BR>" & sFirst & sPrev & sNext & sLast
    Response.Write(sNavBar)
End Sub
            
%>
            
<%
' Creates the link used by the navigatio
' n sub
Function NavLink(scriptName,pageNum,gendr, sWord)
    Dim strLink
            
    strLink = strLink & "<A HREF='"
    strLink = strLink & scriptName
    strLink = strLink & "?page="
    strLink = strLink & pageNum
    strLink = strLink & "&gender="
    strLink = strLink & gendr
    strLink = strLink & "'>"
    strLink = strLink & sWord
    strLink = strLink & "</A> "
                    
    NavLink = strLink
End Function
                        
%>      
<HTML>
<HEAD>
    <TITLE>5K Race Results </TITLE>
    
    <META name="description" content="An example of paging through a database.">
    <META name="keywords" content="Active Server Pages, ASP, database, paging">
    <META http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <BASE target="_top">
    <META name="language" content="en-us">
    <META name="robots" content="INDEX">
    <META name="revisit-after" content="14 days">
    <META http-equiv="pragma" content="no-cache">
</HEAD>
<BODY>
<!-- Database Table -->
    
<H3><% =sGender %> 5K Race</H3>
<P><STRONG>Records</STRONG>: <% =iRecCurrent %> - <% = iRecCurrent + iRecMax %> of <% =iRecEnd %></P>
<P><% NavBar %></P>
<%
' Use these for debugging
'Response.Write ("iRecCurrent: " & iRecC
' urrent & "<BR>")
'Response.Write("iRecEnd: " & iRecEnd & 
' "<BR>")
'Response.Write("iRecMax: " & iRecMax & 
' "<BR>")
'Response.Write("iRecNext: " & iRecNext 
' & "<BR>")
'Response.Write("iRecPrev: " & iRecPrev 
' & "<BR>")
'Response.Write(CInt(iRecEnd/iPageSize) 
' & "<BR>")

%>
<TABLE border="0" cellPadding="1" cellSpacing="0" width="425px">
<TR bgColor="blue">
    <TD style="WIDTH: 130px" width="150" bgcolor="#388C40"><STRONG>Name</STRONG></TD>
    <TD style="WIDTH: 35px" width="35" bgcolor="#388C40"><STRONG>Age</STRONG></TD>
    <TD style="WIDTH: 90px" width="150" bgcolor="#388C40"><STRONG>City</STRONG></TD>
    <TD style="WIDTH: 35px" width="45" bgcolor="#388C40"><STRONG>State</STRONG></TD>
    <TD style="WIDTH: 50px" width="75" bgcolor="#388C40"><STRONG>Time</STRONG></TD>
    <TD style="WIDTH: 50px" width="75" bgcolor="#388C40"><STRONG>Pace</STRONG></TD></TR>
<%
For i = 0 To iRecMax
    If i Mod 2 Then 
    Response.Write ("<TR bgColor=""#008080""><TD>")
    Else
        Response.Write("<TR><TD>")
    End If
    
    Response.Write(RecSet("FirstName") & " ")
    Response.Write(RecSet("LastName")& "</TD>")
    Response.Write("<TD>" & RecSet("age") & "</TD>")
    Response.Write("<TD>" & RecSet("City") & "</TD>")
    Response.Write("<TD>" & RecSet("State") & "</TD>")
    Response.Write("<TD>" & RecSet("Time" )& "</TD>")
    Response.Write("<TD>" & RecSet("Pace") & "</TD>")
    Response.Write("</TR>")
            
        
' Move to the next record
    RecSet.MoveNext
    
Next
' Clean up after yourself
    RecSet.Close
    Set RecSet = Nothing
            
%>
    </TABLE>
<P><% Call NavBar %></P>
        
<!-- End Database Table -->
    </BODY>
</HTML>