Managing Each Product in the Store Manager

 

Introduction.  Now we really get down to the nitty gritty of managing each product.  This interface is more sophisticated because of the variety of things you need to be able to do with each product.  Remember, with the ListProducts.asp we were setting up options for "grosser" operations on products, adding products, finding products, listing them out.  Even when we wanted to add a product we needed to create a more specialized interface in NewProduct.asp.  Now we will focus on what needs to be donw to manage each product that might be selected by the user. 

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

 

Stored Procedure ASP Container
sp_RetrieveProduct

sp_RetrieveDeptByProd

sp_RetrieveDepts

sp_Attributes

sp_RetrieveAttributes

ManageProduct.asp

 

The Stored Procedures.  First we present the stored procedures.  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.  

Since three of the stored procedures listed above have been developed already we will provide a link to the page that contains the ASP files used to create them.  Remember, the ability to use stored procedures in a number of different locations in a web via the ASP middleware is one of the advantages of using them.

The sp_RetrieveProduct uses a SELECT command to retrieve all of the fields in the record for a specified product in the Products table.  This was developed when we developed the Product.asp for the store.

The sp_RetrieveDeptByProd is relatively complicated in its simplicity and it is used to select all of the fields from the Department and DepartmentProduct tables for a particular product.  This allows you to find out all of the departments the product is listed in.  The following file should be called sp_RetrieveDeptByProd.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_RetrieveDeptByProd " & _
"@idProduct int AS " & _
"select * from department, departmentproducts " & _
"where departmentproducts.idProduct = @idProduct and " & _
"department.iddepartment = departmentproducts.iddepartment"

connDeptProds.execute(strSQLCreate)

connDeptProds.Close
Set connDeptProds = Nothing

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

%>

 

The sp_RetrieveDepts uses a SELECT command to retrieve all of the information within the Department table.  This was developed when we developed the Dept.asp for the store.

The sp_Attributes uses a SELECT command to return attributes for a specified product from four different tables, Products, ProductAttribute, Attribute and AttributeCategory.  This was developed when we developed the Product.asp for the store.

The sp_RetrieveAttributes is returns all of the information from the Attribute and AttributeCategory tables.  The following file should be called sp_RetrieveAttributes.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_RetrieveAttributes AS " & _
"select * from attribute, attributecategory " & _
"where attribute.idattributecategory = attributecategory.idAttributeCategory " & _
"order by attributecategory.chrCategoryName"

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 ManageProduct.asp.  This is essentially a form page to get the inputs for the new product.  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. The next section of code creates a form that submits to UpdateProduct.asp in order to update the field entries of the product.
    1. idProduct
    2. chrProductName
    3. txtDescription
    4. chrProductImage
    5. intPrice
    6. intActive
  4. The next section displays the departments to which this product belongs in a table with an option to delete this particular assignment next to it.  These will link to the RemoveProdDept.asp and pass the idDepartment.
  5. The next section contains a small form allows containing a select box that lists the available department names dynamically developed from the database.  This allows the manager to add a department linkage to the product using a form associated by posting the results to the ProdAddDept.asp.
    1. Notice the clever use of a hidden variable to pass the idProduct to the ProdAddDept.asp
  6. The next section after the horizontal rule allows the manager to manage the attributes of the product.  
    1. The first sub-section allows the deletion of current attributes listed in a table with an adjacent delete option.  The delete is performed by linking to the DeleteAttribute.asp and passing the idProduct and idProductAttribute in the URL string.
    2. The second sub-section uses a small form to list out the attributes that could be added to the product.  This form posts its results to the AddAttribute.asp.  It also dynamically builds the select boxes for each attribute category based on the results of a stored procedure.

Call the file ManageProduct.asp.

<%@ Language=VBScript %>
<!-- #Include file="include/validatecheck.asp" -->
<html>
<!-- ManageProduct.asp - Provides the tools to manage
the product data. -->


<head>
<meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</head>
<body>

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

<%

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

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

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

dbProduct.Open

' The sp_RetrieveProduct stored procedure is utilized to 
' get the data for the specified product.

sql = "execute sp_RetrieveProduct " & request("idProduct")

' Execute the statement
set rsProduct = dbProduct.Execute(sql)

%>

<!-- Start the form to update the product data. -->
<form method="post" action="UpdateProduct.asp">

<!-- Start the table to display the product data. -->
<table cellpadding="3" cellspacing="3">

<tr>
<td align="right"><b>Preview Product:</b></td>
<!-- To preview the product a link is built to 
the product.asp page in the live store. 
The Id of the product is passed on the URL. 
Notice the relative path
-->
<td><a href="../product.asp?idProduct=<%=request("idProduct")%>">Preview</a></td>
</tr>

<tr>
<td align="right"><b>Delete Product:</b></td>
<!-- A link to the deleteproduct.asp page is
created to remove the product from the
database. The ID of the product is
passed. -->

