Updating the Profile

 

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

First we will develop the stored procedure then the ASP.

 

The Stored Procedure.  This file should be called sp_UpdateShopper.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_UpdateShopper " & _
"@chrFirstName varchar(150), " & _
"@chrLastName varchar(150), " & _
"@chrAddress varchar(150), " & _
"@chrCity varchar(150), " & _
"@chrState varchar(150), " & _
"@chrProvince varchar(150), " & _
"@chrCountry varchar(100), " & _
"@chrZipCode varchar(50), " & _
"@chrPhone varchar(25), " & _
"@chrFax varchar(25), " & _
"@chrEmail varchar(100), " & _
"@chrPassword varchar(25), " & _
"@intCookie int, " & _
"@idShopper int " & _
"AS " & _
"update shopper set chrFirstName = @chrFirstName, " & _
"chrLastname = @chrLastName, " & _
"chrAddress = @chrAddress, " & _
"chrCity = @chrCity, " & _
"chrState = @chrState, " & _
"chrProvince = @chrProvince, " & _
"chrCountry = @chrCountry, " & _
"chrZipCode = @chrZipCode, " & _
"chrPhone = @chrPhone, " & _
"chrFax = @chrFax, " & _
"chrEmail = @chrEmail, " & _
"chrPassword = @chrPassword, " & _
"intCookie = @intCookie " & _
"where idShopper = @idShopper"

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 UpdateProfile.asp.  This ASP is used to update the profile.  The following UpdateProfile.asp will 
  1. Retrieve the data in the profile and store it in local variables..
  2. Do our usual validation of the profile data for name, address, phone, e-mail.
  3. Feedback information to the shopper and take them back to the Profile.asp if there is an error.
  4. If there is no error then open the database connection and clean up the data.
  5. Build the SQL command to execute sp_UpdateShopper.
  6. Write out the cookie if the shopper requested it.
  7. Give the shopper feedback that their profile has been updated using the Header.asp and Footer.asp to give the usual navigation and closure.
<%@ Language=VBScript %>
<%
' ****************************************************
' UpdateProfile.asp - This page updates the profile
' based on the entries by the user.
' ****************************************************

' Retrieve all of the data that the user entered by using the request object.
chrFirstName = request("chrFirstName")
chrLastName = Request("chrLastName")
chrAddress = Request("chrAddress")
chrCity = Request("chrCity")
chrState = Request("chrState")
chrProvince = Request("chrProvince")
chrCountry = Request("chrCountry")
chrZipCode = Request("chrZipCode")
chrPhone = Request("chrPhone")
chrFax = Request("chrFax")
chrEmail = Request("chrEmail")
chrPassword = Request("chrPassword")
intCookie = request("intCookie")

' Check to see if the first name was entered.
if chrFirstName = "" then 

' Give an error if not.
strError = "You did not enter in your first name.<BR>"

end if

' Check to see if a last name was entered.
if chrLastName = "" then

strError = strError & "You did not enter in your last name.<BR>"

end if

' Check to see if an address was entered
if chrAddress = "" then

strError = strError & "You did not enter in your address.<BR>"

end if

' Check to see if a city was entered.
if chrCity = "" then

strError = strError & "You did not enter in your city.<BR>"

end if

' Check to see if the selected country is US
if chrCountry = "US" then

' Check to see if a state was entered.
if chrState = "" then

' Build the error.
strError = strError & "Invalid state<BR>"

end if

else

' If a International country then check the province field.
if chrProvince = "" then

' Build the error.
strError = strError & "Invalid province<BR>"

end if

end if

' Ensure a country was entered.
if chrCountry = "" then

' Build an error string.
strError = strError & "Invalid country<BR>"

end if

' Check to see if a zip code was entered.
if chrZipCode = "" then

' Build an error string.
strError = strError & "You did not enter in your zip code.<BR>"

end if

' Check to see if a zip code was entered.
if chrPhone = "" then

strError = strError & "You did not enter in your phone number.<BR>"

end if

' Check to see if a zip code was entered.
if chrEmail = "" then

strError = strError & "You did not enter in your email address.<BR>"

end if

' Check to see if a zip code was entered.
if chrPassword = "" then

strError = strError & "You did not enter in your password.<BR>"

end if


' Now we check to see if there are any errors.
if strError <> "" then

%>

<HTML>

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

 <!-- Note the error -->
<B><font color="red">
There is an error in your profile:<BR><BR>
</b></font>

<%

 ' Write out the error messages
Response.Write strError 

 ' Save the email and password in session
' variables for reference on the profile form.
session("email") = chrEmail
session("password") = chrPassword

%>

<!-- Link back to the profile page. The check
parameter indicates the email and password 
should be retrieved from session variables. -->
<BR>
Click <a href="profiledisplay.asp?Check=1">here</a> to update.

<%

else

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

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

dbProfile.Open

 ' If any of our names have a single quote, we will 
' need to double it to insert it into the database
chrFirstName = replace(chrFirstName, "'", "''")
chrLastName = replace(chrLastName, "'", "''")
chrAddress = replace(chrAddress, "'", "''")
chrCardName = replace(chrCardName, "'", "''")
chrCity = replace(chrCity, "'", "''")

 ' SQL statement to update the profile in the database
sql = "execute sp_UpdateShopper '" & _
request("chrFirstName") & "', '" & _
request("chrLastName") & "', '" & _
request("chrAddress") & "', '" & _
request("chrCity") & "', '" & _
request("chrState") & "', '" & _
request("chrProvince") & "', '" & _
request("chrCountry") & "', '" & _
request("chrZipCode") & "', '" & _
request("chrPhone") & "', '" & _
request("chrFax") & "', '" & _
request("chrEmail") & "', '" & _
request("chrPassword") & "', " & _
request("intCookie") & ", " & _
request("idShopper")

 ' Execute the SQL statement
dbProfile.execute(sql)

 ' Write out the cookie
if request("intCookie") = 1 then

' Store the shopper ID
Response.Cookies("WWCD") = request("idShopper")

 ' Expire the cookie down the road.
Response.Cookies("WWCD").Expires = "December 31, 2003"

else

' Delete the cookie
Response.Cookies("WWCD") = ""

end if

%>

<HTML>

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

 <!-- Thank the customer for the order -->
<b>Your profile has been updated!</b>

<%

end if

%>

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

</BODY>
</HTML>

 

This is another, essentially, purely processing script using other pages as the interface.