Searching Products in the Store Manager

 

Introduction.  Assuming our list of products keeps growing, that is we keep stocking a larger variety of CDs, we need to have an automated search.  This will be done in an ASP called SearchProducts.asp.  This ASP will rely on one stored procedure called sp_ManagerRetrieveProdSearch.  These will both be presented on this page and some discussion will ensue.

The following table gives the ASP and stored procedure we will develop in this page.

 

Stored Procedure ASP Container
sp_ManagerRetrieveProdSearch SearchProducts.asp

 

 

The Stored Procedure.  First we present the stored procedure.  Remember, you do not need to implement the stored procedures, this is to illustrate what you would need to do if you had the correct permissions in your database.  This stored procedure will retrieve a pre-specified number of product records starting at some location in the table looking for something LIKE the inputted text.  The file should be called sp_ManagerRetrieveProdSearch.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 connDeptProds, strSQLCreate
Set connDeptProds = Server.CreateObject("ADODB.Connection")
connDeptProds.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"

connDeptProds.Open

' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_ManagerRetrieveProdSearch " & _
"@intStartProdID int, " & _
"@intRowCount int, " & _
"@chrSearchText varchar(100) AS " & _
"set rowcount @intRowCount " & _
"select idProduct, chrProductName, intPrice from products " & _
"where idProduct >= @intStartProdID and chrProductName like '%' + @chrSearchText+ '%'"

connDeptProds.execute(strSQLCreate)

connDeptProds.Close
Set connDeptProds = Nothing

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

%>

 

 

As with all of the stored procedures you create, this file would only need to be executed once in your database.  After that you should get an error that the stored procedure already exists.  Since this is linked to the WildWillies database you do not even need to do this much.  But, remember, if you do execute such an ASP you can delete the ASP immediately after the stored procedure has been created.

The SearchProducts.asp.  You need to save this file and upload it to your StoreManager subdirectory.  This ASP will do the following.

  1. It uses the validatecheck.asp include to make sure the user didn't try to circumvent the security
  2. It then uses the navinclude.asp to create the common navigation at the top of each page in the store manager.
  3. It creates the connection to the database.
  4. Then it uses the sp_ManagerRetrieveProdSearch to retrieve the pre-specified number of products that satisfy the LIKE condition for the search based on a starting location.
    1. This prespecified number can be changed by changing Session("ProdInc").
  5. Then it displays the option for the user to add an entirely new product by linking to NewProduct.asp which will be developed later.
  6. It lists out the retrieved set of products with links to enable modification in ManageProducts.asp which will be developed later.
  7. It lists out some options for scrolling through these products.
  8. Finally, it sets up a form with a textbox and submit button to again post search information to SearchProducts.asp which will be developed later.

Call the file SearchProducts.asp.
<%@ Language=VBScript %>
 <!-- #Include file="include/validatecheck.asp" -->
<HTML>
 <!-- SearchProducts.asp - Provides a feature to search for products from the list. -->

<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

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

<%

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

 ' Create the record set
set rsProducts = server.CreateObject("adodb.recordset")

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

dbProducts.Open

 ' Check to see if there is any search text.
if request("SearchText") <> "" then

' Retrieve the search text
session("SearchText") = request("SearchText")

end if

 ' Check for the product increment setting for the
' number of items to be displayed at once.
if session("ProdInc") = "" then 

' Set the increment to 4 products.
session("ProdInc") = "4"

end if

 ' Get the starting product number
StartProd = request("StartProd")

 ' Check to see if it is set.
if StartProd = "" then

' If not then start at the first product
StartProd = 1

end if

 ' Check to see if the user has moved before the first product
if StartProd < 1 then

' Go to the first product
StartProd = 1

end if

 ' Execute the sp_ManageRetrieveProdSearch stored procedure
' to get products that match the search criteria.
sql = "execute sp_ManagerRetrieveProdSearch " & _
StartProd & ", " & session("ProdInc") & ", '" & _
Session("SearchText") & "'"

 ' Execute the statement
set rsProducts = dbProducts.Execute(sql)

%>

 <!-- Build a link to the NewProduct.asp page in case the
user wants to add a new product. -->
<BR><b>Click <a href="NewProduct.asp">here</a> 
to add a new product.</b>

 <!-- Build a link to list the full product selection -->
<BR><BR><b>Click <a href="ListProducts.asp">here</a>
to see the full listing.</b>

 <!-- Start the display of the search list. -->
<BR><BR><b>To edit a product, select from the 
list below:</b><BR><BR>

<table cellpadding="3" cellspacing="3">
<tr>
<th>Product ID</th>
<th>Name</th>
<th>Price</th>
</tr>

<%

 ' Loop through the products.
do until rsProducts.EOF 

%>

<tr>
<td>
 <!-- Display the product id. And, build a link to the 
ManageProduct.asp with the product id -->
<a href="ManageProduct.asp?idProduct=<%=rsProducts("idProduct")%>">
<%=rsProducts("idProduct")%></a></td>

<td>
 <!-- Display the product name. And, build a link to the 
ManageProduct.asp with the product id -->
<a href="ManageProduct.asp?idProduct=<%=rsProducts("idProduct")%>">
<%=rsProducts("chrProductName")%></a></td>

 <!-- Show the product price. Note it is stored as an integer. -->
<td><%=formatcurrency(rsProducts("intPrice")/100, 2)%></td>
</tr>

<%

 ' Move to the next row
rsProducts.MoveNext 

' Loop back
Loop

%>

</table>

<BR>
 <!-- Build the navigation to move backwards and forwards
between the product screens. -->
<a href="SearchProducts.asp?StartProd=1">First Product</a> | 
<a href="SearchProducts.asp?StartProd=
<%=StartProd - cint(Session("ProdInc"))%>">Previous</a> | 
<a href="SearchProducts.asp?StartProd=
<%=StartProd + cint(Session("ProdInc"))%>">Next</a> 

<BR><BR>

 <!-- Build the form to execute a new search. -->
<form method="post" action="SearchProducts.asp">

 <!-- Build the table -->
<table>

 <!-- Build the input HTML element for the search text. -->
<tr>
<td align="right">Search Text:</td>
<td><input type="text" value="" name="SearchText"></td>
</tr>

 <!-- Build a submit button for the search. -->
<tr>
<td colspan="2">
<input type="submit" value="Submit" name="Submit">
</td>
</tr>
</table>

</form>

</BODY>
</HTML>

 

After you upload this ASP and access it in your web after entering Shirt as the search text you should see something like the following.

 

 

Notice that while we now have the search capability, we still don't have the navigation header, the ability to add new products and the links associated with each product still aren't active.  In the next few pages we will implement this functionality.