Listing Products in the Store Manager

 

Introduction.  Now we are getting into actually managing the products and the first thing we need to do is list out the products in an intelligent fashion.  This will be done in an ASP called ListProducts.asp.  This ASP will rely on one stored procedure called sp_ManagerRetrieveProducts.  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_ManagerRetrieveProducts ListProducts.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.  The file should be called sp_ManagerRetrieveProducts.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_ManagerRetrieveProducts " & _
"@intStartProdID int, @intRowCount int AS set rowcount @intRowCount " & _
"select idProduct, chrProductName, intPrice from products where idProduct >= @intStartProdID"

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 ListProducts.asp.  Now we will get to what is one of our main overall interfaces for managing products.  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_ManagerRetrieveProducts to retrieve the pre-specified number of products for the listing 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 post search information to SearchProducts.asp which will be developed later.

Call the file ListProducts.asp.
<%@ Language=VBScript %>
<!-- #Include file="include/validatecheck.asp" -->
<HTML>
<!-- ListProducts.asp - Lists the products in the store. -->

<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

' The products will not all be displayed at once. We
' want to set the Product Increment in a session variable.
' If the product increment is not set, then we will set 
' it. In this case, we are defaulting it to 4. 
if session("ProdInc") = "" then 

' Default it to 4
session("ProdInc") = "4"

end if

' Get the starting point where this list should begin.
StartProd = request("StartProd")

' If there is no starting point, then we will default it to 1.
if StartProd = "" then 

StartProd = 1

end if

' If the user tries to decrement past the first product,
' we default back to 1 for the first product.
if StartProd < 1 then

StartProd = 1

end if

' Build the stored procedure to retrieve the list of
' products starting at the specified beginning product
' and for the specified increment.
sql = "execute sp_ManagerRetrieveProducts " & _
StartProd & ", " & session("ProdInc")

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

' Ensure some products are returned.
if rsProducts.EOF then

' If none were, then lets return to the beginning of the list.
StartProd = 1

 ' Build the stored procedure
sql = "execute sp_ManagerRetrieveProducts " & _
StartProd & ", " & session("ProdInc")

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

end if

%>

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

<!-- Start the display of the product listing. -->
<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 returned products.
do until rsProducts.EOF 

%>

<!-- Build a row to display the list of products. -->
<tr>
 <!-- A link is built to the ManageProduct.asp page.
The id of the product is passed on the URL and
the ID of the product is displayed. -->
<td>
<a href="ManageProduct.asp?idProduct=<%=rsProducts("idProduct")%>">
<%=rsProducts("idProduct")%></a></td>

 <!-- A link is built to the ManageProduct.asp page.
The id of the product is passed on the URL and
the name of the product is displayed. -->
<td>
<a href="ManageProduct.asp?idProduct=<%=rsProducts("idProduct")%>">
<%=rsProducts("chrProductName")%></a></td>

 <!-- Display the product price. Note that the price 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>
<!-- This navigation is built to allow the shopper to move
back and forth between the product listings. Note that
each link has the starting product number on the URL.
The StartProd variable is set appropriately corresponding
to the Procuct Increment session variable. -->
<a href="ListProducts.asp?StartProd=1">First Product</a> | 
<a href="ListProducts.asp?StartProd=
<%=StartProd - cint(Session("ProdInc"))%>">Previous</a> | 
<a href="ListProducts.asp?StartProd=
<%=StartProd + cint(Session("ProdInc"))%>">Next</a> 

<BR><BR>

<!-- Build a form to allow the user to search for
a specific product. The link is built to the
SearchProducts.asp page. -->
<form method="post" action="SearchProducts.asp">

<!-- The table is created to display the search option -->
<table>
<tr>
<td align="right">Search Text:</td>
 <!-- The input text box for the search text. -->
<td><input type="text" value="" name="SearchText"></td>
</tr>
<tr>
<td colspan="2">
 <!-- The submit button for the form. -->
<input type="submit" value="Submit" name="Submit">
</td>
</tr>
</table>

</form>

</BODY>
</HTML>

 

After you upload this ASP and access it in your web you should see something like the following.

 

 

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