|
lovduv -> RE: Paging and creating page size...yeesh! (3/5/2007 23:26:08)
|
Ok everything is working beautifully now....Thank yoooouuu sooo much for all your help guys!
Here is the script, I wrote it up so that any Outfront user would be able to easily add it to an existing or new site, all you need is a DB! If this would be better as a new topic or moved....Feel Free! I also tried to add or keep the appropriate comments if you see something that should be added, again feel free.
Script for a search page with page size, and Abc paging.
The paging in this script is based on the script found here, http://www.asp101.com/articles/recordsetpaging/index.asp
the search, and ABC paging were added by me, with help from BetheBall and Spooky from Outfront.
Enjoy!
Copy and paste the entire script below into a new page save the page as alphawsearch.asp:
<%@ Language=VBScript %>
<html>
<head>
<script type="text/javascript">
<!-- Prevents a user from entering a blank search
function validateForm(form){
if (form.searchRS.value=="") {
alert ("Sorry, you must enter a search term.")
return false
}
return true
}
//-->
</script>
</head>
<!--You can put this in an include, This starts the include-->
<%
' Script for a search page with page size, and Abc paging.
' The paging in this script is based on the script found here: http://www.asp101.com/articles/recordsetpaging/index.asp
' the search, and ABC paging were added by me with help from BetheBall and Spooky from Outfront.
' Things to change (I use find and replace):
' Connection string (/afolder/adatabase)
' DBfield = the field in your database you want to search
' YourTable = the table in your database that holds your database field
' The following is optional:
' details.asp?DBfield
' If you have a page that gives a detailed view of a selected record change details.asp to that page and
' Record= to your querystring.
' If you do not have a details page delete this entire line of code:
' <td style="background-color: =bgcolor ;"><a href="details.asp?Record= Response.Write objRst("DBfield") ">View Record Details</a> </td>
'Declare variables
Dim objConn
Dim objRst
Dim intPageCount
Dim intRecordCount
Dim intPage
Dim intRecord
Dim intStart
Dim intFinish
Dim strSearch
' Check to see if there is value in the NAV querystring. If there
' is, we know that the client is using the Next and/or Prev hyperlinks
' to navigate the recordset.
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
'Create Connection
Set objConn = Server.CreateObject("ADODB.Connection")
' Set the connection string. You will, need to change /afolder/adatabase
' to the appropiate path for your computer.
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
objConn.open server.mappath("/afolder/adatabase.mdb")
'Create the Record Set
Set objRst = Server.CreateObject("ADODB.Recordset")
' The CursorLocation and the CursorType must be set as they are here
' in order for Recordset Paging to work properly.
objRst.CursorLocation = 3 'adUseClient
objRst.CursorType = 3 'adOpenStatic
objRst.ActiveConnection = objConn
'Declare Search Variables
If Request.Form("searchRS") <> "" Then
strSearch = Request.Form("searchRS")
Else
strSearch = Request.QueryString("Search")
End If
strSearch=Replace(strSearch,"'","''")
'If statements that build the Record Set
if request("searchRS")<>"" Then
objRst.Open "SELECT DBfield FROM YourTable WHERE DBfield LIKE '%" & strSearch & "%' ORDER BY DBfield", objConn
elseif request.querystring("Search")<>"" Then
objRst.Open "SELECT DBfield FROM YourTable WHERE DBfield LIKE '%" & strSearch & "%' ORDER BY DBfield", objConn
elseif request.Querystring("Abc") <>"" Then
objRst.open "Select DBfield FROM YourTable WHERE DBfield LIKE '"& Left(Request.QueryString("Abc"),1) &"%' ORDER BY DBfield", objConn
else
objRst.Open "SELECT DBfield FROM YourTable ORDER BY DBfield ", objConn
end if
'Set the number of reords returned
objRst.PageSize = 5
' The cachesize property sets the number of records that will be cached
' locally in memory.
objRst.CacheSize = objRst.PageSize
intPageCount = objRst.PageCount
intRecordCount = objRst.RecordCount
' Now you must double check to make sure that you are not before the start
' or beyond end of the recordset. If you are beyond the end, set
' the current page equal to the last page of the recordset. If you are
' before the start, set the current page equal to the start of the recordset.
If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1
' Make sure that the recordset is not empty. If it is not, then set the
' AbsolutePage property and populate the intStart and the intFinish variables.
If intRecordCount > 0 Then
objRst.AbsolutePage = intPage
intStart = objRst.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (objRst.PageSize - 1)
End if
End If
'Builds query for paging by letter
Abc = UCase(Request.QueryString("Abc"))
if (Abc < "A") or (Abc > "Z") then Abc = "A"
for i = 65 to 90
if i > 65 then response.write " | "
if Asc(Abc) <> i then
response.write "<a href='alphawsearch.asp?Abc=" & _
Chr(i) & "'>" & Chr(i) & "</a>"
else
response.write "<b>" & Chr(i) & "</b>"
end if
next
%>
<!--This ends the include-->
<body>
<br>
<a href="alphawsearch.asp">ALL</a>
<br>
<br>
<!--Search Form with Javascript validation to prevent blank searches-->
<form method="post" action="alphawsearch.asp" onsubmit="validateForm (this)">
Search for a record using the box below.<br >
<br>
<input name="searchRS" type="text" >
<input name="submitSearch" type="submit" value="GO">
</form>
<br>
<%If intRecordCount > 0 Then
' Display the record that you are starting on and the record
' that you are finishing on for this page by writing out the
' values in the intStart and the intFinish variables.
%>
<table border="1" style="width: 95%" cellspacing="1" cellpadding="2">
<tr>
<td style="height: 43px" >Record</td>
<td style="height: 43px">Record Details</td>
</tr>
<tr>
<%
' Iterate through the recordset until we reach the end of the page
' or the last record in the recordset.
For intRecord = 1 to objRst.PageSize
'Alternates Row Colors change bgcolor to the color you want *Code snippet from http://www.frontpagehowto.com/altcolor.htm?*
Dim x, bgcolor
if x = 1 then
bgcolor="#E1FAB8"
x=2
Else
bgcolor="#AED174"
x=1
End if
%>
<!--Displays the records in a table and sets the color of each row-->
<td style="background-color:<%=bgcolor%>; width: 349px; font-family: Verdana; color: #003300; font-weight: 300; text-transform: capitalize; font-size: 1em;" class="style1">
<%Response.Write objRst("DBfield")%></td>
<td style="background-color:<%=bgcolor%>;"><a href="details.asp?Record=<%Response.Write objRst("DBfield")%>">
View Record Details</a> </td>
</tr>
<%
objRst.MoveNext
If objRst.EOF Then Exit for
Next
If Request.Form("searchRS") <> "" OR Request.Querystring("Search") <>"" Then
Response.Write "<tr><td align='left'><h4>Your search for "%><span style="text-transform: capitalize;"><%Response.Write"" & strSearch & ""%></span><% Response.Write" returned " & intRecordCount & " Records.</h4></td>"
ElseIf request.Querystring("Abc") <>"" Then
Response.Write "<tr><td align='left'><h4>There are " & intRecordCount & " Records begining with the letter " & Abc & " in the Database.</h4></td>"
Else
Response.Write "<tr><td align='left'><h4>There are " & intRecordCount & " Records in the Database.</h4></td>"
End If
Response.Write "<td><h4>You are now viewing records " & intStart & " through " & intFinish & ".</h4></td></tr>"
Response.Write "<tr><td align='right'>"
If cInt(intPage) > 1 Then
%>
<tr><td><a href="alphawsearch.asp?NAV=<%=intPage - 1%>&Abc=<%=Request.QueryString("Abc")%>&Search=<%=strSearch%>">Prev</a></td></tr>
<%End If%>
<% ' Check to see if the current page is less than the last page
' in the recordset. If it is, then add a "Next" link.
If cInt(intPage) < cInt(intPageCount) Then
%>
<tr><td><a href="alphawsearch.asp?NAV=<%=intPage + 1%>&Abc=<%=Request.QueryString("Abc")%>&Search=<%=strSearch%>">Next</a></td></tr>
<%End If%>
</table>
<%
'Clean up and close the connection
End If
objRst.Close
Set objRst = Nothing
objConn.Close
Set objConn= Nothing
%>
</body>
</html>
|
|
|
|
