When it comes to programming, time is short, pressure is high, and quality is critical. Working at one of the nation’s largest financial institutions certainly presents itself with these demands. Being a part of the Web development team, we soon found ourselves engrossed with numerous projects that all dealt with obtaining information from the customer. This information that the customer provides is submitted from dozens of Web product applications (loan applications, account applications), E-mail forms (customer service, questions), registration forms, and oodles of other forms to persuade the customer to become a part of a marketing list-serv. So, you could imagine the feeling when your boss tells you that you are responsible for implementing a site-wide customer customization (pre-fill) of standard fields for all customer forms.
This meant that we had to design the site so that a repeat customer will have their personal information automatically displayed on pages where a form or application needs to be filled out. This information would come from a table in a database containing this customer customization information. Therefore, the pre-fill of information would only apply to forms that asked for information contained in this database table (name, E-mail, etc.). After the form is submitted, the information would still be written to the database the form populates, and the customer customization database would also be updated to reflect new information (change of address, E-mail, etc.). The problems of a task like this soon became apparent. How are we going to change every form to reflect this new procedure within our time constraints?
Our job was to find the best solution and implement it within a month. Working with our manager, we decided on a solution that would minimize the amount of code needed for each ASP form page. It also offered standardize field names across all databases (databases storing on-line form information) for the information that needed pre-filled (E-mail address, first name, last name, address, city, state, phone, etc.). The solution was to create two interstitial pages (pages that contain ASP code but do not post HTML) _precustomize.asp and postcustomize.asp. Grasping the concept of two pages doing so much work was difficult. These pages not only simplified the amount of code, but also decreased the entire work load.
The Files
We have included the sample solution in a downloadable ZIP file. The databases used are Microsoft Access databases simply for the sake of providing an easy-to-view sample. Normally, Access should not be used for a large Web site where multiple users are inserting and updating records at one time. This ZIP file includes the following:
Customer_Service.mdb
A sample database containing information that a customer fills out from Emailform.asp to "E-mail customer service."
Emailform.asp
A sample ASP form that the customer fills out to "E-mail customer service."
Register.mdb:
A sample database containing standard customer information used to pre-fill forms located on the Web site. This database is populated (or updated) by postcustomize.asp every time a customer submits a form on the Web site.
Precustomize.asp
An interstitial ASP page that the customer is directed to before he proceeds to Emailform.asp. Precustomize.asp will first confirm if a cookie exists on the customer’s computer. If the cookie does exist, precustomize.asp will create a recordset from the register.mdb database and use this recordset to insert a new record in the database that the form populates (Customer_Service.mdb in this example). Note that precustomize.asp is intended to be used as a page that forms requiring customization will direct to before displaying the form page.
Postcustomize.asp
An interstitial ASP page that the customer is directed to after he submits a form on the Web site. Postcustomize.asp will first open a recordset from the database written to by the form. Then, postcustomize.asp will determine whether or not a cookie has been created on the customer’s machine. If no cookie exists, the recordset will be used to insert a new record into Register.mdb. A cookie will then be created containing the value of a unique field from Register.mdb (the customer’s E-mail address is used for this sample). If a cookie does exist, the recordset will be used to update the already existing record in Register.mdb. Note that postcustomize.asp was designed to be redirected to by all forms requiring customization.
How It Works
The only tedious task to accomplishing this project is to standardize field names in all databases being used to store customer information from form submissions. There are other ways of accomplishing a project such as this, however, we felt that standardizing the fields in all databases would provide benefits in the long run. The only fields you will need to standardize are the fields being stored in Register.mdb. For example, Customer_Service.mdb contains a field called comments. You need not worry about this field because it would not be a field that is standard to all forms, and it would also contain information that is not relevant to other forms
While modifying the field names in the databases would not be much work, going through all ASP pages referring to those field names would be the real issue. To do this, we suggest you use a tool like Microsoft Visual Interdev to open all pages referencing the old field names of a database, and perform a find and replace search to replace the old field name with the standardized name.
After this is done, you can develop the functionality to customize the forms. Below you will find the ASP code for Emailform.asp, precustomize.asp, and postcustomize.asp. I will not bother displaying the HTML in Emailform.asp but may refer to samples of it when needed. The JavaScript in Emailform.asp is also not displayed because it only validates customer input and submits the page when validation is complete.
The code below is from Emailform.asp and contains the values needed to pass to precustomize.asp and a conditional statement validating whether or not the variable precustomize or uniqueid has been set. As you will see, precustomize.asp or Emailform.asp will set uniqueid, and only precustomize.asp will set precustomize. If both variables are set to nothing, we know the customer has not been to precustomize.asp yet.
Emailform.asp code
<%
Option Explicit
dim uniqueid
'Request the uniqueid hidden field (or querystring variable)
'to find out if the value was set
uniqueid = request("uniqueid")
'variables to pass to precustomize.asp
dim URLreturn
dim strConnection
dim tablename
'This variable is set to determine if the customer has gone to precustomize
dim precustomize
precustomize = request("precustomize")
'setting the page to return to when precustomize.asp is done
URLreturn= Server.URLEncode("Emailform.asp")
'The table in Customer_Service.mdb to open
tablename = Server.URLEncode("customer_info")
'The connection string needed to open Customer_Service.mdb
strConnection = Server.URLEncode( _
"Data Source = D:\Inetpub\wwwroot\15seconds\" &_
"customer_service.mdb;Provider=Microsoft.Jet.OLEDB.3.51;")
'If precustomize is blank or the uniqueid is blank, the customer has not yet gone
'to precustomize.asp or the customer has already submitted the form
if precustomize = "" or uniqueid = "" then
Response.Redirect ("precustomize.asp?strconnection=" &_
strconnection & "&tablename=" & tablename &_
"&URLreturn=" & URLreturn)
end if
Assuming that precustomize or uniqueid is blank, Emailform.asp will then redirect to precustomize.asp. Certain values need to be passed to precustomize.asp so Customer_Service.mdb can be opened by precustomize.asp. We also need to pass the value "Emailform.asp" in the querystring so we are able to redirect back to this page when precustomize.asp is done processing. Below contains the code from precustomize.asp.
Precustomize.asp code
<%
Option Explicit
'store values being passed in from asp form page
dim tablename
tablename = request("tablename")
dim strconnection
strconnection = request("strconnection")
dim URLreturn
URLreturn = request("URLreturn")
'Get unique identifier (unique field in Register.mdb) from cookie
dim RegisterCookie
RegisterCookie = Request.Cookies("RegisterEmail")
'Set variable so Emailform.asp knows if user was here
dim precustomize
precustomize = "yes"
Below, we will check to see that RegisterCookie does have a value. If so, the cookie exists, meaning the user has filled out a form on the Web site before. Therefore, we open a recordset from Register.mdb. This recordset will be retrieved based on the value of the cookie. This cookie contains the unique field of Register.mdb (the customer E-mail address for this example).
Precustomize.asp code
'If RegisterCookie has a value, then we know a cookie has been set and therefore, the customer exists in 'Register.mdb
If RegisterCookie <> "" then
dim strconnection2
dim Conn
dim rs
dim sql
'Connection string to open register.mdb
strConnection2 = "Data Source=D:\Inetpub\wwwroot\15seconds\register.mdb; Provider
=Microsoft.Jet.OLEDB.3.51;"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnection2
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "Select * from customer_info where email = '" & RegisterCookie & "'"
rs.Open sql,Conn
'Recordset fields are stored in variables
dim email, first_name, last_name, city, state, zip, address, day_phone, eve_phone
email= rs("email")
first_name = rs("first_name")
last_name = rs("last_name")
city = rs("city")
state = rs("state")
zip = rs("zip")
address = rs("address")
day_phone = rs("day_phone")
eve_phone = rs("eve_phone")
rs.Close
Conn.Close
After the values above have been set, we need to open a new database connection with the database populated by Emailform.asp (Customer_Service.mdb). To do this we use the connection string passed in by the querystring. The next step is to insert the new record in Customer_Service.mdb with the Register.mdb fields. Normally we would just build an SQL insert statement to accomplish this task. However, since the uniqueid field is the value we need to pass to Emailform.asp, this would not be possible. The uniqueid field is an auto increment number created every time a new record is inserted in the database. How would we grab this field from the record we just inserted? If we were using SQL Server, we would just use @@Identity in a Select statement directly after the execution of the Insert statement. After an Insert, Select Into, or bulk copy statement completes, @@Identity contains the last identity value generated by the statement. However, Access does not support this feature. Therefore, one would consider using Select Max (uniqueid) to retrieve the last record inserted, but the nature of Web applications is to provide services to multiple users at one time. What would happen if another customer were also inserting a new record into the database? Now you run the risk of having a customer grab the uniqueid of another customer. This would not be a good practice, so we decided to use the code below.
We first open a new recordset, selecting only those fields being used from the table name we passed in the querystring. To open this recordset, we need to declare a cursor type that will allow us to scroll through the recordset. AdOpenStatic (3) is used because it creates a static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible, but updates are allowed, as well as scrolling through the recordset. More information about cursortypes can be found in the MSDN Library at http://msdn.microsoft.com .
A locktype of adLockPessimistic (2) is also used because it indicates pessimistic locking, record by record,. This means the provider does what is necessary to ensure successful editing of records, usually by locking records at the data source immediately after editing. More information about locktypes can be found in the MSDN Library at http://msdn.microsoft.com . By doing this, we can use the AddNew method of the Recordset object to insert a new record. This way, we can refer to a field we just inserted in the recordset after it has been updated.
Precustomize.asp code
Conn.Open strConnection
dim rs2
Set rs2 = Server.CreateObject("ADODB.Recordset")
sql = "Select uniqueid, first_name,last_name,email,address," &_
"city,state,zip,day_phone,eve_phone from " & tablename
rs2.Open sql,conn,3,2
rs2.AddNew
rs2("first_name")=first_name
rs2("last_name")=last_name
rs2("email")=email
rs2("address")=address
rs2("city")=city
rs2("state")=state
rs2("zip")=zip
rs2("day_phone")=day_phone
rs2("eve_phone")=evening_phone
rs2.Update
uniqueid = rs2("uniqueid")
rs2.close
Conn.Close
set Conn = nothing
end if
Lastly, we check to see if a question mark is contained in URLReturn. If so, we know there is another variable being passed. Therefore, we need to place an ampersand (&) in front of the uniqueid. If not, we place a question mark (?) in front of uniqueid.
Precustomize.asp then redirects back to the originating ASP form page.
Precustomize.asp code
if InStr(1,UrlReturn,"?") then
Response.Redirect (UrlReturn & "&uniqueid=" & uniqueid & "&precustomize=" & precustomize)
else
Response.Redirect (UrlReturn & "?uniqueid=" & uniqueid & "&precustomize=" & precustomize)
end if
%>
The following code displays the remaining ASP code in Emailform.asp.
Emailform.asp code
dim registration
dim Conn
dim RS
dim sql
dim action
'Request the hidden field "action" to find out if the page was submitted.
'If so, the value will be set to "submit"
action = request("action")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnection
Set RS = Server.CreateObject("ADODB.Recordset")
'If the unique ID is not blank, then it contains the unique identifier
'needed to retrieve the record in 'Customer_Service.mdb that was created in precustomize.asp
if uniqueid <> "" then
sql = "Select * from customer_info where email ='" & uniqueid & "'"
RS.Open sql, Conn
end if
'If the hidden field action is equal to "submit," then the page has submitted
'to itself (the hidden field gets set after 'ASP is processed)
It is very important to note that Emailform.asp resubmits to itself, as most form-based ASP pages do. As a result, the ASP will process the information the customer has input and then redirect to the appropriate location. As you can see, there is a check to see whether or not the variable action = "submit." Action is set to the hidden field called action, which is set to a value of "Submit." Since ASP is processed before HTML, this value does not exist until the ASP is done processing. Therefore, we can test when the page has been submitted or entered for the first time. If action equals "submit," we need to store the control values (customer input values) into variables.
Emailform.asp code
if action = "submit" then
dim first_name,last_name,email,address,city,state,
dim zip,comments,day_phone,eve_phone
first_name = request("firstname")
last_name = request("lastname")
email = request("email")
address = request("address")
city = request("city")
state = request("state")
zip = request("zip")
comments = request("comments")
day_phone = request("dayphone")
eve_phone = request("evephone")
If the uniqueid is blank, we know that a record has not yet been created in Customer_Service.mdb. Therefore, we will insert a new record into Customer_Service.mdb, setting the fields equal to the control values (textbox values, drop-down values, etc.). The same technique used in precustomize.asp (as mentioned earlier) will be used here so the uniqueid field can be retrieved after the new record is added.
Emailform.asp code
'If uniqueid is blank, then precustomize.asp did not find a cookie.
'Therefore, a new record will be inserted into
'Customer_Service.mdb.
if uniqueid = "" then
dim rs2
Set rs2 = Server.CreateObject("ADODB.Recordset")
sql = "Select * from " & tablename
rs2.Open sql,conn,3,2
rs2.AddNew
rs2("first_name")=first_name
rs2("last_name")=last_name
rs2("email")=email
rs2("address")=address
rs2("city")=city
rs2("state")=state
rs2("zip")=zip
rs2("day_phone")=day_phone
rs2("eve_phone")=eve_phone
rs2("comments") = comments
rs2.Update
uniqueid = rs2("uniqueid")
rs2.close
Conn.Close
set Conn = nothing
If the uniqueid is not blank, we need to update the already existing record in Customer_Service.mdb. Note that this style of code is also useful when you are passing the uniqueid to multiple ASP form pages. For example, if the customer decides he wants to go back to a previous form to make changes, the fields will still be displayed since the uniqueid is set.
Emailform.asp code
'If uniqueid is not blank, precustomize.asp found a cookie and created a new record in
' Customer_Service.mdb 'with the standardized fields located in Register.mdb.
' Therefore, we will need to update this record with new 'changes and/or nonstandard fields
else
sql = "Update customer_info Set first_name = '" &_
first_name & "', last_name = '" & last_name & "'," & _
"email = '" & email & "',address = '" &_
address & "', city = '" & city & "',state = '" &_
state & "'," & _
"zip = '" & zip & "',comments = '" &_
comments & "',day_phone = '" &_
day_phone & "',eve_phone = '" & _
eve_phone & "' where email = '" & uniqueid & "'"
Conn.Execute sql
Conn.Close
end if
Set Conn = nothing
The page will now redirect to postcustomize.asp with the necessary variables being passed in the querystring.
Emailform.asp code
'The page will now be redirected to postcustomize.asp
Response.Redirect ("postcustomize.asp?uniqueid=" & uniqueid &_
"&strConnection=" & strConnection & "&tablename=" & tablename & "&URLReturn=" & URLReturn)
end if
%>
Below displays the HTML and ASP code needed to pre-fill each control.
Postcustomize.asp plays the final role in our customization process. This page has two major roles: 1) Postcustomize.asp updates or inserts a record into the register database, depending on whether or not a cookie exists on the user's machine; 2) Postcustomize.asp will also return the user to Emailform.asp, finalizing the process.
Postcustomize.asp code
<%
option explicit
'storing value of cookie in a variable
dim RegisterCookie
RegisterCookie = Request.Cookies("RegisterEmail")
dim Urlreturn
dim uniqueid
dim tablename
dim strConnection
UrlReturn = Request("UrlReturn")
uniqueid = request("uniqueid")
strConnection = request("strConnection")
tablename = request("tablename")
'Creating a connection with Customer_Service.mdb and opening up the Recordset
dim Conn
dim rs
dim sql
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnection
Set rs = Server.CreateObject("ADODB.Recordset")
Now you want to obtain values from the current application (E-mail) using the uniqueid variable as the criteria. Remember from above you set uniqueid equal to the request ("uniqueid"). Setting up variables equal to the Recordset fields is the most efficient way to retrieve database information
Postcustomize.asp code
sql = "Select * from " & tablename & " where uniqueid = '" & uniqueid & "'"
rs.Open sql,Conn
dim email,first_name,last_name,city,state,zip,address,day_phone,eve_phone
email= rs("email")
first_name = rs("first_name")
last_name = rs("last_name")
city = rs("city")
state = rs("state")
zip = rs("zip")
address = rs("address")
day_phone = rs("day_phone")
eve_phone = rs("eve_phone")
'Always remember to close your connections!
rs.Close
Conn.Close
'Set your connection string to open Register.mdb
strConnection = "Data Source=D:\Inetpub\wwwroot\15seconds\register.mdb; Provider =Microsoft.Jet.OLEDB.3.51;"
'Open the connection string
Conn.Open strConnection
The following code is very important. The "if" statement is checking to see if the cookie is empty. If it is not empty, the user is already registered in Register.mdb. If the user already has a record in the database, we do not want to insert a new field, but update field information.
Postcustomize.asp code
if RegisterCookie <> "" then
sql = "Update customer_info Set first_name = '" & first_name &_
"', last_name = '" & last_name & "'," & _
"email = '" & email & "',address = '" & address & "', city = '" &_
city & "',state = '" & state & "'," & _
"zip = '" & zip & "',day_phone = '" & day_phone &_
"',eve_phone = '" & eve_phone & "' where email = '" & RegisterCookie & "'"
'Execute your SQL statement
Conn.Execute sql
'Close your connection object
Conn.Close
Set Conn = nothing
The "else" statement below would only occur if there is no cookie. No cookie would mean the user is a first-time visitor to the site or has previously declined for us to keep their information on file. We will insert a new record into the database, thus registering the customer in Register.mdb. The customer's information will now be pre-filled every time he visits a page that uses this functionality. This code allows us to obtain values for the user for future visits to theWeb site.
Along with inserting a new record into Register.mdb, you need to create the cookie. This cookie is set on the user's machine and is designed to expire in one year from the date it was set. The path is set to "/," which means the path of the cookie will be set to the root of your directory. The final line in the cookie code is storing the customer's E-mail address. This will be used to retrieve the customer's information from Register.mdb in the future.
Postcustomize.asp code
Response.Cookies("RegisterEmail").Expires = DateAdd("d",365,Date)
Response.Cookies("RegisterEmail").Path = "/"
Response.Cookies("RegisterEmail")= email
end if
'Check to see if urlreturn already contains a "?"
if InStr(1,UrlReturn,"?") then
Response.Redirect (UrlReturn & "&uniqueid" & uniqueid)
else
Response.Redirect (UrlReturn & "?uniqueid=" & uniqueid)
end if
%>
As a developer, this process seemed the most logical and efficient way to improve consistency and decrease database and coding errors. Our client receives about 330,000 hits a day and averages more than 10 million hits a month. With hundreds of forms being completed a day, the daily chores of programming can quickly add up.
Many other solutions could be used for a project such as this. For example, placing the functionality of precustomize.asp and postcustomize.asp into functions would also work. However, we used interstitial ASP pages to portray how powerful a separate "non-HTML posting" page can actually be.
Ryan Tuma and Ryan Keenan are technology consultants for Software Architects, Inc., information system consultants, in Columbus, Ohio.
Ryan Tuma graduated cum laude with honors from Ohio University, obtaining a degree in Business Administration, majoring in Finance and Management Information Systems. He is a member of the Golden Key National Honor Society and Beta Gamma Sigma, which is recognized as the highest honor given to a student with a degree in business. Ryan has worked with Software Architects since June 1999 and has learned much through his client endeavors including Internet, e-commerce and client/server technologies.
Ryan Keenan graduated cum laude from Ohio University, obtaining a degree in Business Administration, majoring in Management Information Systems. Ryan has worked with Software Architects since June 1998 and acquired his Microsoft Certified Solution Developer (MCSD) certification in March 1999. By working with several different clients, he has gained invaluable experience with Internet and client/server technologies.
Since 1991, the Columbus Branch of Software Architects, Inc., has demonstrated excellence in information systems consulting. Our commitment to customer service and client satisfaction have resulted in long-term partnerships with major Columbus businesses in clothing, banking, telecommunications, and insurance. With a staff of 130-plus consultants and growing, we have the resources to engage projects ranging from departmental to enterprise in scope.
Central to our success is the ongoing technical and professional growth of our staff. Since we are 100 percent permanent employee-based, Software Architects is able to invest heavily in training and education. This keeps us challenged and at the leading edge of our marketplace. We maintain a supporting and inclusive management philosophy that emphasizes individual growth. The many sports and activities supported by the company encourage team building and personal friendships.
To visit us, please stop by www.sark.com. Questions or comments regarding this article can be sent to
Rkeenan@sark.com or Rtuma@sark.com.
Developers who don't want to spend a lot of money on SQL Server and who want a database that's more robust than Access may find MySQL to be a pleasant alternative. This introductory article covers the bare essentials for getting MySQL installed and running in the Win32 environment. [Read This Article][Top]
There is never a guarantee of project success when endeavoring to build a sophisticated application. However, there are established steps to follow that will ensure a clear, concise scope, support for the team involved, and a solid opportunity for successful deployment. [Read This Article][Top]
If your SQL Server is exposed to the Internet, then hackers are probing it. This article shows how to secure a SQL Server database that's being used with a Web application [Read This Article][Top]
Feel intimidated by .NET? This article by Rob Chartier is designed to ease any level VBScripter (ASP) into .NET by clarifying some OOP concepts. [Read This Article][Top]
A few members of the 15 Seconds discussion list talk about the proper way to use methods in order to prevent ADO object errors.
[Read This Article][Top]
Solomon Shaffer explores the life cycle of an ASP.NET page from initialization to unloading. He also explains the various methods to override ASP.NET server-side events. [Read This Article][Top]
Rob Chartier creates a simple portable and reusable address book in .NET to demonstrate the power of N-tier application architecture. Complete source code included! [Read This Article][Top]
Learn about N-tier application architecture and realize that developing with multiple layers produces a flexible and reusable application for distribution to any number of client interfaces. [Read This Article][Top]
Learn about N-tier application architecture and realize that developing with multiple layers produces a flexible and reusable application for distribution to any number of client interfaces. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
