Deleting Products in the Store Manager

 

Introduction.  Now we can get to our last set of relatively simple processing scripts.  This page will focus on deleting particular products that have been selected so they come up on the ManageDept.asp page.

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

 

Stored Procedure ASP Container
sp_DeleteDept DeleteDept.asp

 

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 a DELETE command in SQL to delete the appropriate department from the Department table.  The file should be called sp_DeleteDept.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_DeleteDept " & _
"@idDepartment int AS " & _
"delete from department where idDepartment = @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 DeleteDept.asp.  This is essentially a processing page to delete a particular department.  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.

  1. Execute the sp_DeleteDept stored procedure while passing the idDepartment to actually delete the product.
  2. Redirect the user to the ListDepts.asp so that they can continue managing the departments.

Call the file DeleteDept.asp.
<%@ Language=VBScript %>
<%
' ****************************************************
' DeleteDept.asp - Deletes the department from the store.
' ****************************************************

' 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

' The sp_DeleteDept stored procedure removes the
' department from the database.
sql = "execute sp_DeleteDept " & request("idDepartment")

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

' Send the user back to the list of departments.
Response.Redirect "ListDepts.asp"

%>

 

You aren't likely to notice this ASP that much because it only runs when a particular link on the ManageDept.asp is selected.  At this point the product is deleted and the user is routed back to the ListDepts.asp to see the other products in the store web.