<td><a href="DeleteProduct.asp?idProduct=<%=request("idProduct")%>">Delete</a></td>
</tr>

<tr>
<td colspan="2"><hr></td>
</tr>
<tr>
<!-- The product ID is displayed. To ensure the ID
can be retrieved for the update a hidden 
HTML element is created. -->

<td align="right"><b>Product ID:</b></td>
<td><%=rsProduct("idProduct")%>
<input type="hidden" 
value="<%=request("idProduct")%>" name="idProduct">
</td>
</tr>
<tr>
<td align="right"><b>Product Name:</b></td>
<!-- Display the product name. -->
<td><input type="text" 
value="<%=rsProduct("chrProductName")%>" 
name="chrProductName" size="60">
</td>
</tr>
<tr>
<td align="right"><b>Product Description:</b></td>
<!-- Display the product description. -->
<td><textarea cols="50" rows="10" name="txtDescription"><%=rsProduct("txtDescription")%></textarea></td>
</tr>
<tr>
<td align="right"><b>Product Image:</b></td>
<!-- Display the product image file name and display 
the image as well. 
Notice the relative path to the image file -->

<td><input type="text" value="<%=rsProduct("chrProductImage")%>" 
name="chrProductImage"> &nbsp;&nbsp;&nbsp;&nbsp;
<img src="../images/products/sm_<%=rsProduct("chrProductImage")%>" align="center"></td>
</tr>
<tr>
<td align="right"><b>Product Price:</b></td>
<!-- The product price is displayed. -->
<td><input type="text" value="<%=rsProduct("intPrice")/100%>" 
name="intPrice"></td>
</tr>
<tr>
<td align="right"><b>Active:</b></td>
<td>

<%

' Check to see if the product is active. 
if rsProduct("intActive") = 1 then

%>

<!-- Display the check box checked if the product is active. -->
<input type="checkbox" value="1" CHECKED name="intActive">

<%
else
%>

<!-- Display the check box with out the check. -->
<input type="checkbox" value="1" name="intActive">

<% end if %>

</td>
</tr>

<tr>
<td colspan="2" align="center">
<!-- Submit button for the form update -->
<input type="submit" value="Update Product" name="Submit">
</td>
</tr>

</table>
</form>

<hr>

<%

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

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

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

dbDeptProd.Open

' Execute the sp_RetrieveDeptByProd to retrieve
' the departments for the product being edited.

sql = "execute sp_RetrieveDeptByProd " & request("idProduct")

' Execute the statement
set rsDeptProd = dbDeptProd.Execute(sql)

%>

<!-- Start the table to display the department list. -->
<table cellpadding="3" cellspacing="3" border="1">
<tr>
<th>Department</th>
<th>Delete</th>
<tr>
<%
' Loop through the departments.
do until rsDeptProd.eof
%>

<tr>
<!-- Display the department name. -->
<td><%=rsDeptProd("chrDeptName")%></td>
<!-- Build a link to the RemoveProdDept.asp page
to remove the department from the list. -->

<td><a href="RemoveProdDept.asp?idProduct=<%=request("idProduct")%>
&idDepartmentProduct=<%=rsDeptProd("idDepartmentProduct")%>">
Delete</a>
</td>
</tr>

</tr>

<%
' Move to the next department product
rsDeptProd.movenext

Loop
%>

</table>

<br>

<!-- Start a new form to add an additional
department for the product. -->

<form method="post" action="ProdAddDept.asp">
<b>Add a department:</b>

<%

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

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

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

dbDepts.Open

' Retrieve all of the departments in the database
sql = "execute sp_RetrieveDepts"

' Execute the statement
set rsDepts = dbDepts.Execute(sql)

%>

<!-- Start the select box for the list of
departments. -->

<select name="idDepartment">
<%
' Loop through the departments
do until rsDepts.eof
%>

<!-- Build the option list for each 
department. -->

<option value="<%=rsDepts("idDepartment")%>">
<%=rsDepts("chrDeptName")%>

<%
' Move to the next row
rsDepts.movenext

loop
%>

</select>

<!-- Build a hidden variable in this form so
we know what product to assign this
department to. -->

<input type="hidden" name="idProduct" 
value="<%=request("idProduct")%>">

<!-- Submit button for the form. -->
<input type="submit" value="Submit" name="Submit">

</form>

<hr>

<%

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

' Create a record set
set rsAttributes = server.CreateObject("adodb.recordset")

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

dbAttributes.Open

' Execute the stored procedure to retrieve the attributes for the products.
sql = "execute sp_Attributes " & request("idProduct")

' Execute the SQL statement
set rsAttributes = dbProduct.Execute(sql)

%>
<!-- Start the table to build list of color and size attributes. -->
<table>

<tr>
<td>
<b>COLOR:</b>

<!-- Build a list of current color assignments. --> 
<table cellpadding="3" cellspacing="3" border="1">

<%

