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

sp_RetrieveDeptByProd

sp_RetrieveDepts

sp_Attributes

sp_RetrieveAttributes

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.

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.

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.

<!-- 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 
%>

• 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