There are times when I find it very convenient to just create functions as stubs and just call the functions within my asp code.
What you are attempting to do fits perfectly into this requirement.
So I will give you two functions that have been tested and work well.
You can then call these functions to perform the paging tasks for you.
'Creates a long value from a variant, we will set invalids always to zero
Function MakeLong(ByVal varValue)
If IsNumeric(varValue) Then
MakeLong = CLng(varValue)
Else
MakeLong = 0
End If
End Function
'Now function to actually creates the page sizes
'Returns a neatly made paging string, automatically configuring for request
'variables, regardless of whether you are using a querystring or from form, adjust output to your needs.
'You can also based on your need, according to your post, turn this into dropdown list box.
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 & """><<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>></a> "
End If
End If
Paging = strRet
End Function
'Now, declare some constants and set your pagesize rules
Const MIN_PAGESIZE = 5 'Minimum pagesize
Const MAX_PAGESIZE = 20 'Maximum pagesize
Const DEF_PAGESIZE = 10 'Default pagesize
'Variables to hold your connection objects
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
'Now build your query
sql=select * from your table"
'Create and open connection object
With objCn
.CursorLocation = adUseClient
.ConnectionTimeout = 15
.CommandTimeout = 30
.ConnectionString = "dsn=yourDSN"
.Open
End With
'Create and open recordset object
With objRs
.ActiveConnection = objCn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Source = sql
.PageSize = intPageSize
.Open
Set .ActiveConnection = Nothing 'Disconnect the recordset
End With
'Create form objects
<form name="frmSearch" method="post" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
<input type="hidden" name="page" value="1"> --passing in page size as a hidden field
'From here, you can either use dropdown or input textboxes.
This is not completed but most of the paging work is done for you.
I will let you handle the form objects