Adding and Deleting Products in Departments

Introduction. When associating products with departments we have two main things we need to be able to do

Add an association between a product and a department

Delete an association between a product and a department

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

The Stored Procedures. First we present the stored procedures. 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.

The first stored procedure uses a DELETE command in SQL to delete the record for the appropriate product within a department relationship from the DepartmentProduct table. The file should be called sp_DeleteProdDept.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_DeleteProdDept " & _
"@idDepartmentProduct int AS " & _
"delete from departmentproducts where idDepartmentProduct = @idDepartmentProduct"

connDeptProds.execute(strSQLCreate)

connDeptProds.Close
Set connDeptProds = Nothing

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

%>

<%@ 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_AddProdDept " & _
"@idProduct int, " & _
"@idDepartment int AS " & _
"insert into DepartmentProducts(idProduct, idDepartment) " & _
"values(@idProduct, @idDepartment)"

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 RemoveProdDept.asp. This is essentially a processing page to remove a particular departmental association for a product from the DepartmentProducts table. You need to save this file and upload it to your StoreManager subdirectory. Since this ASP is completely an internal processing ASP it will do the following.

Connect to the database and execute the sp_DeleteProdDept stored procedure while passing the idDepartmentProduct to actually delete the record.

Redirect the user back to the ManageProduct.asp so that they can continue managing the products.

Call the file RemoveProdDept.asp.

<%@ Language=VBScript %>
<%
' ****************************************************
' RemoveProdDept.asp - Removes the product from a
' specific department.
' ****************************************************

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

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

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

dbProdDept.Open

' Execute the sp_DeleteProdDept stored procedure to remove
' the department assignment for the product.
sql = "execute sp_DeleteProdDept " & request("idDepartmentProduct")

' Execute the statement
set rsProdDept = dbProdDept.Execute(sql)

' Send the user back to the product management page
Response.Redirect "ManageProduct.asp?idProduct=" & _
request("idProduct")

%>

The ProdAddDept.asp. This is essentially a processing page to add a particular departmental association for a product to the DepartmentProducts table. You need to save this file and upload it to your StoreManager subdirectory. Since this ASP is completely an internal processing ASP it will do the following.

Connect to the database and execute the sp_AddProdDept stored procedure while passing the idDepartment and idProduct to the stored procedure.

Redirect the user back to the ManageProduct.asp so that they can continue managing the products.

Call the file ProdAddDept.asp.

<%@ Language=VBScript %>
<%
' ****************************************************
' ProdAddDept.asp - Adds a new product into a department.
' ****************************************************

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

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

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

dbProdDept.Open

' Execute the sp_AddProdDept stored procedure to
' tie together the product and the department
sql = "execute sp_AddProdDept " & _
request("idProduct") & ", " & _
request("idDepartment")

' Execute the statement
set rsProdDept = dbProdDept.Execute(sql)

' Send the user back to the manageproduct.asp page
Response.Redirect "ManageProduct.asp?idProduct=" & _
request("idProduct")

%>

You aren't likely to notice either ASP that much because they only run when a particular link on the ManageProduct.asp is selected. At the end of each ASP the product to department information is updated and the user is routed back to the ManageProduct.asp.

Stored Procedure	ASP Container
sp_DeleteProdDept	RemoveProdDept.asp
sp_AddProdDept	ProdAddDept.asp