Lab 9 Solution - search.asp
<%@ language=VBScript %>
<% option explicit %>
<!-- #include file = "includes/shop.asp" -->
<%
const cRECMAX = 5
const cSQL = "Select ProductID, ProductName, ProductDescription, InStock, Price From Product"
dim i 'as integer
dim j 'as integer
dim s 'as string
dim t 'as string
dim BCOLOR 'as string
dim sSQL
dim sTitle
dim RS 'as recordset
dim sView
dim iPage
dim sSEARCH
dim myRETURN 'as string
sView=cInt("0" & Trim(request("VW")))
iPage=cInt("0" & Trim(request("PG")))
sSEARCH=Trim(request("SEARCH"))
myRETURN = MakeReturn(sSEARCH)
Function DoNav(myReturn,myPage,MaxPage)
dim sret
sRet = ""
sRet = sRet & "<table border='0' cellpadding='3' cellspacing='0' width='100%'>"
sRet = sRet & "<tr>"
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='left'><a href='Search.asp?VW=0'><font color='#000000'>New Search</font></a></td>"
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='left'><a href='#TOP'><font color='#000000'>Top of Page</font></a></td>"
if myPage > 1 then
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='left'><a href='Search.asp?VW=1&PG=1" & myReturn & "'><font color='#000000'>First Page</font></a></td>"
else
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='right'> </td>"
end if
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='left'>Page " & iPage & " of <a href='Search.asp?VW=1&PG=" & cStr(MaxPage) & myReturn & "'><font color='#000000'>" & MaxPage & "</font></td>"
if myPage > 1 then
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='right'><a href='Search.Asp?VW=1&PG=" & cStr(myPage-1) & myReturn & "'><font color='#000000'>Previous " & cRECMAX & "</font></a></td>"
else
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='right'> </td>"
end if
if myPage < MaxPage Then
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='right'><a href='Search.Asp?VW=1&PG=" & cStr(myPage+1) & myReturn & "'><font color='#000000'>Next " & cRECMAX & "</font></a></td>"
else
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='right'> </td>"
end if
sRet = sRet & "<td bgcolor=" & cMdBlue & " align='right'><a href='Cart.Asp?VW=0&PG=" & iPage & "&sSEARCH=" & sSEARCH & "'><font color='#000000'>View Cart</font></a></td>"
sRet = sRet & "</tr>"
sRet = sRet & "</table>" & vbCrLf
DoNav = sRet
exit function
End Function
%>
<html>
<head>
<title>Shop Product Search Page</title>
<meta name="description" content="Search page for Shop demonstration web site.">
<link rel="stylesheet" type="text/css" href="../csc123.css">
</head>
<body bgcolor="#FFFFFF" text="#000000" topmargin="0">
<!-- #INCLUDE FILE="includes/Header.asp" -->
<%
Select Case sView
Case 1:
%>
<%
response.write "<h3>Search Results for '<em>" & sSEARCH & "</em>'"
if len(sSEARCH) > 0 then
sSQL = ""
sTitle = ""
sSQL = sSQL & " (ProductName Like '%" & sSEARCH & "%') or "
sTitle = sTitle & "Product Name or"
sSQL = sSQL & " (ProductDescription Like '%" & sSEARCH & "%') or "
sTitle = sTitle & "Product Description or"
sSQL = Trim(sSQL)
sSQL = Left(sSQL,Len(sSQL)-3)
sSQL = "(" & sSQL & ") and (CatalogID = 1)"
sTitle = Trim(sTitle)
sTitle = Left(sTitle,Len(sTitle)-3)
sSQL = cSQL & " Where " & sSQL & " Order By ProductName "
set rs = server.createobject("ADODB.Recordset")
rs.ActiveConnection = cDSN
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.cursorLocation = adUseServer
rs.PageSize = cRECMAX
rs.Source = sSQL
rs.Open
if iPage < 1 then iPage = 1
if iPage > rs.PageCount Then iPage = rs.PageCount
If rs.RecordCount <> 0 Then rs.AbsolutePage = iPage
If rs.RecordCount = 0 Then
response.write "<p>No matching records, <a href='Search.Asp'>Search Again</a><p>"
Else 'recordcount
Response.Write DoNav(myRETURN, iPage, rs.PageCount)
Response.Write "<table width='100%' Border='0' cellspacing='0' cellpadding='4'>" & vbCrLf
'Write out Column Names
Response.Write "<tr>" & vbCrLf
Response.Write "<td bgcolor=" & cDkBLUE & " align='left' valign='top'><b><font color=" & cWhite & ">Product Name</font></b></td>" & vbCrLf
Response.Write "<td bgcolor=" & cDkBLUE & " align='left' valign='top'><b><font color=" & cWhite & ">Description</font></b></td>" & vbCrLf
Response.Write "<td bgcolor=" & cDkBLUE & " align='left' valign='top'><b><font color=" & cWhite & ">Stock</font></b></td>" & vbCrLf
Response.Write "<td bgcolor=" & cDkBLUE & " align='right' valign='top'><b><font color=" & cWhite & ">Price</font></b></td>" & vbCrLf
Response.Write "</tr>" & vbCrLf
J=0
'Write out all rows values
For J = 1 To cRECMAX
bCOLOR=""
if J mod 2 = 0 then bColor = " bgcolor=" & cLtBlue & " "
if rs.Eof Then
exit for
end if
Response.Write "<tr>" & vbCrLf
T = "<td align='left' valign='top' " & bCOLOR & ">"
S = Trim(rs("ProductName") & "")
If len(S) = 0 Then S = "Product #" & rs("ProductID")
'Note sView, ID, iPage and sSEARCH passed to next page
response.write T & "<a href='Product.Asp?VW=1&ProductID=" & rs("ProductID") & "&PG=" & iPage & "&SEARCH=" & sSEARCH & "'>" & s & "</a></td>" & vbCrLf
T = "<td align='left' valign='top' " & bCOLOR & ">"
S = Trim(rs("ProductDescription") & "")
If len(S) = 0 Then S = " "
response.write T & S & "</td>" & vbCrLf
T = "<td align='left' valign='top' " & bCOLOR & ">"
S = Trim(rs("InStock") & "")
If len(S) = 0 Then S = "0"
S = FormatNumber(s,0)
response.write T & StockLevel(s) & "</td>" & vbCrLf
T = "<td align='right' valign='top' " & bCOLOR & ">"
S = Trim(rs("Price") & "")
If len(S) = 0 Then S = "0.00"
S = FormatCurrency(s,2)
response.write T & S & "</td>" & vbCrLf
Response.Write "</tr>" & vbCrLf
rs.Movenext
Next
Response.Write "</table>" & vbCrLf
Response.Write DoNav(myRETURN, iPage, rs.PageCount)
End if 'recordcount
rs.Close
set rs=nothing
Else 'sSEARCH
response.write "<p>Please Specifiy a Search String, <a href='Search.Asp'>Search Again</a><p>"
End If 'sSEARCH
Case Else
%>
<p>Please search our catalog of products by supplying a keyword.</p>
<form method="POST" action="Search.asp?VW=1&PG=1">
<table border="0" cellspacing="0" cellpadding="3">
<tr>
<td>Search String</td>
<td><input type="text" name="SEARCH" size="20"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset"
name="B2"></td>
</tr>
</table>
<p> </p>
</form>
<%
End Select
%>
<!-- #INCLUDE FILE="includes/Footer.asp" -->
</body>
</html>