Profile Check In

 

Introduction.  Now we need to develop an ASP that will be used so that shoppers who have previously created a profile can re-access it.  The file will be called ProfileDisplay.asp.  This ASP will make use of one stored procedure that actually retrieves the profile based on the e-mail address and password called sp_RetrieveProfile.

First we will develop the stored procedure then the ASP.

 

The Stored Procedure.  This file should be called sp_RetrieveProfile.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_RetrieveProfile @email varchar(255), @password varchar(25) AS " & _
"select * from shopper where chrEmail = @email and chrPassword = @Password"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

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

%>

 

As usual with any sp_filename.asp, you should upload the file, execute it once to create the stored procedure, then delete it from your root web.  Though you are likely to want to save it on your development source computer for future reference and possible reuse.

 

The ProfileDisplay.asp.  This ASP is used to actually display the profile.  It can be modified from here orthis is the processing script that might send the user to the previously developed EmailPassword.asp. The following ProfileDisplay.asp will 
  1. Open the database connection.
  2. Needs to determine why the shopper is accessing the page.
    1. First time user - get values from form
    2. An error in the profile update - get values from session variables
  3. Try to obtain a profile and based on whether one was returned.
    1. Nothing returned - notify user - allow them to get password via e-mail.
    2. Profile returned then display it in a form
  4. Typical form processing we've done for the profile in other places with name, address, phone and e-mail validity to consider.
  5. Give option to submit what is displayed in the profile form for update.

It is interesting to note that this script doesn't adequately handle a poorly entered e-mail address or a non-existent e-mail address.  This sounds like a good homework exercise!

<%@ Language=VBScript %>
<HTML>
<!-- ProfileDisplay.asp - Displays the shoppers profile. -->

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

<%

' 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

' Check to see if we have a parameter on the URL.
' Parameters will be on the URL if we are sending
' the shopper back because of errors in the profile.
' If so, then the username and password will be 
' retrieved from session variables.
if request("check") = "1" then

' Retrieve the values
email = session("email")
password = session("password")

else

' Otherwise we retrieve the values from the profile form.
email = request("email")
password = request("password")

end if

' Build the SQL stored procedure to retrieve the profile
' based on the email and password
sql = "execute sp_RetrieveProfile '" & email & _
"', '" & password & "'"

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

' Check to see if a profile was returned
if rsProfile.EOF then

%>

<!-- If nothing is returned then we notify the user -->
Sorry, that email and password is incorrect. 
Click <a href="profile.asp">here</a> to try again.<BR><BR>

If you have forgotten your password, enter in your email 
address to have your password emailed to you.<BR><BR>

<!-- Display a form that will email the password to the user if it has been forgotten -->
<form method="post" action="emailpassword.asp">

Email Address: <input type="text" value="" name="email"><BR><BR>

<input type="submit" value="Submit" name="submit">

</form>

<%

else

' Set the shopper value so the profile can be later retrieved.
session("idShopper") = rsProfile("idShopper")

' Set the profile retrieve to 0.
session("ProfileRetrieve") = "0"

%>

<!-- Display the profile. -->
<B>Edit your profile below:</b><BR><BR>

<!-- Form to post the changes -->
<form method="post" action="UpdateProfile.asp">

<!-- Table to display the profile data -->
<table>
<!-- First Name -->
<tr>
<td align="right">First Name:</td>
<td>
<input type="text" value="<%=rsProfile("chrFirstName")%>" 
name="chrFirstName">
</td>
</tr>
<!-- Last Name -->
<tr>
<td align="right">Last Name:</td>
<td>
<input type="text" value="<%=rsProfile("chrLastName")%>" 
name="chrLastName">
</td>
</tr>
<!-- Address -->
<tr>
<td align="right">Address:</td>
<td>
<input type="text" value="<%=rsProfile("chrAddress")%>" 
name="chrAddress">
</td>
</tr>
<!-- City -->
<tr>
<td align="right">City:</td>
<td>
<input type="text" value="<%=rsProfile("chrCity")%>" 
name="chrCity">
</td>
</tr>

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

<% 

 ' Check to see which state was selected previously if there was an error.
if rsProfile("chrState") = "AL" then 
SelAL = "Selected"
end if

if rsProfile("chrState") = "AK" then 
SelAK = "Selected"
end if

if rsProfile("chrState") = "AZ" then 
SelAZ = "Selected"
end if

if rsProfile("chrState") = "AR" then 
SelAR = "Selected"
end if

if rsProfile("chrState") = "CA" then 
SelCA = "Selected"
end if

if rsProfile("chrState") = "CT" then 
SelCT = "Selected"
end if

if rsProfile("chrState") = "CO" then 
SelCO = "Selected"
end if

if rsProfile("chrState") = "DC" then 
SelDC = "Selected"
end if

if rsProfile("chrState") = "DE" then 
SelDE = "Selected"
end if

if rsProfile("chrState") = "FL" then 
SelFL = "Selected"
end if

if rsProfile("chrState") = "GA" then 
SelGA = "Selected"
end if

if rsProfile("chrState") = "HI" then 
SelHI = "Selected"
end if

if rsProfile("chrState") = "ID" then 
SelID = "Selected"
end if

if rsProfile("chrState") = "IL" then 
SelIL = "Selected"
end if

if rsProfile("chrState") = "IN" then 
SelIN = "Selected"
end if

if rsProfile("chrState") = "IA" then 
SelIA = "Selected"
end if

if rsProfile("chrState") = "KS" then 
SelKS = "Selected"
end if

if rsProfile("chrState") = "KY" then 
SelKY = "Selected"
end if

if rsProfile("chrState") = "LA" then 
SelLA = "Selected"
end if

