Adding and Deleting Attributes for Products

 

Introduction.  We are now to our last ASPs associated with managing products.  We are going to develop the code associated with adding and deleting attributes
  1. Add an attribute to a product
  2. Delete an attribute from a product

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

 

Stored Procedure ASP Container
sp_DeleteProductAttribute DeleteAttribute.asp
sp_AddProductAttribute AddAttribute.asp

 

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 attribute from the ProductAttribute table based on the idProductAttribute.  The file should be called sp_DeleteProductAttribute.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_DeleteProductAttribute " & _
"@idProductAttribute int AS " & _
"delete from productattribute where idProductAttribute = @idProductAttribute"

connDeptProds.execute(strSQLCreate)

connDeptProds.Close
Set connDeptProds = Nothing

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

%>

 

 

The second stored procedure uses an INSERT command in SQL to add a record for the appropriate product in the ProductAttribute table.  The file should be called sp_AddProductAttribute.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_AddProductAttribute " & _
"@idAttribute int, " & _
"@idProduct int AS " & _
"insert into ProductAttribute(idAttribute, idProduct) " & _
"values(@idAttribute, @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 DeleteAttribute.asp.  This is essentially a processing page to remove a particular attribute association for a product from the ProductAttribute 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.

  1. Connect to the database and execute the sp_DeleteProductAttribute stored procedure while passing the idDepartmentProduct to actually delete the record.
  2. Redirect the user back to the ManageProduct.asp so that they can continue managing the products.

Call the file DeleteAttribute.asp.
<%@ Language=VBScript %>
<%
' ****************************************************
' DeleteAttribute.asp - Deletes an attribute assigned
' to a product.
' ****************************************************

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

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

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

dbProductAttribute.Open

' The SQL statement removes the product
' attribute setting for the specified product.
sql = "sp_DeleteProductAttribute " & request("idProductAttribute")

' Execute the statement
set rsProductAttribute = dbProductAttribute.Execute(sql)

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

%>

 

 

The AddAttribute.asp.  This is essentially a processing page to add a particular attribute association for a product in the ProductAttribute 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.

  1. Connect to the database and execute the sp_AddProductAttribute stored procedure while passing the idAttribute and idProduct to the stored procedure.
  2. Redirect the user back to the ManageProduct.asp so that they can continue managing the products.

Call the file AddAttribute.asp.
<%@ Language=VBScript %>
<%
' ****************************************************
' AddAttribute.asp - Adds the attribute setting for 
' the specified product.
' ****************************************************

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

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

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

dbProdAttr.Open

' Execute the sp_AddProductAttribute stored procedure to
' indicate the attribute is to be assigned to the product.
sql = "execute sp_AddProductAttribute " & _
request("idAttribute") & ", " & _
request("idProduct")

' Execute the statement
set rsProdAttr = dbProdAttr.Execute(sql)

' Send the user back to the product management 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.