CodeAve.com/ASP - Paged Record Output from User Search Input

CodeAve.com - ASP - Database Table Display

ASP
·Read/Write
·Script Writers
·Database Display
·Response Objects
·Server Variables
·Random Events
·Articles
·Miscellaneous
·Contents
·What's New?
JavaScript
CSS
HTML
Links
Mail List
Search
Sitemap

Paged Record Output from User Search Input

When you begin dealing with larger databases and offer search capabilities it becomes essential to offer the ability to view a few of the database observations at a time rather than dumping the whole db out to the browser. The following script will display the 50 United States based on user search requirements, but limit each page to display no more than four states at a time. The example db is rather small, so searching by letter rather than words is best for a fuller demonstration.

•Text View

•Print View

•Mail this Link

• View the Data

<%
u_search=request.form("u_search")
if u_search <> "" then
' Grabs the contstant for cursorlocation %>

<%
p=request.form("p")
' Check the value of p (page) and make sure it is not null
' If a null value is found then 1 is put in its place
if p = "" then
p=1
end if

accessdb="state_info"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
set rs = server.createobject("ADODB.Recordset")
sql = "select statename from states where statename like '%%"& u_search &"%%'"

' ***********************************
' Implements the value from adovbs.inc
rs.cursorlocation=aduseclient
' *************************************
rs.cachesize=5
rs.open sql,cn
if not rs.eof then
g_search="obs found"
rs.movefirst

' Sets the number of observations per page to a max of four
rs.pagesize=4
maxcount=cint(rs.pagecount)
rs.absolutepage=p
%>

<table border="0">
<tr><td valign=top><b>State</b></td></tr>
<%do while not rs.eof and numofobs<rs.pagesize%>
<tr><td valign=top><%=rs("statename")%></td></tr>
<%
rs.movenext
' Counts the number of observations in the current page
numofobs=numofobs+1
loop
%>
</table>

<form method='post' action='<%= request.servervariables("script_name")%>'>
<input type = "hidden" name="u_search" value="<%= u_search %>">
<select name='p' size='1'>
<%
' Starts loop from one to the maximum number of pages
for counter=1 to cint(rs.pagecount)
%>
<option<%
' This will select the current page in the dropdown menu
if cint(p) = cint(counter) then
response.write " selected "
end if
%> value="<%= counter %>">Page <%= counter %> of <%= cint(rs.pagecount) %></option>
<% next %>
</select>
 
<input type="submit" value="Go"</p>
</form>
<br>
<%
if p <> 1 then
%>
<form method='post' action='<%= request.servervariables("script_name")%>'>
<input type = "hidden" name="p" value="<%= p-1 %>">
<input type = "hidden" name="u_search" value="<%= u_search %>">
<input type = "submit" value="<"> Previous Page
</form>
<% end if %>
<%
if cint(p) < cint(rs.pagecount) then
%>
<form method='post' action='<%= request.servervariables("script_name")%>'>
<input type = "hidden" name="u_search" value="<%= u_search %>">
<input type = "hidden" name="p" value="<%= p+1 %>">
Next Page <input type = "submit" value=">">
</form>
<% end if %>
<p>
<%= numofobs &" states on displayed this page<br>" %>
</p>
<%
end if
end if
%>
<%
'Check to see if the search resulted in observations found
'if not then display a message that states no results found
if g_search <> "obs found" then
response.write "Nothing reults found for your search on <i>"& u_search &"</i><br>"
end if %>
<br><br>
<form action="<%= request.servervariables("script_name") %>" method="post">
<input type="text" name="u_search" value="<%= u_search %>">
<input type="submit" value="Search">
</form>