Implementing User Searching Capabilities

 

Introduction.  We have now developed the major components for our store in terms of Departments and Products.  But it may be the case that a user doesn't classify their music in the same way as we do, or maybe they want to move specifically to a location based on some other information.  This is best done by creating searching capabilities.  This will be much less complicated than what we just did and we will have just one ASP and one stored procedure.

We will develop one stored procedure and one ASP file.  While both are decently elaborate, these are all that are required.

 

The Stored Procedures.  This stored procedure gets the SearchText, High end of acceptable prices and the Low end of acceptable prices from the user.  Then it uses these to select from the products everything that meets the criteria.   The first file should be called sp_SearchProducts.asp.
<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include File="adovbs.inc"-->

<%
' Open a connection to our SQL Server database
' We will use the ADO Driver connection
Dim connfoxFire, strSQLCreate
Set connfoxFire = Server.CreateObject("ADODB.Connection")
connfoxFire.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"

connfoxFire.Open

' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_SearchProducts @SearchText varchar(255), @Low int, @High int AS " & _
"select * from products where (chrProductName like '%' + @SearchText+ '%' or " & _ 
"txtDescription like '%' + @SearchText + '%') and " & _
"(intPrice >= @low and intPrice <= @High) order by chrProductName"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "<font size = 5>The SQL has executed</font>"

%>

 

 

Now we need to creatour ASP that will execute this stored procedure to open a record set and then develpo the table for displaying links to the products that are selected.   The ASP will do essentially do the following.
  1. This ASP starts out developing the form to take user inputs for the search
    1. Text for search
    2. Low end price
    3. High end price
  2. The results of this form are posted back to the same Search.asp
  3. As long as at least one of the inputs isn't blank the processing continues at which point the links for the potential selections are displayed.

You want to name this  Search.asp.
<%@ Language=VBScript %>
<HTML>
<!-- Search.asp - Provides searching capabilities for finding products. -->

<!-- #include file="include/header.asp" -->

<BR>

<!-- Build the search form. Note we post to this page. --> 
<form method="post" action="search.asp">

<!-- Table to display the search options -->
<table border="0">

<!-- Display the text search option -->
<tr>
<td align="right"><b>Enter your search text:</b></td>
<!-- Input text box -->
<td align="right"><input type="text" 
value="<%=request("search")%>" name="Search">
</td>
</tr>

<!-- Provide a product price range search -->
<tr><td><b>Price Range:</b></td> 
<td align="right">Low:
<input type="text" value="<%=request("low")%>" name="Low"></td>
</tr>

<!-- High price search option -->
<tr><td></td>
<td align="right">High: <input type="text" 
value="<%=request("high")%>" name="High"></td>
</tr>

<!-- Break column -->
<tr><td colspan="2">&nbsp;</td></tr>

<!-- Submit button -->
<tr><td colspan="2" align="center">
<input type="submit" value="Submit" name="Submit">
</td></tr>

</table>

</form>

<%

' Check to see if a search request was posted to page. 
if request("search") <> "" or request("low") <> "" or request("high") <> "" then

' Create an ADO database connection
set dbSearch = server.createobject("adodb.connection")

' Create a result set
set rsSearch = server.CreateObject("adodb.recordset")

 ' Open the connection using our SQL Server DSN-less connection
dbSearch.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=WildWillies;UID=cis; PWD=csatqu"

dbSearch.Open

 ' Check to see if the low search was set and is a number.
If request("Low") = "" or isnumeric(request("low")) = false then 

' Default to 0
Low = 0

else

' Set value to the data entered. Note that prices are stored in whole number so we must
' mulitply the value times 100.
Low = request("Low") * 100

end if

 ' Check to see if the high search was set and is a number.
if request("High") = "" or isnumeric(request("High")) = false then 

' Default to a very high number
High = 99999999 

else

' Get the value and multiply times 100
High = Request("High") * 100

end if

 ' Build an SQL query that will return the requested
' products. The search text and price range is passed in.
sql = "execute sp_SearchProducts '" & request("search") & "', " & Low & ", " & High

 ' Execute the SQL statement
set rsSearch = dbSearch.Execute(sql)

%>

 <!-- Start the list -->
<UL>

<%

 ' Loop through the record set
do until rsSearch.eof

%>

 <!-- Display the List -->
<li><a href="product.asp?idProduct=<%=rsSearch("idProduct")%>">
<%=rsSearch("chrProductName")%></a></li>

<% 

 ' Move to the next record set
rsSearch.MoveNext

' Loop back
Loop

' End the check
End If

%>

</UL>

<!-- #include file="include/footer.asp" -->

</BODY>
</HTML>

 

Notice that the Header.asp and Footer.asp are stilled being used for navigation and closure.