More Detailed Information About Each Product

 

Introduction.  Now we need to develop an ASP that focuses on using a dynamic publishing approach to display more detailed information about each product.  The page is called Product.asp.  This page requires two stored procedures.

The following table displays the names of the stored procedures and the ASP file in which they are called.

 

Stored Procedure ASP Container
sp_RetrieveProduct

sp_Attributes

 Product.asp

 

Finally, you need to get some images to be used for the individual product pages, 
  • You should copy them into an images/products folder within the WildWillies directory where you have the ASPs.

 

Product Images
Regular Size
Name Image
thimble.gif
static.gif
jazz.gif
circle.gif
hatsoff.gif
lassos.gif
DogCat.gif
candles.gif
hole.gif
under.gif

 

 

The Stored Procedures.  Now I will list out the two stored procedures that you should create using SQL in almost the same way we created the tables.  The first file should be called sp_RetrieveProduct.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_RetrieveProduct @idProduct int AS " & _
"select * from products where idProduct = @idProduct"

connDeptProds.execute(strSQLCreate)

connDeptProds.Close
Set connDeptProds = Nothing

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

%>

 

 

The second file should be called sp_Attributes.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_Attributes @idProduct int AS " & _
"select products.idproduct, attribute.idattribute, attribute.chrattributename, attributecategory.chrcategoryname," & _
"productattribute.idproductattribute from products, productattribute, attribute, attributecategory " & _
" where products.idproduct = @idProduct and productattribute.idproduct = @idProduct and " & _
" productattribute.idattribute = attribute.idattribute and attribute.idattributecategory = attributecategory.idattributecategory " & _
"order by chrcategoryname"

connDeptProds.execute(strSQLCreate)

connDeptProds.Close
Set connDeptProds = Nothing

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

%>

 

Like all of the other ASPs based on CREATE SQL commands, these files need to be uploaded to your web and then executed once.  After they have been executed, you should get an error if you try to execute them again because the stored procedures should already be there.  After these sp_name.asp files have been used they should be removed from your space on the server.

 

The Product.asp.  The following Product.asp will 
  1. open a connection to the database.
  2. create the recordset based on the results of a select query in sp_RetrieveProduct.asp so that you get information about a particular product the user selected.
  3. create another recordset based on the attributes of the product  using sp_Attributes.asp.
  4. get the name, image and attributes to each product from the recordset and display them.
  5. display options for purchasing, though as of yet they still don't function.
<%@ Language=VBScript %>
<HTML>
 <!-- Product.asp - Displays the product information. -->

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

<%

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

 ' Create a 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

 ' Execute the stored procedure to retrieve the product and pass in the id of the product.
sql = "execute sp_RetrieveProduct " & request("idProduct")

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

 ' Retrieve the product data
txtDescription = rsProduct("txtDescription")
chrProductImage = rsProduct("chrProductImage")
chrProductName = rsProduct("chrProductName")
intPrice = rsProduct("intPrice")
idProduct = rsProduct("idProduct")
%>

 <!-- The additem.asp page will be called to add the product to the basket -->
<form method="post" action="additem.asp">

 <!-- The table will provide the layout structure for the product. -->
<table border="0" cellpadding="3" cellspacing="3">

 <!-- Row to display the product image, name and description. -->
<TR>
 <!-- Display the image -->
<td><img src="images/products/<%=chrProductImage%>"></td>

 <!-- Show the product name and description -->
<td valign="top">
<CENTER><b><font size="5"><%=chrProductName%></font></b></center>
<BR><BR>
<%=txtDescription%><BR><BR>
</td>
</TR>

 <!-- Show the product price. An input quantity box is created. Also, several hidden variables will hold 
key data for adding the product to the database. -->
<TR>
<TD align="center"><B>Price: 
<%=formatcurrency(intPrice/100, 2)%></b>
</td>

<TD align="center">
<B>Quantity: 
<input type="text" value="1" name="quantity" size="2"></b>
<input type="hidden" value="<%=idProduct%>" name="idProduct">
<input type="hidden" value="<%=chrProductName%>" name="ProductName">
<input type="hidden" value="<%=intPrice%>" name="ProductPrice">
</td>
</TR>

<%

 ' 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)

 ' Loop through and display the attributes for the product.
if not rsAttributes.EOF then

%>

<TR>
 <!-- Color column -->
<TD>
Color: 
 <!-- Select box for display the color options --> 
<SELECT name="color">

<%

 ' 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("chrAttributeName")%>">
<%=rsAttributes("chrAttributeName")%>

<%

 ' Move to the next row
rsAttributes.MoveNext

loop

%>

</select>
</TD>

 <!-- Size column -->
<TD>
Size: 

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

<%

 ' Loop through the size attributes
do until rsAttributes.EOF

%>

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

<%

' Move to the next row

rsAttributes.MoveNext

loop

%>

</select>
</TD>
</TR>

<%

end if

%>

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

</table>

</form>

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

</BODY>
</HTML>

 

Well, how's that for using ASPs and SQL Server to actually do something realistic!  Far too often students think that course work is artificially difficult.  That is why I try to make sure and tie my coding to realistic situations.  The "real world" of coding always requires more than you think it will!