Updating Products in the Store Manager

 

Introduction.  This script is associated with actually performing any updates to a product that have been entered in the form in ManageProduct.asp.

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

 

Stored Procedure ASP Container
sp_UpdateProduct UpdateProduct.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 an UPDATE command in SQL to update the fields for the appropriate product from the Products table.  The file should be called sp_UpdateProduct.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_UpdateProduct " & _
"@idProduct int, " & _
"@chrProductName varchar(255), " & _
"@txtDescription text, " & _
"@chrProductImage varchar(100), " & _
"@intPrice int, " & _
"@intActive int AS " & _
"UPDATE Products SET " & _
"chrProductName = @chrProductName, " & _
"txtDescription = @txtDescription, " & _
"chrProductImage = @chrProductImage, " & _
"intPrice = @intPrice, " & _
"intActive = @intActive " & _
"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 UpdateProduct.asp.  This is essentially a processing page to get the modified inputs for the product and update them in the Products 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. Retrieve the inputted information from the form in the appropriate section of ManageProduct.asp
  2. Execute the sp_UpdateProduct stored procedure while passing the idProduct to actually delete the product.
  3. Redirect the user back to the ManageProduct.asp so that they can continue managing the products.

Call the file UpdateProduct.asp.
<%@ Language=VBScript %>
<%
' ****************************************************
' UpdateProduct.asp - Handles updating the product data.
' ****************************************************

' Retrieve the product id
idProduct = request("idProduct")

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

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

' Retrieve the product image
chrProductImage = request("chrProductImage")

' Retrieve the product price and multiply by 100 to
' ensure it is stored as an integer.
intPrice = request("intPrice") * 100

' Retrieve the active status.
intActive = request("intActive")

' Check to see if the active check box was set.
if intActive = "" then

' If not then make the product inactive
intActive = 0

else

' If it is then set it to be active
intActive = 1

end if

' 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

' Execute the SQL stored procedure to update the product data
sql = "execute sp_UpdateProduct " & _
request("idProduct") & ", '" & _
chrProductName & "', '" & _
txtDescription & "', '" & _
chrProductImage & "', " & _
intPrice & ", " & _
intActive

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

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

%>

 

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 updated and the user is routed back to the ManageProduct.asp.