Adding Items to the Shopping Basket

Introduction. Now we need to develop an ASPs that will likely be used for adding items to a shopping basket. The file will be called AddItem.asp. This will be presented after developing stored procedures.

We will create four stored procedures to create the shopping basket, insert a basket item, check the quantity for an item, and update the item quantity. The following table displays the name of the stored procedure and the ASP file in which it is called.

Stored Procedure

ASP Container

sp_CreateBasket

sp_InsertBasketItem

sp_CheckBasketItemQuantity

sp_UpdateBasketItemsQuantity

AddItem.asp

The Stored Procedures. Now I will list out the stored procedures that you should create using SQL in almost the same way we created the tables. This file should be called sp_CreateBasket.asp.

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' 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"

connfoxFire.Open

' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_CreateBasket @idShopper int AS " & _
" insert into basket(idShopper) values(@idShopper) " & _
"select idbasket = @@identity"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "The SQL has executed"

%>

This next file should be called sp_InsertBasketItem.asp.

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' 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"

connfoxFire.Open

' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_InsertBasketItem " & _
"@idBasket int, @intQuantity int,@intPrice int,@chrName varchar(255), " & _
"@idProduct int,@chrSize varchar(50),@chrColor varchar(50) AS " & _
"insert into basketitem(idBasket, intQuantity, intPrice, chrName, idProduct, chrSize, chrColor) " & _
" values(@idBasket, @intQuantity, @intPrice, @chrName, @idProduct, @chrSize, @chrColor)"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "The SQL has executed"

%>

This next file should be called sp_CheckBasketItemQuantity.asp.

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' 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"

connfoxFire.Open

' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_CheckBasketItemQuantity @idProduct int, @idBasket int AS " & _
"select intQuantity from basketitem where idProduct = @idProduct and idBasket = @idBasket"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "The SQL has executed"

%>

This next file should be called sp_UpdateBasketItemsQuantity.asp.

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' 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"

connfoxFire.Open

' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_UpdateBasketItemsQuantity " & _
"@idBasket int, @intQuantity int,@idProduct int AS " & _
"update basketitem set intQuantity = @intQuantity " & _
"where idBasket = @idbasket and idProduct = @idProduct"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "The SQL has executed"

%>

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 AddItem.asp. The following AddItem.asp will

assess shopper ID

make sure quantity of item isn't zero

if makes it through this then retrieve all relevant information such as shopper ID and product information

check to make sure a basket has been created

if not then create it using sp_CreateBasket and connect to it

if it's there then connect and check some stats using sp_CheckBasketItemQuantity

sp_CheckBasketItemQuantity and sp_UpdateBasketItemsQuantity are used along with other things to update the basket and call the display ASP.

<%@ Language=VBScript %>
<%
' ****************************************************
' AddItem.asp - This page is utlized to add a selected
' product into the shopping basket.
' ****************************************************

' Check to see if a shopper variable has been set. If not then default to 0.
if session("idShopper") = "" then

' Default to 0
session("idShopper") = 0

end if

' Check to ensure that the quantity is not 0.
if request("quantity") = "0" then

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

End If

' Retrieve the quantity
intQuantity = request("quantity")

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

' Retrieve the product name
chrName = replace(request("productname"), "'", "''")

' Retrieve the product price
intPrice = request("productprice")

' Retrieve the size
chrSize = request("size")

' Retrieve the color
chrColor = request("color")

' Check to see if a basket has been created
if session("idBasket") = "" then

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

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

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

dbBasket.Open

' Execute the create basket stored procedure to create a new basket for the shopper
sql = "execute sp_CreateBasket " & session("idShopper")

' Execute the SQL statement
set rsBasket = dbBasket.Execute(sql)

' Retrieve the id of the basket returned from the insert
idBasket = rsBasket("idBasket")

' Set the basket id in the session variable
session("idBasket") = idBasket

else

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

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

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

dbBasket.Open

' Call the stored procedure to check the basket item quantity. The id of the product to check
' and the ID of the basket is sent in.
sql = "execute sp_CheckBasketItemQuantity " & idProduct & ", " & session("idBasket")

' Execute the SQL statement
set rsBasket = dbBasket.Execute(sql)

' Check to see if the item is in the basket. If it is then we are going to update the quantity
' instead of updating it.
if rsbasket.eof = false then

' If so, then we are going to update the basket quantity instead of adding a new item.
sql = "execute sp_UpdateBasketItemsQuantity " & _
session("idBasket") & ", " & _
intQuantity + rsBasket("intQuantity") & ", " & _
idProduct

' Execute the SQL statement
set rsBasket = dbBasket.Execute(sql)

' Go to the basket
Response.Redirect "basket.asp"

end if

end if

' 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
dbBasketItem.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=WildWillies;UID=cis; PWD=csatqu"

dbBasketItem.Open

' Call the stored procedure to insert the new item into the basket.
sql = "execute sp_InsertBasketItem " & _
session("idBasket") & ", " & _
intQuantity & ", " & _
intPrice & ", '" & _
chrName & "', " & _
idProduct & ", '"& _
chrSize & "', '" & _
chrColor & "'"

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

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

%>