Classic ASP Paging

[fazilkhader]

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???

<%

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


'Variables
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
Else
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") & ";"
.Open
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
.Open
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)
Else
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"
Else
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
Next

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
Next

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

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



If intPage <= 10 Then
intStart = 1
Else
If (intPage Mod 10) = 0 Then
intStart = intPage - 9
Else
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> "
Else
strRet = strRet & "<a href=""" & strScript
strRet = strRet & "page=" & i
strRet = strRet & """>" & i & "</a>"
If i <> intEnd Then strRet = strRet & " "
End If
Next

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>
</div>
<table>
</tr>

<%Else%>
<!--Records Found-->
<tr>
<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>

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

<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>
</tr>

<%
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>
</tr>
<%
If strRowColor = "#91B998" Then strRowColor = "#90ee90" Else strRowColor = "#91B998"
intRecord = intRecord + 1
objRs.MoveNext
Loop
%>
<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>
</table>
</div>
</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

%>
</table>

</div>

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

 

[Wullie]

You have posted this in the Coldfusion forum. You need forum333

Wullie

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

Domestic Appliance Repairs