Validating Payment During the Check Out Process

 

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

We will need three stored procedures to initialize the order, insert the order data and insert the payment data.  These are called sp_InitializeOrderStatus, sp_InsertOrderData and sp_InsertPaymentData.  We also need to update all of the information in the shopping basket using sp_UpdateBasket.  The following table displays the name of each stored procedure and the ASP file in which it is called.

 

Stored Procedure ASP Container

sp_InitializeOrderStatus

sp_InsertOrderData

sp_InsertPaymentData

sp_UpdateBasket

 ValidatePayment.asp

 

The Stored Procedure.  Now I will list out each stored procedure that you should create using SQL and ASPs pretty much exactly like we have for the other stored procedures.  This first file should be called sp_InitializeOrderStatus.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_InitializeOrderStatus @idOrder int AS " & _
"insert into OrderStatus(idOrder) values(@idOrder)"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

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

%>

 

 

This second file should be called sp_InsertOrderData.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_InsertOrderData " & _
"@idShopper int, " & _
"@chrShipFirstName varchar(150), " & _
"@chrShipLastName varchar(150), " & _
"@chrShipAddress varchar(150), " & _
"@chrShipCity varchar(150), " & _
"@chrShipState varchar(25), " & _
"@chrShipProvince varchar(150), " & _
"@chrShipCountry varchar(150), " & _
"@chrShipZipCode varchar(150), " & _
"@chrShipPhone varchar(150), " & _
"@chrShipEmail varchar(150), " & _
"@chrBillFirstName varchar(150), " & _
"@chrBillLastName varchar(150), " & _
"@chrBillAddress varchar(150), " & _
"@chrBillCity varchar(150), " & _
"@chrBillState varchar(25), " & _
"@chrBillProvince varchar(150), " & _
"@chrBillCountry varchar(150), " & _
"@chrBillZipCode varchar(150), " & _
"@chrBillPhone varchar(150), " & _
"@chrBillEmail varchar(150), " & _
"@idBasket int AS " & _
"insert into orderdata(idShopper, chrShipFirstName, " & _
"chrShipLastName, chrShipAddress, " & _
"chrShipCity, chrShipState, " & _
"chrShipProvince, chrShipCountry, " & _
"chrShipZipCode, chrShipPhone, " & _
"chrShipEmail, chrBillFirstName, " & _
"chrBillLastName, chrBillAddress, " & _
"chrBillCity, chrBillState, " & _
"chrBillProvince, chrBillCountry, " & _
"chrBillZipCode, chrBillPhone, " & _
"chrBillEmail, idBasket) " & _
"values(@idShopper, @chrShipFirstName, " & _
"@chrShipLastName, @chrShipAddress, " & _
"@chrShipCity, @chrShipState, " & _
"@chrShipProvince, @chrShipCountry, " & _ 
"@chrShipZipCode, @chrShipPhone, " & _
"@chrShipEmail, @chrBillFirstName, " & _
"@chrBillLastName, @chrBillAddress, " & _
"@chrBillCity, @chrBillState, " & _
"@chrBillProvince, @chrBillCountry, " & _
"@chrBillZipCode, @chrBillPhone, " & _
"@chrBillEmail, @idBasket) " & _
"select idOrder = @@identity"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

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

%>
This file is considerably longer because of all the order data you need.  Each entry has been put on its own line in attempt to increase the clarity of what is contained in the procedure.

 

 

This third file should be called sp_InsertPaymentData.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_InsertPaymentData " & _
"@idOrder int, " & _
"@chrCardType varchar(100), " & _
"@chrCardNumber varchar(50), " & _
"@chrExpDate varchar(25), " & _
"@chrCardName varchar(150) " & _
"AS " & _
"insert into paymentdata(idOrder, chrCardType, " & _
"chrCardNumber, chrExpDate, chrCardName) " & _
"values(@idOrder, @chrCardType, " & _
"@chrCardNumber, @chrExpDate, @chrCardName)"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

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

%>

 

 

