Database Paging

Database Paging

--------------------------------------------------------------------------------

Description : I needed to be able to page through a database of about 1000 records and I was unable to use .AbsolutePage so I had to try something else.

' Assumes:You will need to make a connec
' tion to your own database and re-name to
' records.
<%@ Language=VBScript %>

<%
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 & " " & 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>


<H3><% =sGender %> 5K Race</H3>
Records: <% =iRecCurrent %> - <% = iRecCurrent + iRecMax %> of <% =iRecEnd %>
<% NavBar %>
<%
' Use these for debugging
'Response.Write ("iRecCurrent: " & iRecC
' urrent & " ")
'Response.Write("iRecEnd: " & iRecEnd &
' " ")
'Response.Write("iRecMax: " & iRecMax &
' " ")
'Response.Write("iRecNext: " & iRecNext
' & " ")
'Response.Write("iRecPrev: " & iRecPrev
' & " ")
'Response.Write(CInt(iRecEnd/iPageSize)
' & " ")

%>
<TABLE border="0" cellPadding="1" cellSpacing="0" width="425px">
<TR bgColor="blue">
<TD style="WIDTH: 130px" width="150" bgcolor="#388C40">Name</TD>
<TD style="WIDTH: 35px" width="35" bgcolor="#388C40">Age</TD>
<TD style="WIDTH: 90px" width="150" bgcolor="#388C40">City</TD>
<TD style="WIDTH: 35px" width="45" bgcolor="#388C40">State</TD>
<TD style="WIDTH: 50px" width="75" bgcolor="#388C40">Time</TD>
<TD style="WIDTH: 50px" width="75" bgcolor="#388C40">Pace</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>
<% Call NavBar %>


</BODY>
</HTML>