Shipping During the Check Out Process

 

Introduction.  Now we need to develop an ASP that will be used for shipping.  The file will be called Shipping.asp.  This will be presented after discussing stored procedures.

We will need one stored procedure to retrieve a previously developed profile for the shopper.  The name of the stored procedure is sp_RetrieveProfileByID.  The following table displays the name of the stored procedure and the ASP file in which it is called.

 

Stored Procedure ASP Container

sp_RetrieveProfileByID

 Shipping.asp

 

The Stored Procedure.  Now I will list out the stored procedure that you should create using SQL and ASPs pretty much exactly like we have for the other stored procedures.  This file should be called sp_RetrieveProfileByID.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"

connfoxFire.Open

' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_RetrieveProfileByID @idShopper int AS " & _
"select * from shopper where idShopper = @idShopper"

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 Shipping.asp.  Since we have just created the stored procedure required for this ASP we can now make use of it.  The following Shipping.asp will 
  1. Check to make sure there is a shopping basket and redirect the user if it is missing.
  2. Check to make see if a profile has already been retrieved.  We need to be careful about overwriting updates in the profile.  If there is no profile then provide a link to the Profile.asp.
  3. Check to see if the ProfileRetrieve session variable doesn't equal 1.  It will be set to 0 in the Header.asp based on a cookie if the profile is retrieved.
    1. If it isn't 1 then sp_RetrieveProfileByID will retrieve the profile based on the ID of the shopper.
  4. Once the profile is retrieved, the values are read into appropriate session variables
  5. Next we check to see if we are back at our Shipping.asp due to there having been an error due to inappropriate information when validating at ValidateShipping.asp.  If there is an error then an appropriate message is given to provide feedback to the shopper about what they need to do.  Entries in the controls ar edefaulted to whatever is in the session variables so that the shopper doesn't need to start filling out the form from scratch.
  6. In order to go back to the appropriate values in something like the state option box we need quite a bit of code to check what was previously entered.
  7. Then we allow the shopper to select an option for the country and an option for their province if appropriate.
  8. The remaining form displays the rest of the input controls and defaults them to previously entered values.
<%@ Language=VBScript %>
<%
' ****************************************************
' Shipping.asp - Provides for the user to enter in
' shipping data for the store. It will check to
' see if a profile can be retrieved.
' ****************************************************

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

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

end if
%>

<HTML>

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

<BR>
<center>
<font size="5"><b>Shipping Information</b></font>
</center>

<%

' Check to see if a profile has been retrieved or not. 
if session("ProfileRetrieve") = "" then

' If not then we will create a link to retrieve an existing profile.
%>
<BR>
<B><i>Click <a href="profile.asp">here</a> 
to retrieve an existing profile.</i></b>
<BR>

<%

else

' Check to see if the ProfileRetrieve value is 1. If not then the profile will be retrieved if possible. 
' Note the ProfileRetrieve value will be 0 when a new shopper ID is created from the Header.
if session("ProfileRetrieve") <> "1" then

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

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

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

dbProfile.Open

 ' Build the SQL stored procedure to retrieve the profile based on the id of the shopper 
' (which would be retrieved by the cookie).
sql = "execute sp_RetrieveProfileByID " & session("idShopper")

 ' Execute the statement
set rsProfile = dbProfile.Execute(sql)

 ' Set the shipping variables
session("chrShipFirstName") = rsProfile("chrFirstName")
session("chrShipLastName") = rsProfile("chrLastName")
session("chrShipAddress") = rsProfile("chrAddress")
session("chrShipCity") = rsProfile("chrCity")
session("chrShipState") = rsProfile("chrState")
session("chrShipProvince") = rsProfile("chrProvince")
session("chrShipCountry") = rsProfile("chrCountry")
session("chrShipZipCode") = rsProfile("chrZipCode")
session("chrShipPhone") = rsProfile("chrPhone")
session("chrShipEmail") = rsProfile("chrEmail")
session("chrPassword") = rsProfile("chrPassword")
session("intCookie") = rsProfile("intCookie")

end if

 ' Set the session so we don't retrieve the data again.
' This will ensure we don't pull the profile again.
session("ProfileRetrieve") = "1"

end if

' Check to see if an error was returned if the form was submitted
if session("Error") <> "" then

' Show the error data.
%>
<BR>
<b>You have an error in your shipping form, please correct the data:</b><BR><BR>

<!-- Build out a table to display the error. -->
<table>
<tr>
<td width="70">&nbsp;</td>
<td><i><%=Response.Write(session("Error"))%></i></td>
</tr>
</table>
<BR><BR>
<%

' Clear the error variable.
session("Error") = ""

else

%>

<BR>
<b>Enter your shipping address:</b>
<BR><BR>

<%

end if

%>

<!-- This form will post to the validate page to ensure the shipping data has been entered properly. -->
<center>

<!-- Form to post the shipping data. -->
<form method="post" action="ValidateShipping.asp">