This fourth file should be called sp_UpdateBasket.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_UpdateBasket " & _
"@idBasket int, " & _
"@intQuantity int, " & _
"@intSubTotal int, " & _
"@intShipping int, " & _
"@intFreeShipping int, " & _
"@intTax int, " & _
"@intTotal int " & _
"AS " & _
"update basket set " & _
"intQuantity = @intQuantity, " & _
"intSubtotal = @intSubtotal, " & _
"intShipping = @intShipping, " & _
"intFreeShipping = @intFreeShipping, " & _
"intTax = @intTax, " & _
"intTotal = @intTotal " & _
"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 ValidatePayment.asp.  Since we have just created the stored procedures required for this ASP we can now make use of them.   The following ValidatePayment.asp will 
  1. First retrieve all the data entered by the user.
  2. Then we start validating the presence of particular such as the credit card name and number after having taken out any hyphens.
  3. Then we validate the expiration date to ensure the card is still at least reported as valid.
  4. Then we do a fairly extensive check of the billing particulars such as name, address, phone and e-mail along with accumulating appropriate error messages.
  5. Then we store all of the inputs into session variables and redirect the user back to the Payment.asp if there were errors where they are given a listing of the difficulties with their inputs.
  6. If the inputs pass the error checking we start to deal with the data in order to 
    1. retrieve it and clean it
    2. insert the order data into the database using sp_InsertOrderData.
    3. insert the payment data using sp_InsertPaymentData.
    4. update the shopping basket with the latest figures using sp_UpdateBasket.
    5. update the profile based on the shopper's most recent inputs, including the password for future access of the profile.
    6. send out an e-mail confirmation
    7. write out a cookie if the user desires it
<%@ Language=VBScript %>
<%

' Retrieve the credit card data
chrCCName = request("chrCCName")
chrCCNumber = request("chrCCNumber")
chrCCType = request("chrCCType")
chrCCExpMonth = request("chrCCExpMonth")
chrCCExpYear = request("chrCCExpYear")

' Retrieve the billing data
chrBillFirstName = request("chrBillFirstName")
chrBillLastName = request("chrBillLastName")
chrBillAddress = request("chrBillAddress")
chrBillCity = request("chrBillCity")
chrBillState = request("chrBillState")
chrBillProvince = request("chrBillProvince")
chrBillCountry = request("chrBillCountry")
chrBillZipCode = request("chrBillZipCode")
chrBillPhone = request("chrBillPhone")
chrBillEmail = request("chrBillEmail")
chrPassword = request("chrPassword")
intCookie = request("intCookie")

' Check to see if a credit card name was entered
if chrCCName = "" then

' Build the error.
strError = strError & "Invalid name on credit card<BR>"

end if

' Replace any - characters or spaces to make it a pure number
chrCCNumber = replace(chrCCNumber, "-", "")
chrCCNumber = replace(chrCCNumber, " ", "")

' Check to ensure the credit card number is not blank and is a number.
if (chrCCNumber = "") or (isNumeric(chrCCNumber) = False) then

strError = strError & "Invalid credit card number<BR>"

end if

' Check to see the year is equal to this year
if cint(chrCCExpYear) = year(date) then

' If it is the current year then we need to ensure the
' month is not earlier than the current month.
if cint(chrCCExpMonth) < month(date) then

strError = strError & "Invalid expiration month<BR>"

end if

end if

' Check to ensure the year is not less than the current year
if chrCCExpYear < year(date) then

strError = strError & "Invalid expiration date<BR>"

end if

' Ensure there is a first name
if chrBillFirstName = "" then

strError = strError & "Invalid first name<BR>"

end if

' Ensure there is a last name
if chrBillLastName = "" then

strError = strError & "Invalid last name<BR>"

end if

' Ensure a billing address was entered
if chrBillAddress = "" then

strError = strError & "Invalid address<BR>"

end if

' Ensure a billing city was entered.
if chrBillCity = "" then

strError = strError & "Invalid city<BR>"

end if

' Check to see if an international country or US was entered.
if chrBillCountry = "US" then

' Ensure a bill to state was entered
if chrBillState = "" then

strError = strError & "Invalid state<BR>"

end if

else

' If it is an international country then ensure a province was entered
if chrBillProvince = "" then

strError = strError & "Invalid province<BR>"

end if

end if

' Ensure a bill to country was entered
if chrBillCountry = "" then

strError = strError & "Invalid country<BR>"

end if

' Ensure a bill to zip code was entered
if chrBillZipCode = "" then

strError = strError & "Invalid zip code<BR>"

end if

' Ensure a bill to phone was entered
if chrBillPhone = "" then

strError = strError & "Invalid phone number<BR>"

end if

