Browsing the Departments and Products

 

Introduction.  Now we need to develop the ASPs that will likely be used for browsing the products within a department.   Now we develop Products.asp.  This will be presented after developing stored procedures and displaying required images.

We will create two stored procedures. to retrieve the department/products relationships and then the products associated with the department.  The following table displays the names of the stored procedures and the ASP file in which they are called.

 

Stored Procedure ASP Container
sp_RetrieveDeptProducts

sp_RetrieveDept

Products.asp

 

 

Now, you need to get some images to be used for the products on the department pages, 
  • All of the images are used for displaying the product's thumbnail.  These are contained in the following table.  
  • You should copy them into an images/products folder within your WildWillies directory where you have the ASPs.

 

Product Images
Thumbnails
Image Name
sm_thimble.gif
sm_static.gif
sm_jazz.gif
sm_circle.gif
sm_hatsoff.gif
sm_lassos.gif
sm_DogCat.gif
sm_candles.gif
sm_hole.gif
sm_under.gif

 

 

The Stored Procedures.  Now I will list out the five stored procedures that you should create using SQL in almost the same way we created the tables.  The first file should be called sp_RetrieveDeptProducts.asp.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_RetrieveDeptProducts @idDept int AS " & _
"select * from products, departmentproducts " & _
"where products.idproduct = departmentproducts.idproduct and departmentproducts.iddepartment = @idDept"

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_RetrieveDept.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_RetrieveDept @idDepartment int AS " & _
"select * from department where idDepartment = @idDepartment"

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 Products.asp.  The following Products.asp will 
  1. open a connection to the database.
  2. create the recordset based on the results of a select query in sp_RetrieveDept so that you get information about a particular department the user selected.
  3. create another recordset based on the products within the department using sp_RetrieveDeptProducts.asp.
  4. get the name, image and link to each product in the department from the recordset and display them.
  5. the flag is used to make sure the images alternate columns with the text in the table used for their display.
<%@ Language=VBScript %>
<HTML>
<!-- Products.asp - This page displays the products in a department. -->

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

<% 

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

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

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

dbDepartment.Open

' Build the SQL statement. We are calling the stored procedure to retrieve the department
' information and passing in the ID of the department
sql = "execute sp_RetrieveDept " & request("idDept")

' Retrieve the department
set rsDepartment = dbDepartment.Execute(sql)

' Retrieve the product information
txtDescription = rsDepartment("txtDeptDesc")
chrDeptImage = rsDepartment("chrDeptImage")
chrDeptName = rsDepartment("chrDeptName")

 ' Store the ID of the deparment being referenced in the LastIDDept session variable. This will allow us
' to build a link on the basket back to the department for further shopping.
session("LastIDDept") = request("idDept")

%>

 <!-- Display the department image and name -->
<CENTER>
<img src="images/<%=chrDeptImage%>" align="middle">
<FONT size="4"><B><%=chrDeptName%></b></font><BR><BR>
</CENTER>

 <!-- Display the description -->
<%=txtDescription%> Select a product:<BR><BR>

<% 
' 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 ODBC file DSN
dbProducts.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=WildWillies;UID=cis; PWD=csatqu"

dbProducts.Open

 ' Build the sql statement to retrieve the products in
 ' the department. The ID of the department is passed in.
sql = "execute sp_RetrieveDeptProducts " & request("idDept")

 ' Execute the SQL statement and retrieve the record set
set rsProducts = dbProducts.Execute(sql)

 ' We are going to alternate the images from left to the right side in a table.
Flag = 0

 ' Loop through the products record set
do until rsProducts.EOF

' Retrieve the product information to be displayed.

chrProductName = rsProducts("chrProductName")
chrProductImage = rsProducts("chrProductImage")
idProduct = rsProducts("idProduct")

' Check the display flag. We will rotate the product images from left to right.

If flag = 0 then

' Set the flag

flag = 1

%>

 <!-- Build the link to the product information. -->
<a href="product.asp?idProduct=<%=idProduct%>">
<img src="images/products/sm_<%=chrProductImage%>" 
align="middle" border="0"> 
<%=chrProductName%></a><BR><BR>

<% else %>

<!-- Build the link to the product information. -->
<a href="product.asp?idProduct=<%=idProduct%>">
<%=chrProductName%> 
<img src="images/products/sm_<%=chrProductImage%>" 
align="middle" border="0"></a><BR><BR>
<%

 ' Reset the flag
Flag = 0

end if

' Move to the next row

rsproducts.movenext

loop

%>

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

</BODY>
</HTML>

 

Notice how this ASP makes use of the Header.asp and Footer.asp for navigation and page closure.