Browsing the Departments

 

Introduction.  Now we need to develop some ASPs that will likely be used for browsing the departments.  The file will be called Dept.asp.  This will be presented after developing stored procedures and displaying required images.

We will create one stored procedure to retrieve the departments.  The following table displays the name of the stored procedure and the ASP file in which it is called.

 

Stored Procedure ASP Container
sp_RetrieveDepts Dept.asp

 

Finally, you need to get some images to be used on the Dept.asp page, 
  • funk.gif, dance.gif, western.gif, punk.gif and tshirts.gif are used for displaying the departments.  These are contained in the following table.  
  • You should copy them into an images folder within the directory where you will have the ASPs.

 

Department Images
Image Name Image
funk.gif
dance.gif
western.gif
punk.gif
tshirts.gif

 

The Stored Procedure.  Now I will list out the stored procedure that you should create using SQL in almost the same way we created the tables.  This file should be called sp_RetrieveDepts.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 connfoxFire, strSQLCreate
Set connfoxFire = Server.CreateObject("ADODB.Connection")
connfoxFire.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"

connfoxFire.Open

' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_RetrieveDepts AS " & _
"select * FROM department"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = 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 Dept.asp.  The following Dept.asp will 
  1. open a connection to the database.
  2. create the recordset based on the results of a select query in sp_RetrieveDepts so that all departments are referenced.
  3. get the name, image and link to each department from the recordset and display them.
  4. the flag is used to make sure the images alternate columns with the text in the table used for their display.
<%@ Language=VBScript %>
<HTML>
<!-- Dept.asp - Displays the departments in the stores. -->

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

<b>Select from a department below:</b><BR><BR>

<% 

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

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

' Call the stored procedure to retrieve the departments in the store.
sql = "execute sp_RetrieveDepts"

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

' We will use a flag to rotate images from left to right
Flag = 0

' Loop through the departments
do until rsDepts.EOF

' Retrieve the field values to display the name, image and link to the ID of the department

chrDeptName = rsDepts("chrDeptName")
chrDeptImage = rsDepts("chrDeptImage")
idDepartment = rsDepts("idDepartment")

' Check the flag

If Flag = 0 then

' Flip the flag

Flag = 1

%>

 <!-- Display the image and the name of the department. 
In this case the image is on the left and the name on the right. -->
<a href="products.asp?idDept=<%=idDepartment%>">
<img src="images/<%=chrDeptImage%>" align="middle" border=0>
<%=chrDeptName%></a><BR><BR>

<% else %>

<!-- Display the image and the name of the department
In this case the image is on the right and the name on the left. -->
<a href="products.asp?idDept=<%=idDepartment%>">
<%=chrDeptName%>
<img src="images/<%=chrDeptImage%>" align="middle" border=0>
</a><BR><BR>

<%

 ' Reset the flag
Flag = 0

end if

' Move to the next row.
rsDepts.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.