Initializing the Page Structure

Introduction. First we need to develop the directory structure that you must use in your WildWillies web. It is very important that you follow this structure and spelling in order to get the code to work properly. It will also be very important to make certain you load your ASPs and images into the appropriate directories.

You need to have a WildWillies directory within your account and then these other subdirectories are contained within it.

Now we need to develop some ASPs that will likely be included on almost every other ASP page. Essentially we are going to create the Header.asp and Footer.asp. The header will contin universal navigation and imagery. The footer will contain the common ending for all of the pages. First we start with the Header.asp. In addition, we will create two stored procedures. to insert a shopper into the database if they are new, and retrieve their last shopping basket if they aren't new.

Stored procedures in SQL Server are essentially just that. For example, let's say you have a query you want to run over and over, possibly from different places in your web. You can CREATE a PROCEDURE, similar to CREATE TABLE, to store the procedure so that you can access it with a call. We will create two stored procedures, one called sp_InsertShopper, the other called sp_RetrieveLastBasket. Since these are required within the Header.asp we will develop these before we develop the Header.asp.

Finally, you need to get some images to be used on the header, cdlogo.gif and cdbullet.gif. These are contained in the following table. You should copy them into an images folder within your WildWillies directory. The WildWillies directory is where you will have most of the ASPs for the shopping experience.

Image Name	Image
cdlogo.gif
cdbullet.gif

The Stored Procedures. Now I will list out the two stored procedures that you should create using SQL in almost the same way we created the tables. The first file should be called sp_InsertShopper.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 procedure
strSQLCreate = "CREATE PROCEDURE sp_InsertShopper AS " & _
"insert into shopper(chrusername, chrpassword) values('', '') " & _
"select idShopper = @@identity"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "The SQL has executed"

%>

The second file should be called sp_RetrieveLastBasket.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 procedure
strSQLCreate = "CREATE PROCEDURE sp_RetrieveLastBasket @idShopper int AS " & _
"select * from basket where idShopper = @idShopper and intOrderPlaced =0 and intTotal = 0 order by dtCreated DESC"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "The SQL has executed"

%>

The Header.asp. The following Header.asp will appear on most of our pages in the store web. It consists of the images and navigation common at the top and left hand side of all of our pages. Make sure when you upload this ASP you put it in the Include subfolder of your WildWillies folder.

<%

' Check to see if the shopper session is 0. If so
' then we will need to create a shopper ID for tracking
' the shopper.
if CInt(session("idShopper")) = 0 then

' Next we look to see if a Wild Willie cookie
' has been set with the shopper ID.
if Request.Cookies("WWCD") = "" then

' Create an ADO database connection for the Shopper

set dbShopper = server.createobject("adodb.connection")

' Create a record set for the Shopper

set rsShopper = server.CreateObject("adodb.recordset")

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

dbShopper.Open

' Call the stored prodecure to insert a new
' shopper since there is no cookie.
sql = "execute sp_InsertShopper"

' Execute the SQL statement
set rsShopper = dbShopper.Execute(sql)

' Set the shopper ID in the session variable.
session("idShopper") = rsShopper("idShopper")

else

' Retrieve the shopper ID from the cookie
session("idShopper") = Request.Cookies("WWCD")

' Create an ADO database connection for the Shopping Basket
set dbShopperBasket = server.createobject("adodb.connection")

' Create a record set for the Shopping Basket
set rsShopperBasket = server.CreateObject("adodb.recordset")

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

dbShopperBasket.Open

' Retrieve the last basket that the shopper utilized. Note
' that only baskets that are not completed will be returned.
sql = "execute sp_RetrieveLastBasket " & session("idShopper")

' Execute the SQL statement
set rsShopperBasket = dbShopperBasket.Execute(sql)

' Check to see if a basket has been returned.
if rsShopperBasket.EOF <> true then

' Set the session ID of the basket

session("idBasket") = rsShopperBasket("idBasket")

end if

' Indicate that a profile has NOT been retrieved.
session("ProfileRetrieve") = "0"

end if

end if

%>


<body bgcolor="lightgoldenrodyellow" topmargin="0" leftmargin="0">


<table width="680" border="0">
<tr>
<td align="right" valign="center"><img src="images/cdlogo.gif"></td>
<td>
Wild Willie's CD Store</td>
<td align="right" valign="center"><img src="images/cdlogo.gif"></td>
</tr>
</table>

 

<hr width="680" align="left">


<table width="680" border="0">
<tr>

<td width="130" valign="top">
<img src="images/cdbullet.gif" border="0" align="center">

<a href="dept.asp">Departments</a>
 

<img src="images/cdbullet.gif" border="0" align="center">

<a href="basket.asp">Basket</a>
 

<img src="images/cdbullet.gif" border="0" align="center">

<a href="shipping.asp">Check Out</a>
 

<img src="images/cdbullet.gif" border="0" align="center">

<a href="profile.asp">Profile</a>
 

<img src="images/cdbullet.gif" border="0" align="center">

<a href="search.asp">Search</a>
 

<img src="images/cdbullet.gif" border="0" align="center">

<a href="OrderStatus.asp">Order Status</a>
 

</td>


<td width="10"> <td>


<td valign="top" width="540">

Finally, the common footer of all of the store pages needs to be called Footer.asp. Make sure when you upload this ASP you put it in the Include subfolder of your WildWillies folder.

</td>


</tr>


<tr>

<td colspan="4" width="680">
<HR>

Need help? Email <a href="mailto:support@wildwillieinc.com">support@wildwillieinc.com</a>
 

©Copyright 1999 Wild Willie Productions, Inc.
</td>
</tr>
</table>

This ends our section of code, ASPs and stored procedures that are common to all the pages in the store. Remember, we have many more pages for other people to use, like data administrators and market researchers.