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.


Classic ASP Paging

Classic ASP Paging

I'm trying to display 12 records in each page.
On the First page it shows 12 records
But on the second page it starts again from 11th record.
Could anyone help me to display the 13th record on the second page???


Const MIN_PAGESIZE = 5 'Minimum pagesize
Const MAX_PAGESIZE = 20 'Maximum pagesize
Const DEF_PAGESIZE = 10 'Default pagesize

Dim strSearch
Dim strURL
Dim objCn 'ADO DB connection object
Dim objRs 'ADO DB recordset object
Dim blnWhere 'True/False for have WHERE in sql already
Dim intRecord 'Current record for paging recordset
Dim intPage 'Requested page
Dim intPageSize 'Requested pagesize
Dim sql 'Dynamic sql query string

'Create objects
Set objCn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")

'Set/initialize variables
intRecord = 1
blnWhere = False

'-Get/set requested page
intPage = MakeLong(Request("page"))
If intPage < 1 Then intPage = 1

'-Get/set requested pagesize
If IsEmpty(Request("pagesize")) Then 'Set to default
intPageSize = DEF_PAGESIZE
intPageSize = MakeLong(Request("pagesize"))
'Make sure it fits our min/max requirements
If intPageSize < MIN_PAGESIZE Then
intPageSize = MIN_PAGESIZE
ElseIf intPageSize > MAX_PAGESIZE Then
intPageSize = MAX_PAGESIZE
End If
End If

'-Build dynamic sql
'sql = "SELECT * FROM tbl_Guestbook "

sql = "SELECT * FROM tblPost_Job " _
& "ORDER BY datereleased DESC;"

'Create and open connection object
With objCn
.CursorLocation = adUseClient
.ConnectionTimeout = 15
.CommandTimeout = 30
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("database/database.mdb") & ";"
End With

'Create and open recordset object
With objRs
.ActiveConnection = objCn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Source = sql
.PageSize = 12 'Displaying 12 records on first page
Set .ActiveConnection = Nothing 'Disconnect the recordset
End With

'Creates a long value from a variant, invalid always set to zero
Function MakeLong(ByVal varValue)
If IsNumeric(varValue) Then
MakeLong = CLng(varValue)
MakeLong = 0
End If
End Function

'Returns a neatly made paging string, automatically configuring for request
'variables, regardless of in querystring or from form, adjust output to your needs.
Function Paging(ByVal intPage, ByVal intPageCount, ByVal intRecordCount)
Dim strQueryString
Dim strScript
Dim intStart
Dim intEnd
Dim strRet
Dim i

If intPage > intPageCount Then
intPage = intPageCount
ElseIf intPage < 1 Then
intPage = 1
End If

If intRecordCount = 0 Then
strRet = "No Records Found"
'ElseIf intPageCount = 1 Then
' strRet = "End Of Hits"
For i = 1 To Request.QueryString.Count
If LCase(Request.QueryString.Key(i)) <> "page" Then
strQueryString = strQueryString & "&"
strQueryString = strQueryString & Server.URLEncode(Request.QueryString.Key(i)) & "="
strQueryString = strQueryString & Server.URLEncode(Request.QueryString.Item(i))
End If

For i = 1 To Request.Form.Count
If LCase(Request.Form.Key(i)) <> "page" Then
strQueryString = strQueryString & "&"
strQueryString = strQueryString & Server.URLEncode(Request.Form.Key(i)) & "="
strQueryString = strQueryString & Server.URLEncode(Request.Form.Item(i))
End If

If Len(strQueryString) <> 0 Then
strQueryString = "?" & Mid(strQueryString, 2) & "&"
strQueryString = "?"
End If

strScript = Request.ServerVariables("SCRIPT_NAME") & strQueryString

If intPage <= 10 Then
intStart = 1
If (intPage Mod 10) = 0 Then
intStart = intPage - 9
intStart = intPage - (intPage Mod 10) + 1
End If
End If

intEnd = intStart + 9
If intEnd > intPageCount Then intEnd = intPageCount

strRet = "Page " & intPage & " of " & intPageCount & ": "

