Updating, Deleting and Adding Shipping Features

 

Introduction.  We are now to our last ASPs for this semimester.  We are going to develop the code associated with updating, deleting and adding constraints and costs for shipping.
  1. Update upper and lower limits.
  2. Update costs
  3. Delete a constraint with limits and costs
  4. Add a constraint with limits and costs

The following table gives the thee ASPs and three stored procedures we will develop in this page.  All of these are essentially processing scripts that rely on the ManageShipping.asp for the interface.

 

Stored Procedure ASP Container
sp_UpdateShippingRate UpdateShipping.asp
sp_DeleteShippingRate DeleteShipping.asp
sp_InsertShippingRate AddShipping.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 an  UPDATE command in SQL to update the intLowQuantity, intHighQuantity and intFee for each record.  The file should be called sp_UpdateShippingRate.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_UpdateShippingRate " & _
"@idQuantityRange int, " & _
"@intLowQuantity int, " & _
"@intHighQuantity int, " & _
"@intFee int AS " & _
"update shipping set " & _
"intLowQuantity = @intLowQuantity, " & _
"intHighQuantity = @intHighQuantity, " & _
"intFee = @intFee " & _
"where idQuantityRange = @idQuantityRange"

connDeptProds.execute(strSQLCreate)

connDeptProds.Close
Set connDeptProds = Nothing

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

%>

 

 

The second stored procedure uses a DELETE command in SQL to delete a record for the appropriate idQuantityRange in the Shipping table.  The file should be called sp_DeleteShippingRate.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_DeleteShippingRate " & _
"@idQuantityRange int AS " & _
"delete from shipping where idQuantityRange = @idQuantityRange"

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 new data in the Shipping table.  The file should be called sp_InsertShippingRate.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_InsertShippingRate " & _
"@intLowQuantity int, " & _
"@intHighQuantity int, " & _
"@intFee int AS " & _
"insert into shipping(intLowQuantity, intHighQuantity, intFee) " & _
"values(@intLowQuantity, @intHighQuantity, @intFee)"

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 UpdateShipping.asp.  This is essentially a processing page to update the fee and range limits for the fee within the Shipping 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_UpdateShippingRate stored procedure while passing the idQuantityRange to actually update all of the records while looping through.
  2. Redirect the user back to the ManageShipping.asp so that they can continue managing the shipping.

Call the file UpdateShipping.asp.
<%@ Language=VBScript %>
<%
' ****************************************************
' UpdateShipping.asp - Handles updating the existing
' shipping rates.
' ****************************************************

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

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

dbShipUpdate.Open

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

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

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

dbShipping.Open

' Execute the sp_GetShippingRate to retrieve the shipping rates.
sql = "execute sp_GetShippingRate"

' Execute the statement
set rsShipping = dbShipping.Execute(sql)

' Loop through the shipping rates.
do until rsShipping.EOF

' Retrieve the low and high shipping quantities
' along with the shipping rate. On the
' ManageShipping.asp page, the field names are
' built with the id of the quantity range tacked
' onto the field name. We build the same naming
' here to retrieve the values.
intLow = request("intLow" & rsShipping("idQuantityRange"))
intHigh = request("intHigh" & rsShipping("idQuantityRange"))
intFee = request("intFee" & rsShipping("idQuantityRange"))

 ' Execute the update stored procedure to change the range.
dbShipUpdate.Execute "execute sp_UpdateShippingRate " & _
rsShipping("idQuantityRange") & ", " & _
intLow & ", " & _
intHigh & ", " & _
intFee

 ' Move to the next row
rsShipping.MoveNext 

' Loop back
loop

' Send the user back to the shipping manager page.
Response.Redirect "ManageShipping.asp"

%>

 

 

The DeleteShipping.asp.  This is essentially a processing page to delete a particular range set and fee from the Shipping 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_DeleteShippingRate stored procedure while passing the idQuantityRange to determine the record to be deleted.
  2. Redirect the user back to the ManageShipping.asp so that they can continue managing the products.

Call the file DeleteShipping.asp.
<%@ Language=VBScript %>
<%
' ****************************************************
' DeleteShipping.asp - Handles deleting the specific
' shipping range.
' ****************************************************

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

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

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

dbShipping.Open

' Execute the stored procedure to delete the specified shipping rate.
sql = "execute sp_DeleteShippingRate " & request("idQuantityRange")

' Execute the statement
set rsShipping = dbShipping.Execute(sql)

' Send the user back to the shipping manager
Response.Redirect "ManageShipping.asp"

%>

 

 

The AddShipping.asp.  This is essentially a processing page to add a range and fee to the Shipping 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_InsertShippingRate stored procedure to do the database processing.
  2. Redirect the user back to the ManageShipping.asp so that they can continue managing the products.

This ASP is a bit elaborate because of the rather unusual way he has set up the form and the possibilities for range sets.  I think it is overly restrictive and would make a great homework problem to improve upon this.  But you aren't likely to see such a problem because I actually believe you already have plenty to do!  Call the file AddShipping.asp.
<%@ Language=VBScript %>
<%

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

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

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

dbShipping.Open

intLow1 = request("intLow1")
intHigh1 = request("intHigh1")
intFee1 = request("intFee1")

if intFee1 <> "" then

dbShipping.Execute "execute sp_InsertShippingRate " & _
intLow1 & ", " & _
intHigh1 & ", " & _
intFee1

end if

intLow2 = request("intLow2")
intHigh2 = request("intHigh2")
intFee2 = request("intFee2")

if intFee2 <> "" then

dbShipping.Execute "execute sp_InsertShippingRate " & _
intLow2 & ", " & _
intHigh2 & ", " & _
intFee2

end if

intLow3 = request("intLow3")
intHigh3 = request("intHigh3")
intFee3 = request("intFee3")

if intFee3 <> "" then

dbShipping.Execute "execute sp_InsertShippingRate " & _
intLow3 & ", " & _
intHigh3 & ", " & _
intFee3

end if

Response.Redirect "ManageShipping.asp"

%>

 

You aren't likely to notice any of these ASPs that much because they only run when a particular link or button on the ManageShipping.asp is selected.  At the end of each ASP the information is updated and the user is routed back to the ManageShipping.asp.