Listing Departments in the Store Manager

 

Introduction.  Now we are getting into actually managing the departments and the first thing we need to do is list them out in an intelligent fashion.  This will be done in an ASP called ListDepts.asp.  This ASP will rely on one stored procedure called sp_RetrieveDepts.  These will be presented on this page and some discussion will ensue.

Fortunately, in general, managing the departments will be much less complicated than managing the products.

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

 

Stored Procedure ASP Container
sp_RetrieveDepts ListDepts.asp

 

The Stored Procedure.  First we present the stored procedure.  This stored procedure is something we have developed and used before, so we don't need to do it again.  Essentially, the sp_RetrieveDepts uses a SELECT command to retrieve all of the fields for all of the records in the Department table.

The ListDepts.asp.  Now we will get to what is one of our main overall interfaces for managing departments.  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. It creates the connection to the database.
  4. Then it uses the sp_RetrieveDepts to retrieve the departments.
  5. Then it displays the option for the user to add an entirely new department by linking to NewDept.asp which will be developed later.
  6. It lists out the retrieved set of idDepartment and the department names with links to enable modification in ManageDept.asp which will be developed later.

Call the file ListDepts.asp.
<%@ Language=VBScript %>
<!-- #Include file="include/validatecheck.asp" -->
<HTML>
<!-- ListDepts.asp - Lists the departments in the store. -->

<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

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

<%

' 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

' Call the sp_RetrieveDepts stored procedure to 
' return the departments in the database.
sql = "execute sp_RetrieveDepts"

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

%>

<!-- Build a link the new department page
if the user wants to add a department. -->
<BR><b>Click <a href="NewDept.asp">here</a> to add 
a new department.</b>

<!-- Start out the structure to show the list of departments. -->
<BR><BR>
<b>To edit a department, select from the list below:</b>
<BR><BR>

<table cellpadding="3" cellspacing="3">
<tr>
<th>Department ID</th>
<th>Name</th>
</tr>

<%

' Loop through the list of departments.
do until rsDepts.EOF 

%>

<!-- Create a link to the ManageDept.asp to work with the department. -->
<tr>
<td>
 <!-- The link to the department needs to include the
id of the department. Following that the id
of the deparment is displayed. -->
<a href="ManageDept.asp?idDepartment=<%=rsDepts("idDepartment")%>">
<%=rsDepts("idDepartment")%></a></td>

 <!-- The link to the department needs to include the
id of the department. Following that the name
of the deparment is displayed. -->
<td>
<a href="ManageDept.asp?idDepartment=<%=rsDepts("idDepartment")%>">
<%=rsDepts("chrDeptName")%></a></td>

</tr>

<%

' Move to the next row
rsDepts.MoveNext 

' Loop back
Loop

%>

</table>

</BODY>
</HTML>

 

After you upload this ASP and access it in your web you should see something like the following.

 

 

Notice the navigation header, the ability to add new departments and the links associated with each department aren't yet active.  In the next few pages we will implement this functionality.