if rsProfile("chrState") = "ME" then 
SelME = "Selected"
end if

if rsProfile("chrState") = "MA" then 
SelMA = "Selected"
end if

if rsProfile("chrState") = "MD" then 
SelMD = "Selected"
end if

if rsProfile("chrState") = "MI" then 
SelMI = "Selected"
end if

if rsProfile("chrState") = "MN" then 
SelMN = "Selected"
end if

if rsProfile("chrState") = "MS" then 
SelMS = "Selected"
end if

if rsProfile("chrState") = "MO" then 
SelMO = "Selected"
end if

if rsProfile("chrState") = "MT" then 
SelMT = "Selected"
end if

if rsProfile("chrState") = "NE" then 
SelNE = "Selected"
end if

if rsProfile("chrState") = "NV" then 
SelNV = "Selected"
end if

if rsProfile("chrState") = "NH" then 
SelNH = "Selected"
end if

if rsProfile("chrState") = "NJ" then 
SelNJ = "Selected"
end if

if rsProfile("chrState") = "NM" then 
SelNM = "Selected"
end if

if rsProfile("chrState") = "NY" then 
SelNY = "Selected"
end if

if rsProfile("chrState") = "NC" then 
SelNC = "Selected"
end if

if rsProfile("chrState") = "ND" then 
SelND = "Selected"
end if

if rsProfile("chrState") = "OH" then 
SelOH = "Selected"
end if

if rsProfile("chrState") = "OK" then 
SelOK = "Selected"
end if

if rsProfile("chrState") = "OR" then 
SelOR = "Selected"
end if

if rsProfile("chrState") = "PA" then 
SelPA = "Selected"
end if

if rsProfile("chrState") = "RI" then 
SelRI = "Selected"
end if

if rsProfile("chrState") = "SC" then 
SelSC = "Selected"
end if

if rsProfile("chrState") = "SD" then 
SelSD = "Selected"
end if

if rsProfile("chrState") = "TN" then 
SelTN = "Selected"
end if

if rsProfile("chrState") = "TX" then 
SelTX = "Selected"
end if

if rsProfile("chrState") = "UT" then 
SelUT = "Selected"
end if

if rsProfile("chrState") = "VT" then 
SelVT = "Selected"
end if

if rsProfile("chrState") = "VA" then 
SelVA = "Selected"
end if

if rsProfile("chrState") = "WY" then 
SelWY = "Selected"
end if

if rsProfile("chrState") = "WI" then 
SelWI = "Selected"
end if

if rsProfile("chrState") = "WV" then 
SelWV = "Selected"
end if

if rsProfile("chrState") = "WA" then 
SelWA = "Selected"
end if

if rsProfile("chrState") = "FSO" then 
SelFSO = "Selected"
end if

%>

 <!-- State select box. -->
<select name="chrState">
<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 input -->
or Province:<input type="text" value="<%=rsProfile("chrProvince")%>" 
name="chrProvince" size="15">

</td>
</tr>

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

<% 

 ' Check to see which country was selected previously if there was an error.
if rsProfile("chrCountry") = "US" then 
SelUS = "Selected"
end if

if rsProfile("chrCountry") = "CA" then 
SelCA = "Selected"
end if

if rsProfile("chrCountry") = "MX" then 
SelMX = "Selected"
end if

%>
 <!-- Country select box -->
<select name="chrCountry">
<option value="">Select a Country
<option value="US" <%=SelUS%>>United States
<option value="CA" <%=SelCA%>>Canada
<option value="MX" <%=SelMX%>>Mexico
</select> 

</td>
</tr>


<!-- Zip Code -->
<tr>
<td align="right">Zip/Postal Code:</td>
<td>
<input type="text" value="<%=rsProfile("chrZipCode")%>" 
name="chrZipCode">
</td>
</tr>
<!-- Phone -->
<tr>
<td align="right">Phone:</td>
<td>
<input type="text" value="<%=rsProfile("chrPhone")%>" 
name="chrPhone">
</td>
</tr>
<!-- Fax -->
<tr>
<td align="right">Fax:</td>
<td>
<input type="text" value="<%=rsProfile("chrFax")%>" name="chrFax">
</td>
</tr>
<!-- Email -->
<tr>
<td align="right">Email:</td>
<td>
<input type="text" value="<%=rsProfile("chrEmail")%>" name="chrEmail">
</td>
</tr>
<!-- Password -->
<tr>
<td align="right">Password:</td>
<td>
<input type="password" value="<%=rsProfile("chrPassword")%>" 
name="chrPassword")>
</td>
</tr>
<!-- Option to save the profile as a cookie -->
<tr>
<td align="right">Save Profile Cookie?:</td>
<td>

<%
 ' Default the cookie based on the previous selection.
if rsProfile("intCookie") = 1 then

YesChecked = "CHECKED"

else

NoChecked = "CHECKED"

end if
%>

 <!-- Radio button input for defaulting a cookie with the shopper ID -->
<input type="radio" value="1" name="intCookie" <%=YesChecked%>> Yes
<input type="radio" value="0" name="intCookie" <%=NoChecked%>> No
</td>
</tr>
<!-- Submit Button -->
<tr>
<td colspan="2" align="center">
<input type="hidden" name="idShopper" 
value="<%=rsProfile("idShopper")%>">
<input type="submit" value="Submit" name="Submit">
</td>
</tr>

</table> 

</form>

<%

end if

%>

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

</BODY>
</HTML>

 

This ASP has many of the same form issues as we saw with the Shipping.asp, Payment.asp and their validation pages!  Makes you want to require your shoppers to fill out profiles and do all of their modifications from these only!