' Loop through the attributes.
do until rsAttributes.EOF

' Check to see if we have moved beyond the color attribute in the list..
if rsAttributes("chrCategoryName") <> "Color" then 

' Exit the do loop
exit do

end if

%>

<tr>
<td>
<!-- Display the attribute name. -->
<%=rsAttributes("chrAttributeName")%>
</td>
<td>
<!-- Build a link to the DeleteAttribute.asp page
to remove the attribute for the product. -->

<a href="DeleteAttribute.asp
?idProduct=<%=request("idProduct")%>
&idProductAttribute=<%=rsAttributes("idProductAttribute")%>">
Delete</a>
</td>
</tr>
<%

' Move to the next row
rsAttributes.MoveNext

' Loop back
loop

%>

</table>
</td>
</tr>
<!-- Build a buffer between the listings. -->
<tr><td>&nbsp;</td></tr>
<tr> 
<!-- Start the size listings -->
<td>
<b>SIZE: </b>

<!-- Start the size listing table. -->
<table cellpadding="3" cellspacing="3" border="1">

<%

' Loop through the size attributes
do until rsAttributes.EOF

%>

<tr>
<td> 
<!-- Display the name of the size attribute -->
<%=rsAttributes("chrAttributeName")%>
</td>
<td>
<!-- Build a link to the DeleteAttribute.asp 
page to remove the size setting for the product. -->

<a href="DeleteAttribute.asp
?idProduct=<%=request("idProduct")%>
&idProductAttribute=<%=rsAttributes("idProductAttribute")%>">
Delete</a>
</td>
</tr>

<%

' Move to the next row
rsAttributes.MoveNext

' Loop back
loop

%>

</table>

</td>

</tr>

</table>

<!-- Call the ShowAttributeList subroutine --> 
<% ShowAttributeList %>

<!-- Close out the page -->
</body>
</html>

<!-- Start the ShowAttributeList subroutine. -->

<% 

' Start the subroutine.
Sub ShowAttributeList() 

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

' Create a record set
set rsAttributes = server.CreateObject("adodb.recordset")

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

dbAttributes.Open

' Execute the stored procedure to retrieve the attributes in the database.
sql = "execute sp_RetrieveAttributes"

' Execute the SQL statement
set rsAttributes = dbProduct.Execute(sql)

%>

<br>

<!-- Start the option to build the list of 
attributes in the database. -->

<b>Select an Attribute to Add:</b>

<table>

<tr>
<!-- Color column -->
<td>
<!-- Build a form to post the adding of the attribute. -->
<form method="post" action="AddAttribute.asp">
Color: 

<!-- Select box for display of the color options --> 
<select name="idAttribute">

<%

' Loop through the attributes.
do until rsAttributes.EOF

' Check to see if we have moved beyond the color attribute in the list..
if rsAttributes("chrCategoryName") <> "Color" then 

' Exit the do loop
exit do

end if

%>

<!-- Build the option value for the color. The value will be
the ID of the color -->

<option value="<%=rsAttributes("idAttribute")%>">
<%=rsAttributes("chrAttributeName")%>

<%

' Move to the next row
rsAttributes.MoveNext

' Loop back
loop

%>

</select>

<!-- Build a hidden variable to store the id 
of the product so we know what product to
add the attribute to. -->

<input type="hidden" value="<%=rsProduct("idProduct")%>" 
name="idProduct">

<!-- Submit button to add the attribute to the list. -->
<input type="Submit" value="Add" name="Submit">
</form>
</td>
</tr>
<tr> 
<!-- Build the size attributes select box. -->
<td>
<form method="post" action="AddAttribute.asp">
Size: 

<!-- Start the size select box -->
<select name="idAttribute">

<%

' Loop through the size attributes
do until rsAttributes.EOF

%>

<!-- Display the options -->
<option value="<%=rsAttributes("idAttribute")%>">
<%=rsAttributes("chrAttributeName")%>

<%

' Move to the next row
rsAttributes.MoveNext

' Loop back
loop

%>

</select>

<!-- Build the hidden HTML element to store the product id. -->
<input type="hidden" value="<%=rsProduct("idProduct")%>" 
name="idProduct">

<!-- Build the submit button for the form. -->
<input type="Submit" value="Add" name="Submit">
</form>
</td>

</tr>

</table>

<%
End Sub 
%>

 

After you upload this ASP and access it in your web, probably by clicking on the Joe Bob's Thimble Sounds link you should see something like the following.  Notice you need to scroll in order to get access all parts of this form.

 

 

Notice the very large variety of options available on this page.
  • Preview - which is already enabled
  • Delete - delete the product which we have yet to implement
  • Update Product - after modifying some basic information - which we have yet to implement
  • Delete - delete the department connection to this product - yet to be implemented
  • Submit - add a department connection to this product - yet to be implemented
  • Add - add a color attribute to this product - yet to be implemented
  • Add - add a size attribute to this product - yet to be implemented