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 ManageDept.asp.

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

 

Stored Procedure ASP Container
sp_UpdateDepartment UpdateDept.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 department in the Department table.  The file should be called sp_UpdateDepartment.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_UpdateDepartment " & _
"@idDepartment int, " & _
"@chrDeptName varchar(255), " & _
"@txtDeptDesc text, " & _
"@chrDeptImage varchar(100) AS " & _
"update department set " & _
"chrDeptName = @chrDeptName, " & _
"txtDeptDesc = @txtDeptDesc, " & _
"chrDeptImage = @chrDeptImage " & _
"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 UpdateDept.asp.  This is essentially a processing page to get the modified inputs for the department and update them in the Department 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 ManageDept.asp
  2. Execute the sp_UpdateDepartment stored procedure while passing the idDepartment to actually determine the department.
  3. Redirect the user back to the ManageDept.asp so that they can continue managing the products.

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

' Retrieve the id of the department
idDepartment = request("idDepartment")

' Retrieve the department name and ensure that any
' single quotes are doubled.
chrDeptName = replace(request("chrDeptName"), "'", "''")

' Retrieve the department description and ensrue that 
' any single quotes are doubled.
txtDeptDesc = replace(request("txtDeptDesc"), "'", "''")

' Retrieve the name of the department image 
chrDeptImage = request("chrDeptImage")

' 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

' Execute the sp_UpdateDepartment stored procedure to 
' update the department data.
sql = "execute sp_UpdateDepartment " & _
request("idDepartment") & ", '" & _
chrDeptName & "', '" & _
txtDeptDesc & "', '" & _
chrDeptImage & "'"

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

' Send the user back to department manager and pass back 
' the department id.
Response.Redirect "ManageDept.asp?idDepartment=" & _
request("idDepartment")

%>

 

You aren't likely to notice this ASP that much because it only runs when a particular button on the ManageDept.asp is selected.  At this point the department is updated and the user is routed back to the ManageDept.asp.