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 ManageProduct.asp page.

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 a DELETE command in SQL to delete the appropriate product from the Products table. The file should be called sp_DeleteProduct.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_DeleteProduct " & _
"@idProduct int AS " & _
"delete from products where idProduct = @idProduct"

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 DeleteProduct.asp. This is essentially a processing page to delete a particular product. 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.

Execute the sp_DeleteProduct stored procedure while passing the idProduct to actually delete the product.

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

Call the file DeleteProduct.asp.

<%@ Language=VBScript %>
<%
' ****************************************************
' DeleteProduct.asp - Deletes the produce from the store.
' ****************************************************

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

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

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

dbProduct.Open

' Call the sp_DeleteProduct stored procedure to
' remove the product from the database.
sql = "execute sp_DeleteProduct " & request("idProduct")

' Execute the statement
set rsProduct = dbProduct.Execute(sql)

' Send the user back to the list of products.
Response.Redirect "ListProducts.asp"

%>

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

Stored Procedure	ASP Container
sp_DeleteProduct	DeleteProduct.asp