<!-- The table will display the shipping form. Note that the values will be defaulted to any session variables. -->
<table>
<!-- Shipping First Name -->
<tr>
<td align="right">First Name:</td>
<td><input type="text" value="<%=session("chrShipFirstName")%>" 
name="chrShipFirstName" size="30"></td>
</tr>
<!-- Shipping Last Name -->
<tr>
<td align="right">Last Name:</td>
<td><input type="text" value="<%=session("chrShipLastName")%>" 
name="chrShipLastName" size="30"></td>
</tr>
<!-- Shipping Address -->
<tr>
<td align="right">Address:</td>
<td><input type="text" value="<%=session("chrShipAddress")%>" 
name="chrShipAddress" size="30"></td>
</tr>
<!-- Shipping City -->
<tr>
<td align="right">City:</td>
<td><input type="text" value="<%=session("chrShipCity")%>" 
name="chrShipCity" size="30"></td>
</tr>
<!-- Shipping City -->
<tr>
<td align="right">State:</td>
<td>

<% 

 ' Check to see which state was selected previously if there was an error. 
' We then set a variable to put in the appropriate 'SELECTED' HTML tag.
if session("chrShipState") = "AL" then 
SelAL = "Selected"
end if

if session("chrShipState") = "AK" then 
SelAK = "Selected"
end if

if session("chrShipState") = "AZ" then 
SelAZ = "Selected"
end if

if session("chrShipState") = "AR" then 
SelAR = "Selected"
end if

if session("chrShipState") = "CA" then 
SelCA = "Selected"
end if

if session("chrShipState") = "CT" then 
SelCT = "Selected"
end if

if session("chrShipState") = "CO" then 
SelCO = "Selected"
end if

if session("chrShipState") = "DC" then 
SelDC = "Selected"
end if

if session("chrShipState") = "DE" then 
SelDE = "Selected"
end if

if session("chrShipState") = "FL" then 
SelFL = "Selected"
end if

if session("chrShipState") = "GA" then 
SelGA = "Selected"
end if

if session("chrShipState") = "HI" then 
SelHI = "Selected"
end if

if session("chrShipState") = "ID" then 
SelID = "Selected"
end if

if session("chrShipState") = "IL" then 
SelIL = "Selected"
end if

if session("chrShipState") = "IN" then 
SelIN = "Selected"
end if

if session("chrShipState") = "IA" then 
SelIA = "Selected"
end if

if session("chrShipState") = "KS" then 
SelKS = "Selected"
end if

if session("chrShipState") = "KY" then 
SelKY = "Selected"
end if

if session("chrShipState") = "LA" then 
SelLA = "Selected"
end if

if session("chrShipState") = "ME" then 
SelME = "Selected"
end if

if session("chrShipState") = "MA" then 
SelMA = "Selected"
end if

if session("chrShipState") = "MD" then 
SelMD = "Selected"
end if

if session("chrShipState") = "MI" then 
SelMI = "Selected"
end if

if session("chrShipState") = "MN" then 
SelMN = "Selected"
end if

if session("chrShipState") = "MS" then 
SelMS = "Selected"
end if

if session("chrShipState") = "MO" then 
SelMO = "Selected"
end if

if session("chrShipState") = "MT" then 
SelMT = "Selected"
end if

if session("chrShipState") = "NE" then 
SelNE = "Selected"
end if

if session("chrShipState") = "NV" then 
SelNV = "Selected"
end if

if session("chrShipState") = "NH" then 
SelNH = "Selected"
end if

if session("chrShipState") = "NJ" then 
SelNJ = "Selected"
end if

if session("chrShipState") = "NM" then 
SelNM = "Selected"
end if

if session("chrShipState") = "NY" then 
SelNY = "Selected"
end if

if session("chrShipState") = "NC" then 
SelNC = "Selected"
end if

if session("chrShipState") = "ND" then 
SelND = "Selected"
end if

if session("chrShipState") = "OH" then 
SelOH = "Selected"
end if

if session("chrShipState") = "OK" then 
SelOK = "Selected"
end if

if session("chrShipState") = "OR" then 
SelOR = "Selected"
end if

if session("chrShipState") = "PA" then 
SelPA = "Selected"
end if

if session("chrShipState") = "RI" then 
SelRI = "Selected"
end if

if session("chrShipState") = "SC" then 
SelSC = "Selected"
end if

if session("chrShipState") = "SD" then 
SelSD = "Selected"
end if

if session("chrShipState") = "TN" then 
SelTN = "Selected"
end if

if session("chrShipState") = "TX" then 
SelTX = "Selected"
end if

if session("chrShipState") = "UT" then 
SelUT = "Selected"
end if

if session("chrShipState") = "VT" then 
SelVT = "Selected"
end if

if session("chrShipState") = "VA" then 
SelVA = "Selected"
end if

if session("chrShipState") = "WY" then 
SelWY = "Selected"
end if

if session("chrShipState") = "WI" then 
SelWI = "Selected"
end if

if session("chrShipState") = "WV" then 
SelWV = "Selected"
end if

if session("chrShipState") = "WA" then 
SelWA = "Selected"
end if

if session("chrShipState") = "FSO" then 
SelFSO = "Selected"
end if