If intPage <> 1 Then
strRet = strRet & "<a href=""" & strScript
strRet = strRet & "page=" & intPage - 1
strRet = strRet & """>&lt;&lt;Prev</a> "
End If

For i = intStart To intEnd
If i = intPage Then
strRet = strRet & "<b>" & i & "</b> "
strRet = strRet & "<a href=""" & strScript
strRet = strRet & "page=" & i
strRet = strRet & """>" & i & "</a>"
If i <> intEnd Then strRet = strRet & " "
End If

If intPage <> intPageCount Then
strRet = strRet & " <a href=""" & strScript
strRet = strRet & "page=" & intPage + 1
strRet = strRet & """>Next&gt;&gt;</a> "
End If
End If

Paging = strRet
End Function

<div align="left" style="width: 599; height: 24">

<!--Search Form-->
<form name="frmSearch" action="<%= Request.ServerVariables("SCRIPT_NAME") %>" method="Post">

<p align="center">

<%If objRs.EOF Then%>
<!--No Records Found-->

<b><font face="verdana" size="2">No Records found!</font></b>

<!--Records Found-->
<td width="885" height="97">
<div align="left">
<table border="0" width="595" cellpadding="0" cellspacing="1" height="206">
<tr><td colspan=7 width="482" height="52" align="right" </b>
<p align="right"><span style="position: absolute; left: 400; top: 114"><img border="0" src="../listofvacant.jpg" width="360" height="58"></span></td></tr>
<tr><td colspan=7 width="482" height="13"><b><font face="Verdana" size="2">Job(s)&nbsp; Found:
<%=objRs.RecordCount%></font> </b></td></tr>
<tr><td align="center" colspan="7" width="482" bgcolor="#FFFFFF" height="21"><font face="Verdana" size="1">
<p align="left"><%=Paging(intPage, objRs.PageCount, objRs.RecordCount)%></font></td></tr>

<td align="center" colspan="7" width="482" height="18"></td>

<tr bgcolor="#efefef">
<td width="85" align="center" height="25"><font face="Verdana" size="1">Type</font></td>
<td width="78" align="center" height="25"><font face="Verdana" size="1">Position</font></td>
<td width="73" align="center" height="25"><font face="Verdana" size="1">Agency</font></td>
<td width="157" align="center" height="25"><font face="Verdana" size="1">Item No</font></td>
<td width="187" align="center" height="25"><font face="Verdana" size="1">Salary Grade</font></td>
<td align="center" width="105" height="25"><font face="Verdana" size="1">Requirements</font></td>
<td align="center" width="111" height="25"><font face="Verdana" size="1">Date&nbsp; Released</font></td>

If objRs.PageCount < intPage Then intPage = objRs.PageCount
objRs.AbsolutePage = intPage

Dim strRowColor
strRowColor = "#ffffff"

Do While Not objRs.EOF And intRecord <= intPageSize

<tr bgcolor="<%=strRowColor%>">
<td nowrap width="85" align="center" bgcolor="#D3E6CC" height="21"><font face="Verdana" size="1"><%=objRs("jobtype").Value%></font></td>
<td nowrap width="78" align="center" bgcolor="#D3E6CC" height="25"><font face="Verdana" size="1"><%=objRs("jobposition").Value%></font></td>
<td width="73" align="center" bgcolor="#D3E6CC" height="21"><font face="Verdana" size="1"><%=objRs("agency").Value%></font></td>
<td width="157" align="center" bgcolor="#D3E6CC" height="21"><font face="Verdana" size="1"><%=objRs("itemno").Value%></font></td>
<td width="187" align="center" bgcolor="#D3E6CC" height="21"><font face="Verdana" size="1"><%=objRs("salarygrade").Value%></font></td>
<td nowrap align="center" width="105" bgcolor="#D3E6CC" height="21">
<p align="center"><font face="Verdana" size="1"><%=objRs("requirements").Value%></font></td>
<td nowrap align="center" width="111" bgcolor="#D3E6CC" height="21"><font face="Verdana" size="1"><%=objRs("datereleased").Value%></font></td>
If strRowColor = "#91B998" Then strRowColor = "#90ee90" Else strRowColor = "#91B998"
intRecord = intRecord + 1
<tr><td colspan="7" width="482" height="21"></td></tr>
<tr><td align="center" colspan="7" width="482" bgcolor="#FFFFFF" height="21"><font face="Verdana" size="1">
<p align="left"><%=Paging(intPage, objRs.PageCount, objRs.RecordCount)%></font></td></tr>

<%End If%>

'Object cleanup
If IsObject(objRs) Then
If Not objRs Is Nothing Then
If objRs.State = adStateOpen Then objRs.Close
Set objRs = Nothing
End If
End If

If IsObject(objCn) Then
If Not objCn Is Nothing Then
If objCn.State = adStateOpen Then objCn.Close
Set objCn = Nothing
End If
End If



<td width="1%" height="21" valign="top" align="left"></td>
<td width="102%" height="21"></td>
<td width="100%" height="191" colspan="2"></td>

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


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