Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Paging recordset in ASP

Data Access

Paging recordset in ASP

by  tty0  Posted    (Edited  )
The following code is as well commented as I can, if there are any questions please feel free to email me.

To use this page by copy and paste it needs to be named search.asp and it is called from an html form that passes a value called Query in the querystring. This is searched for in the first field in the database.

*****************************
<%@language=vbscript%>
<%option explicit

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
dim strSQL, objRS, I, records
Dim objConnection
Dim abspage, pagecnt

'open your connection to the database
objConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
objConnection = objConnection & Server.MapPath("\database.mdb")

'store the record number in a variable as you will refer to this
'a few times later on
records = request.querystring("records")

set objRS = server.CreateObject("ADODB.Recordset")
objRS.PageSize = 6 'this is the amount of records you ned on a page
objRS.CacheSize = 300
objRS.CursorLocation = adUseClient

'set the SQL query
strSQL = "SELECT * FROM table WHERE field1='"
strSQL = strSQL & request.querystring("Query") & "' order by field1"

'open the recordset
objRS.Open strSQL, objConnection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
%>

<html>
<head>
<title>Your Search Results</title>
</head>
<body>
<table width="600" border="0" cellspacing="0" cellpadding="0" align="center">
<tr>
<td>
<%
if objRS.EOF and objRS.BOF then
response.write("Your search has returned no results, ")
response.write(" please re search the database using a different criteria.")
else
'***************************************
'start the loop in the recordset here
'***************************************
If Len(request.querystring("records")) = 0 then
objRS.AbsolutePage = 1
Else
If CInt(request.querystring("records")) <= objRS.PageCount then
objRS.AbsolutePage = request.querystring("records")
Else
objRS.AbsolutePage = 1
End if
End if
abspage = objRS.AbsolutePage
pagecnt = objRS.PageCount
'***************************************
'set this up to mirror what you are wanting in the display
'***************************************
%>
<TABLE WIDTH='100%' cellpadding=2 cellspacing=2 border=1>
<%
For I = 1 to objRS.PageSize
If Not objRS.EOF Then
<TR>
'write the table data onto the page with the recordset details.%>
<TD>
<%=objRS("field1")%><BR>
<%=objRS("field2")%><BR>
<%=objRS("field3")%><BR>
<%=objRS("field4")%><BR>
<%=objRS("field5")%><BR>
<%=objRS("field6")%><BR>
</TD>
</TR>
<%
objRS.movenext
end if
Next
'***************************************
'end the loop in the recordset here and close the objects
'***************************************
%>
</table>
<%
end if
objRS.close
set objRS = nothing
%>
</td>
</tr>
<tr>
<td>
<div align="right">
<%
'********************************************************
'put the navigation links on the bottom of the page here
'********************************************************
Response.Write "<div align='right'>" & vbcrlf
Response.Write "<a href='"
Response.Write "search.asp"
Response.Write "?Query=" & request.querystring("Query") & _
"'>First Page</a>"
Response.Write " | "
If abspage = 1 Then
Response.Write "<span style='color:silver;'>Previous Page</span>"
Else
Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME")
Response.Write "?records=" & abspage - 1 & "&ListAll=" & _
request.querystring("ListAll")& "&Town=" & request.querystring("Town") & _
"&County=" & request.querystring("County") & "'>Previous Page</a>"
End If
Response.Write " | "
If abspage < pagecnt Then
Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME")
Response.Write "?Query=" & request.querystring("Query") & _
"&records=" & abspage + 1 & "'>Next Page</a>"
Else
Response.Write "<span style='color:silver;'>Next Page</span>"
End If
Response.Write " | "
Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME")
Response.Write "?Query=" & request.querystring("Query") & _
"&records=" & pagecnt & "'>Last Page</a>"
Response.Write "</div>"
'********************************************************
'end the navigation links on the bottom of the page here
'********************************************************
%>
</div>
</td>
</tr>
</table>
</body>
</HTML>
****************************************
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top