' Validate the email address to ensure it has an '@' sign and a '.'
if (instr(1, chrBillEmail, "@") = 0) or (instr(1, chrBillEmail, ".") = 0) then

strError = strError & "Invalid email address<BR>"

end if

' Ensure that the user didn't enter in both a state and province.
if (chrBillState <> "") and (chrBillProvince <> "") then

strError = strError & "You can not fill in both the state and province fields<BR>"

end if

' Check to see if there was an error.
if strError <> "" then

' Retrieve all of the billing data and store it in session variables
session("chrCCName") = request("chrCCName")
session("chrCCNumber") = request("chrCCNumber")
session("chrCCType") = request("chrCCType")
session("chrCCExpMonth") = request("chrCCExpMonth")
session("chrCCExpYear") = request("chrCCExpYear")
session("chrBillFirstName") = request("chrBillFirstName")
session("chrBillLastName") = request("chrBillLastName")
session("chrBillAddress") = request("chrBillAddress")
session("chrBillCity") = request("chrBillCity")
session("chrBillState") = request("chrBillState")
session("chrBillProvince") = request("chrBillProvince")
session("chrBillCountry") = request("chrBillCountry")
session("chrBillZipCode") = request("chrBillZipCode")
session("chrBillPhone") = request("chrBillPhone")
session("chrBillEmail") = request("chrBillEmail")
session("chrPassword") = request("chrPassword")

 ' Store the error in a session variable
session("Error") = strError

 ' Send the user back to the payment page
Response.Redirect "payment.asp"

else

' We are ready to store and finish the order. This happens in many steps.

'***********************************************
'**** 1. Clean the data.
'***********************************************

 ' Ensure that the shipping data is validated for SQL inserts
' We will check for single quotes and double them up
session("chrShipFirstName") = replace(session("chrShipFirstName"), "'", "''")
session("chrShipLastName") = session("chrShipLastName")
session("chrShipAddress") = session("chrShipAddress")
session("chrShipCity") = session("chrShipCity")
session("chrShipProvince") = session("chrShipProvince")

 '***********************************************
'**** 2. Retrieve the data
'***********************************************

' Retrieve all of the payemnt data and ensure that it is
' ready for a SQL insert.
session("chrCCName") = replace(request("chrCCName"), "'", "''")
session("chrCCNumber") = request("chrCCNumber")
session("chrCCType") = request("chrCCType")
session("chrCCExpMonth") = request("chrCCExpMonth")
session("chrCCExpYear") = request("chrCCExpYear")
session("chrBillFirstName") = replace(request("chrBillFirstName"), "'", "''")
session("chrBillLastName") = replace(request("chrBillLastName"), "'", "''")
session("chrBillAddress") = replace(request("chrBillAddress"), "'", "''")
session("chrBillCity") = replace(request("chrBillCity"), "'", "''")
session("chrBillState") = request("chrBillState")
session("chrBillProvince") = replace(request("chrBillProvince"), "'", "''")
session("chrBillCountry") = request("chrBillCountry")
session("chrBillZipCode") = request("chrBillZipCode")
session("chrBillPhone") = request("chrBillPhone")
session("chrBillEmail") = request("chrBillEmail")
session("chrPassword") = request("chrPassword")
session("intCookie") = request("intCookie")

 '************************************************
'**** 3. Insert the order information into the database
'************************************************

' Create an ADO database connection
set dbOrderData = server.createobject("adodb.connection")
set rsOrderData = server.CreateObject("adodb.recordset")

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

dbOrderData.Open

 ' SQL update statement to insert the the order
' data into the OrderData table.
sql = "execute sp_InsertOrderData " & _
session("idShopper") & ", '" & _
session("chrShipFirstName") & "', '" & _
session("chrShipLastName") & "', '" & _
session("chrShipAddress") & "', '" & _
session("chrShipCity") & "', '" & _
session("chrShipState") & "', '" & _
session("chrShipProvince") & "', '" & _
session("chrShipCountry") & "', '" & _
session("chrShipZipCode") & "', '" & _
session("chrShipPhone") & "', '" & _
session("chrShipEmail") & "', '" & _
session("chrBillFirstName") & "', '" & _
session("chrBillLastName") & "', '" & _
session("chrBillAddress") & "', '" & _
session("chrBillCity") & "', '" & _
session("chrBillState") & "', '" & _
session("chrBillProvince") & "', '" & _
session("chrBillCountry") & "', '" & _
session("chrBillZipCode") & "', '" & _
session("chrBillPhone") & "', '" & _
session("chrBillEmail") & "', " & _
session("idBasket")

 ' Execute the SQL statement
