Adding Departments in the Store Manager

Introduction. Now that we have taken care of listing our existing departments, we need to start looking at other typical database functions. In this page we develop the functionality to add new departments The interface for this is done in an ASP called NewDept.asp. Since NewDept.asp is essentially a form, it needs a processing script to actually add the new product to the database. This processing script will be called AddNewDepartment.asp. This ASP will rely on one stored procedure called sp_InsertDept to actually implement the insertion into the table. These will be presented on this page and some discussion will ensue.

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

The Stored Procedure. First we present the stored procedure. Remember, you do not need to implement the stored procedures, this is to illustrate what you would need to do if you had the correct permissions in your database. This stored procedure uses an INSERT INTO command in SQL to take the new department fields and insert them into the Department table. It also uses a SELECT command to increment the idDepartment. The file should be called sp_InsertDept.asp.

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' 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_InsertDept " & _
"@chrDeptName varchar(255), " & _
"@txtDeptDesc text, " & _
"@chrDeptImage varchar(100) AS " & _
"insert into department(chrDeptName, txtDeptDesc, chrDeptImage) " & _
"values(@chrDeptName, @txtDeptDesc, @chrDeptImage) " & _
"select idDepartment = @@identity"

connDeptProds.execute(strSQLCreate)

connDeptProds.Close
Set connDeptProds = Nothing

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

%>

As with all of the stored procedures you create, this file would only need to be executed once in your database. After that you should get an error that the stored procedure already exists. Since this is linked to the WildWillies database you do not even need to do this much. But, remember, if you do execute such an ASP you can delete the ASP immediately after the stored procedure has been created.

The NewDept.asp. This is essentially a form page to get the inputs for the new department. You need to save this file and upload it to your StoreManager subdirectory. This ASP will do the following.

It uses the validatecheck.asp include to make sure the user didn't try to circumvent the security

It then uses the navinclude.asp to create the common navigation at the top of each page in the store manager.

It displays a form for the inputs associated with the product and posts them to the AddNewDepartment.asp.

Call the file NewDept.asp.

<%@ Language=VBScript %>

<HTML>


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



<form method="post" action="AddNewDepartment.asp">


<table cellpadding="3" cellspacing="3">


<tr>
<td>Department Name</td>
<td><input type="text" value="" name="chrDeptName"</td>
</tr>


<tr>
<td>Department Description</td>
<td><textarea name="txtDeptDesc" cols="40" rows="5"></textarea></td>
</tr>


<tr>
<td>Department Image</td>
<td><input type="text" value="" name="chrDeptImage"></td>
</tr>


<tr>
<td colspan="2">
<input type="Submit" value="Add Department"
name="Submit"></td>
</tr>


</table>

</form>

</BODY>
</HTML>

Now that we have the form for inputs, we need the processing script

The AddNewDepartment.asp. This is a relatively straight forward ASP with the real work done by the stored procedure. You need to save this file and upload it to your StoreManager subdirectory. This ASP will do the following.

It cleans up the inputs using Request and Replace objects.

It modifies the price to an integer.

Then it uses the sp_InsertDept to insert the basic product information into the database.

Then it redirects the user to the ManageDept.asp in order to do things like modify the name, description and image name.

Call the file AddNewDepartment.asp.

<%@ Language=VBScript %>
<%
' ****************************************************
' AddNewDepartment.asp - Handles adding a new
' department to the store.
' ****************************************************

' Retrieve the department name and ensure any single
' quotes are doubled.
chrDeptName = replace(request("chrDeptName"), "'", "''")

' Retrieve the department description and ensure any
' single quotes are doubled.
txtDeptDesc = replace(request("txtDeptDesc"), "'", "''")

' Retrieve the department image file name.
chrDeptImage = request("chrDeptImage")

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

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

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

dbDept.Open

' Execute the sp_InsertDept stored procedure to add
' the new department into the database.
sql = "execute sp_InsertDept '" & _
chrDeptName & "', '" & _
txtDeptDesc & "', '" & _
chrDeptImage & "'"

' Execute the statement
set rsDept = dbDept.Execute(sql)

' Redirect the user to the ManageDept.asp page to do some
' any editing on the department. Note that the ID of the
' new department is returned from the stored procedure.
Response.Redirect "ManageDept.asp?idDepartment=" & _
rsDept("idDepartment")

%>

Stored Procedure	ASP Container
	NewDept.asp
sp_InsertDept	AddNewDepartment.asp