Displaying the Shopping Basket

 

Introduction.  Now we need to develop an ASP that will be used for displaying the  items in a shopping basket.  The file will be called Basket.asp.  This will be presented after developing stored procedures.

We will need two stored procedures to display the shopping basket.  One of them we have already developed, sp_CreateBasket.  The other, sp_RetrieveBasketItem, is new and this page contains the code for creating it.  The following table displays the name of the stored procedures and the ASP file in which it is called.

 

Stored Procedure ASP Container
sp_CreateBasket

sp_RetrieveBasketItem

 Basket.asp

 

 

The Stored Procedures.  Now I will list out the stored procedure that you should create using SQL in almost the same way we created the tables.  The sp_CreateBasket was developed in the previous web page so ou do not need to create it again.  This is one of the advantages of using stored procedures, since once it is created you can use it in other places.  Another advantage is that stored procedures are retained within SQL Server as a sort of compiled code so that they execute more quickly and don't need to be recompiled each time they are used.

This file should be called sp_RetrieveBasketItem.asp.  The name is a bit misleading since it actually retrieves all of the items in a shopping basket identified by its idBasket.
<%@ 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"

connfoxFire.Open

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

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
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 Basket.asp.  The following Basket.asp will 
  1. assess idBasket and
    1. if it is blank then use sp_CreateBasket to create the basket and continue
  2. use the sp_RetrieveBasketItem to retrieve the shopper's basket
  3. work to display all the items in the basket by building a table with a new row for each item and a column for each important piece or group of information about the product.
  4. calculate the subtotals for each item and the subtotal for the entire order.
  5. display the rest of the options associated with the basket.
<%@ Language=VBScript %>
<HTML>
<!-- Basket.asp - Displays the items in the shoppers basket. -->

<!-- #include file="include/header.asp" -->

<!-- This form will allow the user to update the quantity of the items in the basket -->
<form method="post" action="UpdateBasket.asp">

<%

' Check to see if the ID of the basket is blank
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

 ' Create a new shopping basket
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

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

' Execute the stored procedure to retrieve the basket items for the shopper.
sql = "execute sp_RetrieveBasketItem " & session("idBasket")

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

' Check to ensure a basket has been created and that there are items in the basket
if session("idBasket") = "" or rsBasketItem.EOF = true then

%>

 <!-- Show the empty basket message -->
<center>
<BR><BR>
<font size="4">Your basket is empty.</font>
</center>

<% 

else 

' Check to see if the last department is set based on where they ordered from. 
if session("LastIDDept") <> "" then 

%>

<!-- Show the link to go back to the department to make navigation easier. -->
<BR>
Click <a href="products.asp?idDept=<%=session("LastIDDept")%>">
here</a> to continue shopping.
<BR><BR>

<%end if%>

<!-- Build the basket table -->
<table border="0" cellpadding="3" cellspacing="2" width="500">

<!-- Build the header row -->
<tr>
<th>Item Code</th>
<th>Name</th>
<th>Attributes</th>
<th>Quantity</th>
<th>Price</th>
<th>Total</th>
<th>Remove</th>
</tr>

<%

' Loop through the basket items.
do until rsBasketItem.EOF

%>

<!-- Show the row -->
<tr>
 <!-- Show the product id -->
<td align="center"><%=rsBasketItem("idProduct")%></td>
 <!-- Show the product name -->
<td><%=rsBasketItem("chrName")%></td>
 <!-- Show the product attributes -->
<td>

<% if rsBasketItem("chrColor") <> "" then %>

<%=rsBasketItem("chrSize")%>, 
<%=rsBasketItem("chrColor")%>

<% end if %>

</td>
 <!-- Show the product quantity -->
<td align="center">
<input type="text" value="<%=rsBasketItem("intQuantity")%>" 
name="<%=rsBasketItem("idProduct")%>" size="2">
</td>
 <!-- Show the product price -->
<td><%=formatcurrency(rsBasketItem("intPrice")/100, 2)%></td>

 <!-- Show the product total cost -->
<td>
<%=formatcurrency(rsBasketItem("intPrice")/100 * rsBasketitem("intQuantity"), 2)%>
</td>
 <!-- Show the remove option. -->
<td>
<a href="deleteitem.asp?idBasketItem=<%=rsBasketItem("idBasketItem")%>">
Remove</a>
</td>
 <!-- Continue to calculate the subtotal -->
<% subtotal = subtotal + (rsBasketItem("intPrice") * rsBasketitem("intQuantity")) %>

</tr>

<%

' Move to the next row
rsBasketItem.MoveNext

' Loop back
loop

%>

<!-- Build a break -->
<tr>
<td colspan="7"><HR></td>
</tr>

<!-- Show the sub total of the basket -->
<tr>
<td colspan="5" align="right"><b>Subtotal:</b></td>
<td><% = formatcurrency(subtotal/100, 2) %></td>
<td>&nbsp;</td>
</tr>

</table>

<!-- Show the submit button for the quantity update action -->
<table width="100%">

<td><input type="submit" value="Update Basket" name="Submit"></td>

</form>

<!-- Show the empty basket and check out links -->
<td><a href="emptybasket.asp">Empty Basket</a></td>
<td><a href="shipping.asp">Check Out</a></td>

</tr>

</table>

<% end if %>

<!-- #include file="include/footer.asp" -->

</BODY>
</HTML>

 

Notice that this ASP page has links to other pages that we have yet to create, such as Shipping.asp or EmptyBasket.asp.