set rsOrderData = dbOrderData.execute(sql)
session("idOrder") = rsOrderData("idOrder")

 '**************************************************
'**** 4. Insert the payment information intothe database
'**************************************************

' Build a SQL statement to insert the 
' payment data into the paymentdata table
sql = "execute sp_InsertPaymentData " & _
session("idOrder") & ", '" & _
session("chrCCType") & "', '" & _
session("chrCCNumber") & "', '" & _
session("chrCCExpMonth") & "/" & session("chrCCExpYear") & "', '" & _
session("chrCCName") & "'"

 ' Execute the SQL statement
set rsOrderData = dbOrderData.execute(sql)

 '***********************************************
'**** 5. Initialize the order status tracking
'**** to indicate the order is received
'***********************************************

' Build a SQL statement to insert the 
' payment data into the paymentdata table
sql = "execute sp_InitializeOrderStatus " & _
session("idOrder")

 ' Execute the SQL statement
set rsOrderData = dbOrderData.execute(sql)

 '***********************************************
'**** 6. Update the basket with the final order data.
'***********************************************

' Finally we need to update the basket with the final
' amounts for quantity, subtotal, shipping, tax and total
sql = "execute sp_UpdateBasket " & _
session("idBasket") & ", " & _
session("Quantity") & ", " & _
session("Subtotal") & ", " & _
session("Shipping") & ", " & _
session("Tax") & "," & _
session("Total") & ", 1"

 ' Execute the SQL statement
set rsOrderData = dbOrderData.execute(sql)

 '***********************************************
'**** 7. Update the profile based on the new
'**** billing information.
'***********************************************

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

 ' 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

 ' SQL insert statement to update the profile in the database
sql = "execute sp_UpdateShopper '" & _
session("chrBillFirstName") & "', '" & _
session("chrBillLastName") & "', '" & _
session("chrBillAddress") & "', '" & _
session("chrBillCity") & "', '" & _
session("chrBillState") & "', '" & _
session("chrBillProvince") & "', '" & _
session("chrBillCountry") & "', '" & _
session("chrBillZipCode") & "', '" & _
session("chrBillPhone") & "', '" & _
session("chrBillFax") & "', '" & _
session("chrBillEmail") & "', '" & _
session("chrPassword") & "', " & _
session("intCookie") & ", " & _
session("idShopper")

 ' Execute the SQL statement
dbProfile.execute(sql)

 '***********************************************
'**** 8. Send an email receipt to the shopper
'***********************************************

set Mailer = Server.CreateObject("CDONTS.NewMail")
Mailer.From = "support@wildwillieinc.com"
Mailer.To = session("chrBillEmail")
Mailer.Subject = "Wild Willie CD Receipt"

strBody = "Thank You for your Order!" & vbCrLf & vbCrLf
strBody = strBody & "Order Id = " & session("idOrder") & vbCrLf
strBody = strBody & "Subtotal = " & formatcurrency(session("Subtotal")/100, 2) & vbCrLf
strBody = strBody & "Subtotal = " & formatcurrency(session("Shipping")/100, 2) & vbCrLf
strBody = strBody & "Subtotal = " & formatcurrency(session("Tax")/100, 1) & vbCrLf
strBody = strBody & "Subtotal = " & formatcurrency(session("Total")/100, 2) & vbCrLf & vbCrLf
strBody = strBody & "Please call 1-800-555-Wild with any questions. "
strBody = strBody & "Be sure and check back to retrieve your order status."

Mailer.Body = strBody

Mailer.Send

 '***********************************************
'**** 9. Write out a cookie if the user 
'**** requested it to be written
'***********************************************

' Write out the cookie if they selected the 'Yes' radio button.
if request("intCookie") = 1 then

Response.Cookies("WWCD") = session("idShopper")

Response.Cookies("WWCD").Expires = "December 31, 2005"

end if

' Send the user to the confirmation page 
Response.Redirect "Confirmed.asp"

end if

%>

 

This ASP is almost entirely a processing script and relies on other ASPs, primarily Payment.asp, as the user interface.