%>

 <!-- Option box for the shipping states. Note that the past default will be selected -->
<select name="chrShipState">
<option value="">Select a State
<option value="AL" <%=SelAL%>>Alabama
<option value="AK" <%=SelAK%>>Alaska
<option value="AZ" <%=SelAZ%>>Arizona
<option value="AR" <%=SelAR%>>Arkansas
<option value="CA" <%=SelCA%>>California
<option value="CT" <%=SelCT%>>Connecticut
<option value="CO" <%=SelCO%>>Colorado
<option value="DC" <%=SelDC%>>D.C.
<option value="DE" <%=SelDE%>>Delaware
<option value="FL" <%=SelFL%>>Florida
<option value="GA" <%=SelGA%>>eorgia
<option value="HI" <%=SelHI%>>Hawaii
<option value="ID" <%=SelID%>>Idaho
<option value="IL" <%=SelIL%>>Illinois
<option value="IN" <%=SelIN%>>Indiana
<option value="IA" <%=SelIA%>>Iowa
<option value="KS" <%=SelKS%>>Kansas
<option value="KY" <%=SelKY%>>Kentucky
<option value="LA" <%=SelLA%>>Louisiana
<option value="ME" <%=SelME%>>Maine
<option value="MA" <%=SelMA%>>Massachusetts
<option value="MD" <%=SelMD%>>Maryland
<option value="MI" <%=SelMI%>>Michigan
<option value="MN" <%=SelMN%>>Minnesota
<option value="MS" <%=SelMS%>>Mississippi
<option value="MO" <%=SelMO%>>Missouri
<option value="MT" <%=SelMT%>>Montana
<option value="NE" <%=SelNE%>>Nebraska
<option value="NV" <%=SelNV%>>Nevada
<option value="NH" <%=SelNH%>>New Hampshire
<option value="NJ" <%=SelNJ%>>New Jersey
<option value="NM" <%=SelNM%>>New Mexico
<option value="NY" <%=SelNY%>>New York
<option value="NC" <%=SelNC%>>North Carolina
<option value="ND" <%=SelND%>>North Dakota
<option value="OH" <%=SelOH%>>Ohio
<option value="OK" <%=SelOK%>>Oklahoma
<option value="OR" <%=SelOR%>>Oregon
<option value="PA" <%=SelPA%>>Pennsylvania
<option value="RI" <%=SelRI%>>Rhode Island
<option value="SC" <%=SelSC%>>South Carolina
<option value="SD" <%=SelSD%>>South Dakota
<option value="TN" <%=SelTN%>>Tennessee
<option value="TX" <%=SelTX%>>Texas
<option value="UT" <%=SelUT%>>Utah
<option value="VT" <%=SelVT%>>Vermont
<option value="VA" <%=SelVA%>>Virginia
<option value="WA" <%=SelWA%>>Washington
<option value="WY" <%=SelWY%>>Wyoming
<option value="WI" <%=SelWI%>>Wisconsin
<option value="WV" <%=SelWV%>>West Virginia
<OPTION value="FSO" <%=SelFSO%>>Military Stuff
</select>

 <!-- Province option -->
or Province:<input type="text" 
value="<%=trim(session("chrShipProvince"))%>" 
name="chrShipProvince" size="15">

</td>
</tr>
<!-- Shipping Country -->
<tr>
<td align="right">Country:</td>
<td>

<% 
 ' Check to see which country was selected previously if there was an error. 
' We then set a variable to set the 'Selected' tag.
if session("chrShipCountry") = "US" then 
SelUS = "Selected"
end if

if session("chrShipCountry") = "CA" then 
SelCA = "Selected"
end if

if session("chrShipCountry") = "MX" then 
SelMX = "Selected"
end if

%>

 <!-- Option box for the country with the past country defaulted -->
<select name="chrShipCountry">
<option value="">Select a Country
<option value="US" <%=SelUS%>>United States
<option value="CA" <%=SelCA%>>Canada
<option value="MX" <%=SelMX%>>Mexico
</select> 
</td>
</tr>
<!-- Shipping Zip Code or Postal Code -->
<tr>
<td align="right">Zip/Postal Code:</td>
<td><input type="text" value="<%=session("chrShipZipCode")%>" 
name="chrShipZipCode" size="30"></td>
</tr>
<!-- Shipping Phone -->
<tr>
<td align="right">Phone:</td>
<td><input type="text" value="<%=session("chrShipPhone")%>" 
name="chrShipPhone" size="30"></td>
</tr>
<!-- Shipping Email -->
<tr>
<td align="right">Email:</td>
<td><input type="text" value="<%=session("chrShipEmail")%>" 
name="chrShipEmail" size="30"></td>
</tr>
<!-- Submit Button -->
<tr>
<td colspan="2" align="center">
<input type="Submit" value="Submit" name="Submit">
</td>
</tr>
</table>

</form>

</center>

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

</BODY>
</HTML>

 

This ASP is one of the longest we have developed so far.  While the logic isn't that complicated, there is a lot of HTML code to make sure things are available in the form and to check whether controls have been filled in appropriately.