Emptying the Shopping Basket


Introduction.  Now we need to develop an ASP that will likely be used for deleting all of the items from a shopping basket.  The file will be called EmptyBasket.asp.  This will be presented after developing stored procedures.

We will create one stored procedure to actually empty the basket called sp_ClearBasketItems.  The following table displays the name of the stored procedure and the ASP file in which it is called.


Stored Procedure ASP Container




The Stored Procedure.  Now I will list out the stored procedure that you should create using SQL in almost the same way we created the tables.  This file should be called sp_ClearBasketItems.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 connfoxFire, strSQLCreate
Set connfoxFire = Server.CreateObject("ADODB.Connection")
connfoxFire.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"


' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_ClearBasketItems @idBasket int AS " & _
"delete from basketitem where idBasket = @idBasket"


Set connfoxFire = Nothing

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



Like all of the other ASPs based on CREATE SQL commands, these files need to be uploaded to your web and then executed once.  After they have been executed, you should get an error if you try to execute them again because the stored procedures should already be there.  After these sp_name.asp files have been used they should be removed from your space on the server.



The DeleteItem.asp.  The following EmptyBasket.asp is called when the Empty Basket hyperlink is pressed.  This processing script will
  1. create a connection to the user's shopping basket
  2. Use the stored procedure sp_ClearBasketItems based on the idBasket and delete everything.
  3. Return you to the Basket.asp for display and further operations.
<%@ Language=VBScript %>
' ****************************************************
' EmptyBasket.asp - This empties the items in the 
' basket.
' ****************************************************

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

' Create a record set
set rsBasketItem = server.CreateObject("adodb.recordset")

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

' Call the clear basket items stored procedure
sql = "execute sp_ClearBasketItems " & session("idBasket")

' Execute the SQL statement to empty the basket 
set rsBasketItem = dbBasketItem.Execute(sql)

' Send the user to the basket page
Response.Redirect "basket.asp"



Notice that this ASP is essentially purely a processing script.  There isn't any HTML associated with the display.  After the results are developed, the Basket.asp is called to display them.