'**************************************
    ' Name: Database Paging
    ' Description:I needed to be able to pag
    '     e through a database of about 1000 recor
    '     ds and I was unable to use .AbsolutePage
    '     so I had to try something else.
    ' By: Troy Demet
    '
    ' Assumes:You will need to make a connec
    '     tion to your own database and re-name to
    '     records.
    '
    'This code is copyrighted and has    ' limited warranties.Please see http://w
    '     ww.Planet-Source-Code.com/vb/scripts/Sho
    '     wCode.asp?txtCodeId=6252&lngWId=4    'for details.    '**************************************
    
    <%@ Language=VBScript %>
    <!--#include File="_fpclass/adovbs.inc"-->
    <%
    	gend = CStr(Request.QueryString("gender"))
    			
    ' **************** Added July 5, 2000 **
    '     **********
    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
    	
    ' ****** End Added July 5, 2000 ********
    '     
    	
    '	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
    	
    '****** Added July 5, 2000 *************
    '     ****
    	
    ' 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
    	
    '********End Added July 5, 2000 ********
    '     
    	
    ' 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>