Databases and Cookies

Databases and Cookies

From a programmer's standpoint, one great problem of the web is the lack of an intrinsic identification of visitors. Cookies provide a means of identifying visitors, with their approval, so that continuity can be established between visits. Cookies are limited in the amount of information they can hold but, by storing an identification number in a cookie, we can then use that ID with ASP-ADO to look up a returning visitor in our database, and thus have all of the visitor's data available.

The following section provides a review of the purpose of cookies and a few key points to remember about cookies. If you have been working with ASP pages for very long you are probably already familiar with the basics and can skip to the section entitled "Using cookies with ADO and a Database." The topic of cookies is also covered in depth in Beginning and Professional Active Server Pages (ISBN 1-861001-34-7 and 1-861001-26-6) with additional information on cookies in the context of sessions and applications.

Cookies were created to overcome the problem of identifying visitors to the site for the purpose of maintaining some continuity between requests. There is no mechanism built into the World Wide Web that states the name of the PC that is sending a request, or that provides the ability to sense that a series of requests are all coming from the same visitor. Recall that the WWW was originally designed to provide rapid and universal access to pages of information. There was no need for a system to track a series of requests as being from the same user. Implementation of that type of continuity would slow down the server.

However, modern sites provide a more interactive experience, which frequently requires that the server has an identification of the visitor. For example, visitors may proceed through many pages of forms to place an order; the server will have to know which was the last page completed by that user and be capable of holding information between submissions. Cookies were designed to hold information about a user, including an identifying number. When a visitor returns to a site after a few minutes or a few years the site can identify that user and serve appropriate pages.

After teaching the concepts and techniques of cookies to several classes, I have found that the same concepts cause confusion for many people. This section provides explanations for those problems areas. Note that these explanations are in the context of a site designer working with ASP-ADO, and are generalities. Other site design tools offer their own techniques for working with cookies. In each case there are a few exceptions, but it helps my students to understand the core ideas first, then deal with exceptions later.

Many people wonder how the cookie gets set in the first place. It is not automatic. There is nothing built into the server or the browser that automatically sets cookies. The cookie-making process starts with several lines of code that you write in a page of your site. When the visitor opens that page, the lines of code instruct the browser to create the cookie. In summary, the script of the page instructs the browser to carry out the cookie baking.

Cookies are stored on the hard drive of the browser's machine. Each cookie has several pieces of data:

• Which domain (web site) set the cookie

• A date of expiration for this cookie

• One or more pieces of data

• Optional: keys that organize multiple data in folder-like groups

Note that the browser knows that a particular domain set a particular cookie.

As programmers, we can write an ASP page that tells the browser to create a cookie. We do not need to know any further specifics about how the browser creates the cookie since the browser performs the actual writing. Many students ask about the intricacies of how cookies are formatted and organized on the disk, but that topic is not part of ASP-ADO, it is part of a course in programming IE or Netscape. As programmers, we only need to know that all browsers have the ability to receive and carry out generic (not browser-specific) instructions regarding cookies. To summarize, to the programmer all browsers have the same interface regarding cookies. The details of carrying out the job vary between browsers, but that does not concern us in this book.

Cookies, by default, only last as long as the browser is open. To make a cookie persist beyond the current session of the browser, you as the programmer must set an expiration date for some day in the future.

The WWW standards for browsers specify that they must support at least 300 cookies. That is a total from all websites, so the server that sets the 301st cookie erases the first cookie. In addition, each cookie size cannot exceed 4KB. There is also a limit of about 250 keys per cookie. In practical work, the limits on keys are not a problem. However, a very active web surfer could exceed the number of cookies supported by her browser. As covered below, it is important to have your site prepared for the non-existence of cookies.

Prior to sending an URL request to the ISP, the browser checks to see if there are any cookies that have been set by that domain. If there are, then the browser copies the cookie's data into the header and sends it with the URL. Now consider this from the server side; this means that all of the cookies you set are available to you, as the programmer, in each and every page request sent from the browser. You can extract the cookie data and use it in the first response page. To repeat, when reading cookies you do not make a separate trip to the browser to get the cookies; all the cookies set by your domain are sitting right in the Request object, having been sent to the server by the browser with the original request.  

Looking at your cookies

Although it is not of direct concern to us to see the cookies (remember, the browser will set and serve cookies to ASP pages), they can be viewed easily. If you are using Microsoft Internet Explorer on Windows 98, you can look in C:\Windows\Cookies where you will see small text files holding each cookie; the name of the file is the user name and the domain name. In Netscape you can see them in C:\program files\netscape\users\ \cookies.txt. Note that, as a security measure, when a cookie file is opened the browser considers the cookie tampered with and thus nonexistent.

Cookies suffer from a multitude of abuses, and thus can never be relied upon to be there when needed. Any of the following events can render a cookie unusable or inappropriate:

• Replacing or reformatting the hard drive

• Expiration of the cookie

• Movement of a visitor to a different PC

• Use of a PC by a different user

• Preference settings on a browser that prevent the setting of cookies

• Use of a firewall that prevents setting or reading cookies

• Switching between browser softwares

• Deletion or corruption of the cookie files

It is important to design your site to handle the possible absence of a cookie, not only from first-time visitors, but from returning browsers that have lost or rendered their cookies inappropriate.

Cookies can hold one datum or many. A minimalist approach is to store in the cookie only an identifier, which is then used to look up all other information in a database, using ASP-ADO. The large-cookie approach is to store all of the information that will be needed in the cookie itself. Generally, an intermediate tack is taken, with the most commonly and urgently needed information in the cookie; particularly the user's ID number, and less frequently used or secure information held in the database for look-up.

However, doing a look-up has time and server load costs, and so if there are one or two fundamental pieces of data we may want to include them in the cookie. For example, storing display preferences in a cookie would allow the building of pages with larger type for aging baby-boomers. Likewise, some basics on the visitor's area of interest would allow information of greatest appeal to the visitor to be immediately displayed. Another useful piece of data, if available, is the visitor's zip code, which allows geographic specialization.

Factors in favor of keeping data in cookies:

• The data is crucial to improving page loading speed

• The data is used frequently, for example building every page

• The information is not confidential

Factors in favor of keeping data in a database for look-up:

• Data is only used infrequently in the site

• You want to minimize the impact of the cookie on the user

• Cookies are likely to be unavailable, e.g. destroyed, unaccepted, or inapplicable

• The data needs to be kept secure

I mention several times in this chapter that if you use cookies you will have to address the possibility that a visitor does not accept cookies or has lost a cookie that you set. There are several ways to handle this problem. The first is to require that visitors sign in each time they visit the site. A second is to check if they have your cookie, and if not shunt them to a page which asks them to re-register and re-set their cookie.

A third option is to offer a set of pages that are cookie-less. This suite of pages would be weaker in features and more limited in scope than those for cookie-acceptors. At various points on these pages you could offer the chance for the visitor to change their mind about accepting a cookie.

Fourth, there is a product called Cookie Munger, which can emulate most aspects of cookies for cookie-deniers. More information is available by searching the Microsoft site for "Cookie Munger." A good paper resides, at the time of this publication, at http://msdn.microsoft.com/workshop/server/toolbox/cookie.asp. The Munger searches both outgoing and incoming data streams, and if it sees cookie-like activity it excises the information and performs the same read or write task, but instead by adding extensions to the URL. Design drawbacks include performance hits that can become significant, cumbersome URLs that get book-marked, and other problems. Furthermore, in some cases it just doesn't work.

Lastly, in case you are thinking of avoiding cookies by using ASP sessions variables, remember that sessions require cookies.

Cookies have a bad reputation and thus you may encounter resistance from customers. The facts, though, are quite simple. First, it is impossible to set a cookie without the approval of the user. Second, cookies are generally only available to the domain that set them. Third, cookies do not have the ability to gather and report information about other activities of the user back to a web site.

Having said the above, there is also a level of responsibility required on the part of the programmer. Secure information such as a credit card number should not be stored in a cookie. Although it would be difficult for another web site to obtain the information, a hacker could find the cookie on the hard drive and derive the sensitive information.  

Let's look at several levels of using cookies, the simplest requiring ASP only (no ADO). A simple case would be to store in a cookie a user's preference about how to display a page. The technique would work like this for a site called www.MyCookieSite.com:

• During the first visit to www.MyCookieSite.com the site uses a form page to ask the user if they prefer red or black color characters.

• That preference is set in a cookie by code contained in a second page that uses ASP.

• Subsequently when that user logs in to www.MyCookieSite.com, the browser sends along the cookie to the server. The ASP code checks the preference in the cookie and sets the character color to suit the preference. The visitor sees the page with the color he selected.

Now let's take a more sophisticated case where we use the cookie to store a membership number, and then use ADO to look that up in a database and derive additional information about the member.

• When the visitor joins www.MyClub.org, the site uses a form page to gather from the user their mailing address, including the zip code. The new member is added to the database and assigned a member number.

• That member number is set on the user's hard drive in a cookie, by code contained in a second page.

• The next time that the member logs in to www.MyClub.org, the browser sends along the member number in the cookie to the server. The ASP/ADO code uses that number in the WHERE clause of a SQL statement and gets the member's zip code. Based on that zip code, ASP builds the rest of the page to feature events in the area of the member.

Prior to running through the techniques of using cookies with databases via ADO, let us briefly look at how to use cookies with just ASP. Again, if you are already using these types of commands, skip to the section titled "Using Cookies with ADO and a Database".

These techniques are explained in more depth in Beginning Active Server Pages (ISBN 1-861001-34-7) and Professional Active Server Pages (ISBN 1-861001-26-6), but I will run through them here. To set a persistent cookie you need three lines within ASP delimiters. The first line must go at the very beginning of the page, even before the<HTML> tag:

<%Response.Buffer=true%>
<HTML>

<BODY>
<%
  Response.Cookies("FirstCookie")="DataInCookie"
  Response.Cookies("FirstCookie").Expires = date + 365
%>

The first line is the trickiest. Normally ASP starts writing a header and then the HTML of the page as it is interpreting the ASP code. But the instructions to set a cookie must go into the header and if that header is already written then the cookie-setting commands will fail. So we must use <%Response.Buffer=true%> to tell ASP to wait on writing the page, because later in the page we want to write code into the header.

The first line of ASP code in the body sets the value of the data and the name of the cookie. However, cookies by default do not persist beyond the time the browser is open so we must specifically set an expiration date on the following line. In this case we pick up today's date, add a year's worth of days, and use that for the expiration date.

To read a cookie into a variable you need one line to get the cookie's data out of the Request object and into a variable. You can then use that variable as needed. There is no need to worry about buffering since you are not adding characters to the header.

<%
  Dim varCookie
  varCookie = Request.Cookies("FirstCookie")
  Response.Write varCookie
%>

In the above code we start by declaring the variable, then filling it. A following line writes the contents of the variable to the page. Reading a cookie does not require any buffering of the response, only setting a cookie requires page buffering.

Many students ask why the cookie data is first copied into a variable rather than used directly out of the Request object in a statement like the following:

Response.Write Request.Cookies("FirstCookie")

There is a rumor that Microsoft does not guarantee being able to extract cookie data from a request string more than once, however, if the data is extracted into a variable then the variable is, of course, available for the entire page. Although many programmers use the data directly without problems, I tend to take the more cautious approach. Don't be confused by this caveat. The cookie itself remains re-usable on the browser's hard disk. It is the copy of the cookie that was sent in the request that cannot be guaranteed for re-use. A subsequent request, with its new copy of the cookie, will be readable.

There is one last topic before getting into cookies with ADO. Sometimes we want to hold more than one datum in a cookie. This is done by using keys, which are like subfolders within a cookie. Each key has a name and a value.

With multiple keys the code is as follows:

<%Response.Buffer=true%>
<HTML>

<BODY>
<%
  Response.Cookies("SecondCookie")("FirstDatum")="FirstDataInSecondCookie"
  Response.Cookies("SecondCookie")("SecondDatum")="SecondDataInSecondCookie"
  Response.Cookies("SecondCookie")("ThirdDatum")="ThirdDataInSecondCookie"
  Response.Cookies("SecondCookie").Expires = date + 365
%>

We can retrieve the data from multiple keys within a cookie with the following code:

<%
  Dim varSecondCookie1
  Dim varSecondCookie2
  Dim varSecondCookie3
  varSecondCookie1 = Request.Cookies("SecondCookie")("FirstDatum")
  varSecondCookie2 = Request.Cookies("SecondCookie")("SecondDatum")
  varSecondCookie3 = Request.Cookies("SecondCookie")("ThirdDatum")
  Response.Write varSecondCookie1 & "<BR>"
  Response.Write varSecondCookie2 & "<BR>"
  Response.Write varSecondCookie3 & "<BR>"
%>

Notice in the above code that we follow the name of the cookie with a key name. Also note that expiration is set for the entire cookie, you cannot set different expirations for each key within a cookie. If you need to have different expirations then you must use different cookies, not different keys within one cookie.  

Try It Out – Setting and Reading a Cookie with ASP Alone

Considering the dates of birth in our sailors list, it looks like we should accommodate folks that need glasses. Sailors trying to use laptops on yachts, where the bright sun makes reading a LCD screen difficult, exacerbate this problem. So we want to give sailors the ability to pick one of three types of displays: small, medium and large. Since this parameter will be utilized with every page, and is not sensitive, we can improve server speed by storing the setting in a cookie rather than in our People table for look–up.

You will have to create three pages. The first will be a "Set Preference" form to ask the sailor for her or his preference in type size. A second page will be a "Cookie Setter" that stores the preference in a cookie and offers a button to open the home page. The third is the "Home Page Using Preference" that will read the cookie and use that data to set its type style.

If you haven't worked with HTML styles before, I suggest you study the following few ideas:

• Style attributes are set for levels of text, for example <p>, <H1>, etc.
  
• Text for the remainder of the document will be formatted according to the style of its level

• Styles are set within the tags <STYLE> and </STYLE>

• Style tags are placed in the head section of the page

• Style attributes are set as Level {AttributeName: value} such as the following:
  P {font–size: 14 pt}
  
• Multiple attributes are separated by semi colons as follows:
  P {font–size: 14 pt; color: black}
  
  You can get a much better feel for all the features of styles from Chapters 1 and 2 of Frank Boumphrey's book: Professional Style Sheets (ISBN 1-861001-65-7). This book also carries you through XML, an improved formatting technique.

The first page is a form that gets the font size preference from the user. The code for this page is available as 2726-11-Cook-TIO-01-SetPreferenceForm.htm:

<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726 chapt11 Cookies TIO 01 SetPreferences</TITLE>
</HEAD><BODY>

Please select the size font you prefer.
<FORM ACTION="2726-11-Cook-TIO-01-CookieSetter.asp" METHOD=get id=form1 NAME=form1>
<INPUT TYPE="radio" NAME="PrefFontSize" VALUE=small> Small
<INPUT TYPE="radio" NAME="PrefFontSize" VALUE=medium CHECKED> Medium
<INPUT TYPE="radio" NAME="PrefFontSize" VALUE=large> Large<BR><BR>
<INPUT TYPE=submit>
</FORM>

</BODY></HTML>

The above code produces the following form page:

Then we need the response page that actually sets the cookie. 2726-11-Cook-TIO-01-CookieSetter.asp contains the following code:

<%@ Language=VBScript%>
<%Response.Buffer=true%>
<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726 chapt11 Cookies TIO 01 Create Cookie that holds the Preferences</TITLE>
</HEAD><BODY>
<%
  varPrefFontSize = Request.Querystring("PrefFontSize")
  Response.Write "Your cookie holds a font size preference of " & varPrefFontSize & _ "<BR>"
    Response.Cookies("PrefFontSize")=varPrefFontSize
    Response.Cookies("PrefFontSize").Expires = date+365
%>
<A HREF="2726-11-Cook-TIO-01-HomePageUsingSizePreference.asp ">
Now that the font size is set, click here to go to the home page.</A>
</BODY></HTML>

The preceding cookie-setting code produces the screen below, if the visitor selected medium.

Now that the cookie is set we can use it in the following code for a home page, saved in file 2726-11-Cook-TIO-01-HomePageUsingPreference.asp:

<%@ Language=VBScript %>
<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726 chapt11 Cookies TIO 01 HomePage Using the Font Size Preferences</TITLE>
</HEAD><BODY>
<%
'-------------------
' the plan to set sailor preference in view
' get cookie
' use sailor preference in Select Case to set styles
'-------------------
' get cookie
'
  varPrefFontSize = Request.Cookies("PrefFontSize")
' next line for testing
' Response.Write "<HR>The cookie is " & varPrefFontSize & "<HR>"

'-------------------
' use sailor preference in Select Case to set styles
'
  Response.Write "<STYLE>"
  Select Case varPrefFontSize
    case "large"
      Response.Write "P {font-size: 26 pt}"
    case "medium"
      Response.Write "P {font-size: 16 pt}"
    case "small"
      Response.Write "P {font-size: 6 pt}"
  End Select
  Response.Write "</STYLE>"
%>
</HEAD>
<BODY>
<H2>Chapter 11 ASP-ADO and Cookies <BR><BR>
TIO #1 - Setting Page Styles According to Preferences</H2>
<P><P>Welcome to the Sailor Home Page.<BR><BR>
Your page is displayed as per your viewing preference</P>
</BODY></HTML>

The above home page code produces the screen below, again assuming that the medium size text was selected. 

How it Works – Setting and Reading a Cookie with ASP Alone

The first page is a simple HTML form. Remember that when using radio buttons on a form, one VALUE will be assigned to the NAME and then sent to the response page.

<FORM ACTION="2726-11-Cook-TIO-01-CookieSetter.asp" METHOD=get >
<INPUT TYPE="radio" NAME="PrefFontSize" VALUE=small> Small
<INPUT TYPE="radio" NAME="PrefFontSize" VALUE=medium CHECKED> Medium
<INPUT TYPE="radio" NAME="PrefFontSize" VALUE=large> Large<BR><BR>
<INPUT TYPE=submit>
</FORM>
</BODY></HTML>

The next page is the response page, as named in the ACTION attribute above. This page will be writing the cookie so we must be sure to buffer the page before starting the usual <HEAD> information:

<%@ Language=VBScript%>
<%Response.Buffer=true%>
<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726 chapt11 Cookies TIO#01 Create Cookie that holds the Preferences</TITLE>
</HEAD><BODY>

Now we pick the data of the cookie out of the Request object and assign it to a variable. I like to report back to the user what she has selected.

<%
  varPrefFontSize = Request.Querystring("PrefFontSize")
  Response.Write "Your cookie holds a font size preference of " &_
    varPrefFontSize & "<BR>"

Once safely ensconced in a variable, we can use the data as the text for a cookie. Since cookies by default expire when the browser closes, we need to explicitly set an expiration:

  Response.Cookies("PrefFontSize")=varPrefFontSize 
  Response.Cookies("PrefFontSize").Expires = date+365
%>

Now that the cookie is set we can give the user a hyperlink to the home page to try out the new convenience.

<A HREF="2726-11-Cook-TIO-01-HomePageUsingSizePreference.asp">
Now that the font size is set, click here to go to the home page.</A>

The home page that uses the cookie has two steps. After the housekeeping, here is the game plan laid out.

<%@ Language=VBScript %>
<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726 chapt11 Cookies TIO#01 HomePage Using the Font Size Preferences</TITLE>
</HEAD><BODY>
<%
'--------------------
' the plan to use the font size cookie preference on this page
' get cookie
' use sailor preference in Select Case to set styles

To pick up the cookie we query the Request object and use that result to fill a variable. You can see the artifact of development where I checked the cookie on the browser screen. I looked at the results while building the page, then at the time of deployment added an apostrophe to the beginning of the line which turned the command into a comment and thus prevented execution of the Response.Write. I like to leave these in the code because later, when there is another round of problems and troubleshooting, these tools are available by just deleting the leading apostrophe.

'--------------------
' get cookie
'
varPrefFontSize = Request.Cookies("PrefFontSize")
' next line for testing
'Response.Write "<HR>The cookie is " & varPrefFontSize & "<HR>"

Now we run a Select Case against the variable that contains the cookie contents. Note that we must write the <STYLE> and </STYLE>  tags regardless of the preference, so those are outside of the Select Case. Within the Select Case only one of the style definitions will be written.

'--------------------
' use sailor preference in Select Case to set styles
'
  Response.Write "<STYLE>"
  Select Case varPrefFontSize
    case "large"
      Response.Write "P {font-size: 26 pt}"
    case "medium"
      Response.Write "P {font-size: 16 pt}"
    case "small"
      Response.Write "P {font-size: 6 pt}"
  End Select
  Response.Write "</STYLE>"
%>

The remainder of the page contains simple HTML, which uses <p><p> styles that have been modified within the above code:

'--------------------
' use sailor preference in Select Case to set styles
'
Response.Write "<STYLE>"
Select Case varPrefFontSize
case "large"
Response.Write "P {font-size: 26 pt}"
case "medium"
Response.Write "P {font-size: 16 pt}"
case "small"
Response.Write "P {font-size: 6 pt}"
End Select
Response.Write "</STYLE>"
%>

Using Cookies with ADO and a Database

Now that we have reviewed using cookies with ASP we can see how to enhance those techniques with ASP–ADO. The syntax is direct, but students can be confused by how to handle the information across multiple pages. Setting a cookie and making a new record in a database is generally done in three pages.

The first page gathers information in a form, this page requires neither ASP nor ADO, just simple HTML forms. That information is passed to an ASP/ADO response page, which performs two jobs. It sets the cookie on the user's PC, and writes the information to the database. Since the user sees neither of those processes, I usually include some text that repeats the data back to the user, and notes that a cookie has been set and they have been entered in the database, along with a hyperlink button to request the third page.

As a robust test we then write a third page to test our process, usually by reading the cookie and using it to look up the visitor's information. I usually add a button here that allows the user to catch a mistake. That button takes the visitor to an editing screen to correct the errors.

Once a cookie is set we can use it in subsequent visits to link the visitor to our database. For example, we can use the ID to find out the sailor's state and then feature on the web page regattas proximal to that sailor.

Cookies are used to obtain specific information from a database by including them in a SQL statement WHERE clause. This is usually performed over two blocks of code, the first is to get the cookie into a variable, and the second is to use the contents of that variable in the WHERE clause of a SQL statement that gets data from the specific record of the visitor:

<%
  Dim varCookieHolder
  varCookieHolder=Request.Cookies("MyCookie")
  Dim oRecordSet
  Set oRecordSet = Server.CreateObject("ADODB.Recordset")
  sqltext = "SELECT * FROM MyTable WHERE RecordID=" &  varCookieHolder
  oRecordSet.Open sqltext, "DSN=MyDataSource"
  Response.Write "Data from Table is " & oRecordSet
 ("MyField")
%>

The most common problem here is failure of the SQL statement when the cookie's data is different from expectations. For example, since MyCookie data could hold an ID for a record that has been deleted, you should check that the resulting recordset has a record prior to trying to use it. An additional problem could be when there is no cookie in the request. This condition must be tested and resolved prior to running a SQL statement with an empty WHERE clause.

Try It Out – Setting a Cookie Using ASP–ADO

Our objective is to set a cookie with the user's ID number, then use that to look up the visitor's yacht club code in the database, and display that information on the home page. To make this example simpler, we will assume the visitors know their membership number from their membership card.

Firstly, let us look at the overall strategy that we will use. The four steps we will follow are:

• Get the visitor's membership number on a form.

• Second, we set a cookie that holds the visitor's ID number from the People table.

• Third we can use the ID from the cookie to find their record in the table.

• Lastly, from the correct record we can show the person's yacht club code.

This will require three pages. The first is a CookieSetterForm page that gathers the visitor's ID. The second page, CookieSetter, actually sets the cookie and confirms that act to the user. The third page, CookieHome, uses the emerging cookie and ADO to derive the name of the visitor's yacht club code.

The first page, in file 2726-11-Cook-TIO-02-CookiesWithADOCookieSetterForm.asp follows:

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726-11-TI-O2 Cookies With ADO CookieSetterForm</TITLE>
</HEAD>
<BODY>

<H1>Welcome to the Sailors Site, But First...</H1>
<P>Please provide your membership number so we can set your cookie.</P>
<FORM ACTION="2726-11-Cook-TIO-02-CookiesWithADOCookieSetter.asp" METHOD=post>
Please type your membership number here
<INPUT NAME='PeopleID'>
<INPUT TYPE=submit VALUE="Submit">
</FORM>
</BODY>
</HTML>

Which gives this result:

The second page, 2726-11-Cook-TIO-02-CookiesWithADOCookieSetter.asp, takes the data and uses it to set a cookie, as follows:

<%@ Language=VBScript %>
<%Response.Buffer=true%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726-11-TI-O2 Cookies With ADO CookieSetter</TITLE>
</HEAD>
<BODY>

<%
  varPeopleID = Request.Form("PeopleID")
'next line for testing
'Response.Write varpeopleID
  Response.Cookies("PeopleIDNumber")=varPeopleID
  Response.Cookies("PeopleIDNumber").Expires = Date + 366
%>
A cookie has been set with your membership ID number of <%=varPeopleID%><BR>
<A HREF="2726-11-Cook-TIO-O2-CookiesWithADOCookieHome.asp">
Click here to return to the home page</A>
</BODY>
</HTML>

The above code gives the following result:

Then we finish by clicking back to the home page, whose code is in file 2726-11-Cook-TIO-02-CookiesWithADOCookieHome.asp and below:

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726-11-TI-O2 Cookies With ADO Home</TITLE>
</HEAD>
<BODY>
<H1>Welcome to the Sailor's Home Site <BR><BR>
<%
  Dim oRS, varPeopleIDNumber
  varPeopleIDNumber = Request.Cookies("PeopleIDNumber")
  Set oRS=Server.CreateObject("ADODB.Recordset")
  sqltxt = "SELECT PeopleNameFirst, PeopleNameLast, 
    PeopleClubCode"
  sqltxt = sqltxt & " FROM People "
  sqltxt = sqltxt & " WHERE PeopleID = " &
    varPeopleIDNumber & ";"
  oRS.Open sqltxt, "DSN=sailors"
  Response.Write oRS("PeopleNameFirst") & " "
  Response.Write oRS("PeopleNameLast")
%>
</H1>
Your yacht club code is <%=oRS("PeopleClubCode")%>
</BODY>
</HTML>

The above code produces the following screen:

How it Works – Setting a Cookie Using ASP–ADO

The first page starts by informing the user that we need to set the cookie.

<%@ Language=VBScript %>
<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726-11-TI-O2 Cookies With ADO CookieSetterForm</TITLE>
</HEAD><BODY>
<H1>Welcome to the Sailors Site, But First...</H1>
<P>Please provide your membership number so we can set your cookie.</P>

Continuing on the first page, we set up the form with a single text input field named PeopleID. The Submit button will direct the browser to pull up the CookieSetter page.

<FORM ACTION="2726-11-Cook-TIO-02-CookiesWithADOCookieSetter.asp" METHOD=post>
Please type your membership number here
<INPUT NAME='PeopleID'>
<INPUT TYPE=submit VALUE="Submit">
</FORM></BODY></HTML>

The second page takes the data and uses it to set a cookie. Of course, we want our cookie to persist beyond this session, so we set an expiry date.

<%@ Language=VBScript %>
<%Response.Buffer=true%>
…
<TITLE>2726-11-TI-O2 Cookies With ADO CookieSetter</TITLE>
</HEAD><BODY>
<%
  varPeopleID = Request.Form("PeopleID")
'Response.Write varpeopleID
  Response.Cookies("PeopleIDNumber")=varPeopleID
  Response.Cookies("PeopleIDNumber").Expires = Date + 366
%>
A cookie has been set with your membership ID number of <%=varPeopleID%><BR>
<A HREF="2726-11-Cook-TIO-O2-CookiesWithADOCookieHome.asp">
Click here to return to the home page</A>
</BODY></HTML>

Now, in the home page, we can use ADO to use the cookie to look up a specific person's names and yacht club code. We start with the usual housekeeping, and then read the cookie from the Request object into a variable:

<TITLE>2726-11-TI-O2 Cookies With ADO Home</TITLE>
</HEAD>
<BODY>
<H1>Welcome to the Sailor's Home Site <BR><BR>
<%
  Dim oRS, varPeopleIDNumber
  varPeopleIDNumber = Request.Cookies("PeopleIDNumber")

Then we create a SQL statement that has the cookie data in the WHERE clause, as in the lines below. Note that I have written a space both after People and before WHERE. That puts two spaces in the SQL statement when only one is needed. One of the most common student mistakes occurs when a space is left out of that position. I suggest that when students are building multi-line SQL statements they put a space both at the end of the FROM clause and the beginning of the WHERE clause. If, in the course of editing, one is removed, the statement will still have the other. SQL will never fail because of the extra space, so "better spaced than sorry".

  Set oRS=Server.CreateObject("ADODB.Recordset")
  sqltxt = "SELECT PeopleNameFirst, PeopleNameLast, PeopleClubCode"
  sqltxt = sqltxt & " FROM People "
  sqltxt = sqltxt & " WHERE PeopleID = " & varPeopleIDNumber & ";"

Finally, we use that SQL statement to open and read from a record set, as follows:

  oRS.Open sqltxt, "DSN=sailors"
  Response.Write oRS("PeopleNameFirst") & " "
  Response.Write oRS("PeopleNameLast")
%>
</H1>
Your yacht club code is <%=oRS("PeopleClubCode")%>
</BODY></HTML>

There are two cases for setting a cookie. The first is that no cookie existed because the person was never registered, that is, they have no record in the database. But a frequent second type of case is that a person has a record in the People table but they have lost their cookie. This could be for many reasons, as discussed earlier in the chapter. In this case we want to reset a cookie.

In this second case, it is nice to give people a way to reset their cookie quickly and easily, without reentering all of their information in the People table. In fact, we specifically want to avoid the latter since re-entering their data could easily lead to duplicate records for people.

In other words, we don't want to enter this person as a new record in the database like we would when first setting a cookie; rather we want to identify their existing record in the database and use that to set a cookie.

The solution to the second problem is to build a page that gives cookie-less visitors several options. Firstly, they could pick their name out of a drop-down list of names already in your People table. You can also give the visitor the option of visiting without a cookie. The third is that they can register, which will respond by both adding them as a record in the People table and setting a cookie on their hard drive. A fourth option is to ask the visitor to sign in at the beginning of each visit. The sign-in could be by name, member ID or a field like Email address. Although tedious, some visitors prefer signing in to setting a cookie.

You must also weigh up which options you want to offer in view of your security. In an Internet site, you would not want to reveal the names of all your visitors in a drop-down box. Likewise, with more than a few score members, you could not offer a drop-down list of names, and would have to gather the name by a text field and do a match with the database. But let us cover an example here for a departmental intranet with a few dozen users.

There are four pages or parts of pages that are needed for this process:

• First, our cookie-using home page must be modified to detect a missing cookie.

• Second, we must create a form for resetting a cookie, which offers a list of members.

• Third, we need a page that will set a cookie based on the selection in the above page.

• Last, when we reset a cookie, we will provide to the user a confirmation page.

Try It Out – Resetting a Cookie

Our objective is to show on the home page the visitor's name and professional class, based on the person's ID stored as a cookie. We will do that by first reviewing the home page from the last Try It Out, which does not have a cookie check. Then we will modify that home page to check for cookies. If there is no cookie, then the visitor will be shunted to ResetCookieForm.asp and that leads to ResetCookieResponse.asp.

One note on this exercise. In the course of this exercise you will probably want to delete a cookie for testing. After deleting the cookie it is best to re-start your browser.

You can delete a cookie by hand from IE by going to C:\Windows\cookies . It will probably be named UserName@SiteName . You can also sort by date and you will see your cookie as the most recently changed. In Netscape a cookie is located at: c:\program files\netscape\users\<profile name>\cookies.txt

The code for a typical home page that uses a cookie would look like:

<%@ Language=VBScript %>
<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726-11-TI-O3 Reset Cookie Home Page No Check</TITLE>
</HEAD><BODY>
<H1>Welcome to the Sailor's Home Page</H1>
<P>(no cookie check)</P>
<%
  Dim varPeopleID
  Dim oRS
  varID=Request.Cookies("PeopleID")
  sqltxt = "SELECT PeopleNameLast, PeopleNameFirst, "
  sqltxt = sqltxt & "PeopleProfessionalClass FROM People "
  sqltxt = sqltxt & " WHERE PeopleID =" & varID & ";"
  Set oRS = Server.CreateObject("ADODB.Recordset")
  oRS.open sqltxt, "DSN=Sailors"
  Response.Write "Welcome " & oRS("PeopleNameFirst")
  Response.Write " " & oRS("PeopleNameLast") & "<BR>"
  Response.Write "<P>We have you registered as "
  Response.Write " of Professional Class&nbsp;"
  Response.Write oRS("PeopleProfessionalClass")
%>
</BODY></HTML>

And the above code (available as file 2726-11-Cook-TI-03-ResetCookieHomePageNoCheck.asp) would produce the following page:

But when we consider that cookies may be unusable or unavailable we have to modify that home page to detect the absence of a cookie. If the cookie does not exist then the user is notified and asked to click to the Cookie Reset page, as follows. This code is available as file 2726-11-TI-03-ResetCookieHomePageWithCheck.asp:

<HTML>
<HEAD>
<TITLE>2726-11-TI-O3 Reset Cookie Home Page With Check</TITLE>
</HEAD>
<BODY>
<%
  Dim varPeopleID, oRS
  varPeopleID=Request.Cookies("PeopleID")
  If varPeopleID = "" Then
    Response.Write "<H1>This is the Sailor's Home Page, but...</H1>"
    Response.Write "<P>(failed cookie check)</P>"
    Response.Write "Sorry, we can not find your cookie<BR>"
    Response.Write "<A href='2726-11-TI-O3-ResetCookieForm.asp'>"
    Response.Write "Please click here </A>to re-set your cookie"
  Else
    Response.Write "<H1>Welcome to the Sailor's Home Page</H1>"
    Response.Write "<P>(passed cookie check)</P>"
    Set oRS = Server.CreateObject("ADODB.Recordset")
    sqltxt = "SELECT PeopleNameLast, PeopleNameFirst, "
    sqltxt = sqltxt & "PeopleProfessionalClass "
    sqltxt = sqltxt & " FROM People WHERE PeopleID=" & varPeopleID & ";"
    oRS.Open sqltxt, "DSN=Sailors"
    Response.Write "<P>We have you, " & oRS("PeopleNameFirst") & " "
    Response.Write oRS("PeopleNameLast") & ", "
    Response.Write "registered in Professional Class&nbsp;"
    Response.Write oRS("PeopleProfessionalClass") & "</P>"
  End If
%>
</BODY></HTML>

The above cookie-checking page gives the same result as the last page if a cookie is present, but if the cookie is missing, then we get the following:

The user would click to reset a new cookie, and would hit the following code, file 2726-11-TI-03-ResetCookieForm.asp, to allow selection of the cookie to set:

<%@ Language=VBScript %>
<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726-11-TI-O3 Reset Cookie Form</TITLE>
</HEAD><BODY>
<H1>Reset Cookie</H1>
<P>Sorry, but we are having problems identifying you.</P>
<P>Please select your name from the list below</P>
<FORM ACTION="2726-11-TI-O3-ResetCookieResponse.asp" METHOD=get>
<SELECT NAME="PeopleName">

<%
  Dim oRSPeople
  Set oRSPeople=Server.CreateObject("ADODB.Recordset")
  sqltxt = "Select PeopleNameLast, PeopleNameFirst from People"
  sqltxt = sqltxt & " ORDER BY PeopleNameLast;"
  oRSPeople.Open sqltxt, "DSN=Sailors"
  Do while not oRSPeople.EOF
    Response.Write "<OPTION NAME=>"
    Response.Write orsPeople("PeopleNameLast") & ", "
    Response.Write orsPeople("PeopleNameFirst") & "</OPTION>"
    oRSPeople.MoveNext
  Loop
%>
</SELECT>
<INPUT TYPE="Submit">
<INPUT TYPE="Reset">
</FORM></BODY></HTML>

Which produces the following page:

The form needs a response, which in this case actually sets the cookie, and we would confirm that by informing the user of setting a cookie and giving her a hyperlink back to the home page, where she can use the new cookie. The code for this page is taken from 2726-11-TI-03-ResetCookieResponse.asp and is listed below:

<%@ Language=VBScript%>
<%Response.Buffer = true%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726-11-TI-O3 Reset Cookie Response</TITLE>
</HEAD>
<BODY>

<P>&nbsp;</P>
<H1>This page will re-set your cookie </H1>
<%
  varPeopleNameTotal = Request.Querystring("PeopleName")
  varPeopleNameLast = left(varPeopleNameTotal,instr
   (varPeopleNameTotal, ",")-1)
  varPeopleNameFirst = right(varPeopleNameTotal,len
   (varPeopleNameTotal)- instr(varPeopleNameTotal, ",")-1)
  Dim oRSPeopleIDLookUp
  Set oRSPeopleIDLookUp=Server.CreateObject
   ("ADODB.Recordset")
  sqltxt = "Select PeopleID from People"
  sqltxt = sqltxt & " WHERE PeopleNameLast = '" &
    varpeoplenamelast & "'"
  sqltxt = sqltxt & " AND PeopleNameFirst = '" &
    varpeoplenamefirst & "';"
  oRSPeopleIDLookUp.Open sqltxt, "DSN=Sailors"
  Response.Cookies("PeopleID") = oRSPeopleIDLookUp
    ("PeopleID")
  Response.Cookies("PeopleID").Expires = date + 365
%>
We have re-set your cookie, the identification is now for:<BR><BR>
<%=varPeopleNameFirst%>&nbsp;<%=varPeopleNameLast%>
with the ID number <%=oRSPeopleIDLookUp("PeopleID")%>
<BR><BR>Click here to re-try
<A HREF="2726-11-TI-O3-ResetCookieHomePageWithCheck.asp">
homepage</A>
with your new cookie
</BODY>
</HTML>

Which gives the user the following page, reporting back to the user that the cookie has been set.

How It Works – Resetting a Cookie

The home page in its original form could not handle visitors without a cookie. The following code would put a blank into varPeopleID. That causes a logical failure, but not an ASP error since VBScript accepts putting "nothing" into a variable.

<TITLE>2726-11-TI-O3 Reset Cookie Home Page No Check</TITLE>
…
varID=Request.Cookies("PeopleID")

However, the following code would fail because SQL will not accept a null value for the expression of the WHERE clause.

sqltxt = "SELECT PeopleNameLast, PeopleNameFirst, "
sqltxt = sqltxt & "PeopleProfessionalClass FROM People "
sqltxt = sqltxt & " WHERE PeopleID =" & varID & ";"
set oRS = Server.CreateObject("ADODB.Recordset")
oRS.open sqltxt, "DSN=Sailors"

So we use the code of the improved page, which has an If to determine if the cookie is empty:

<TITLE>2726-11-TI-O3 Reset Cookie Home Page With Check</TITLE>
…
Dim varPeopleID, oRS
varPeopleID=Request.Cookies("PeopleID")
If varPeopleID = "" Then

In the following code we determine if the cookie is empty, and if so give the "Sorry but … " message with a
hyperlink to a form to reset the cookie (2726–11–TI–O3-ResetCookieForm.asp). After getting this to
work, you may consider changing the code to automatically redirect the user using Response.Buffer=true and Response.Redirect. However, I find that students understand the concepts better on their first try by clicking through the pages.

  Response.Write "<H1>This is the Sailor's Home Page, but...</H1>"
  Response.Write "<P>(failed cookie check)</P>"
  Response.Write "Sorry, we can not find your cookie<BR>"
  Response.Write "<A HREF='2726-11-TI-O3-ResetCookieForm.asp'>"
  Response.Write "Please click here </A>to reset your cookie"

If the cookie exists we perform the following code to get the name and professional class of the visitor from the database. Note that we use the cookie as the expression of the WHERE clause in the SQL
statement. Since the PersonID is a number, we do not concatenate quotes around the variable in the last line below:

Else
  Response.Write "<H1>Welcome to the Sailor's Home Page</H1>"
  Response.Write "<P>(passed cookie check)</P>"
  Set oRS = Server.CreateObject("ADODB.Recordset")
  sqltxt = "SELECT PeopleNameLast, PeopleNameFirst, "
  sqltxt = sqltxt & "PeopleProfessionalClass "
  sqltxt = sqltxt & " FROM People WHERE PeopleID=" & varPeopleID & ";"

Then we can write the text of the home page using data from the recordset:

oRS.open sqltxt, "DSN=Sailors"
Response.Write "<P>We have you, " & oRS("PeopleNameFirst") & " "
Response.Write oRS("PeopleNameLast") & ", "
Response.Write "registered in Professional Class&nbsp;"
Response.Write oRS("PeopleProfessionalClass") & "</P>"
End If
%>

But let us get back to the path of visitors without a cookie. They got the "Sorry but…" message on the home page and click to come to 2726–11–TI–O3-ResetCookieForm.asp. Our objective is to set up a
list of the available names from which to choose. The first part of that code, below, creates a form and a list box using the <SELECT> tag.

<TITLE>2726-11-TI-O3 Reset Cookie Form</TITLE>

<FORM ACTION="2726-11-TI-O3-ResetCookieResponse.asp" METHOD=get>
<SELECT NAME="PeopleName">

Then we create a recordset of the members so we can put them into a list box:

<%
  Dim oRSPeople
  Set oRSPeople=Server.CreateObject("ADODB.Recordset")
  sqltxt = "Select PeopleNameLast, PeopleNameFirst from People"
  sqltxt = sqltxt & " ORDER BY PeopleNameLast;"
  oRSPeople.Open sqltxt, "DSN=Sailors"

Then we actually populate the list box by looping through that recordset creating an <OPTION> tag for each member. As always, be sure to include the RS.MoveNext in the loop.

  Do while not oRSPeople.EOF
    Response.Write "<OPTION NAME= >"
    Response.Write orsPeople("PeopleNameLast") & ", "
    Response.Write orsPeople("PeopleNameFirst") & "</OPTION>"
    oRSPeople.MoveNext
  Loop
%>
</SELECT>

After the user has made a selection, the Action of the FORM tag rolls us to 2726–11–TI–O3-ResetCookieResponse.asp. Since we will be setting a cookie we must start by turning on the response
buffer:

<%@ Language=VBScript%>
<%Response.Buffer = true%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>2726-11-TI-O3 Reset Cookie Response</TITLE>

Our incoming data is the name of the member selected from the list box, in the form of last name, and comma, and the first name. We need to not only harvest that data from the querystring, but also parse apart the first and last names so we can compare the names with the database and extract the person ID.
Our clue to separating the names is the comma. The third line below puts into the last name variable all of the text to the left of the comma:

<%
  varPeopleNameTotal = Request.Querystring("PeopleName")
  varPeopleNameLast = left(varPeopleNameTotal,instr(varPeopleNameTotal, ",")-1)

The line below puts the first name into a variable:

varPeopleNameFirst = right(varPeopleNameTotal,len(varPeopleNameTotal)-instr(varPeopleNameTotal, ",")-1)

Now we can create a recordset that selects out the one record matching the first and last names. Note that we are applying two tests in the WHERE clause; one for the last name and one for the first name.

Dim oRSPeopleIDLookUp
Set oRSPeopleIDLookUp=Server.CreateObject("ADODB.Recordset")

sqltxt = "Select PeopleID from People"
sqltxt = sqltxt & " WHERE PeopleNameLast = '" & varpeoplenamelast & "'"
sqltxt = sqltxt & " AND PeopleNameFirst = '" & varpeoplenamefirst & "';"
oRSPeopleIDLookUp.Open sqltxt, "DSN=Sailors"

We can now use the PeopleID data, from the one correct record sitting in the recordset, to write a cookie.

  Response.Cookies("PeopleID") = oRSPeopleIDLookUp("PeopleID")
  Response.Cookies("PeopleID").Expires = date + 365
%>

Lastly, we wrap up with some notices to the user about what we have done:

We have reset your cookie, the identification is now for:<BR><BR>
<%=varPeopleNameFirst%>&nbsp;<%=varPeopleNameLast%>
with the ID number <%=oRSPeopleIDLookUp("PeopleID")%>
<BR><BR>Click here to re-try
<A HREF="2726-11-TI-O3-ResetCookieHomePageWithCheck.asp">
homepage</A>
with your new cookie
</BODY></HTML>

Summary

Cookies allow a site programmer to overcome one of the major problems of the Internet – the lack of identification of visitors. However, cookies are only usable with the approval of the visitor. Programmers must write code that will compensate for unusable or non-existent cookies.

Cookies are ideal for holding information about how a page should be presented. For example, data such as a zip code can be applied, with ADO, to a database, in order to build a page optimized for the visitor's
neighborhood. In this technique, frequently used data is stored directly in the cookie.

An alternative approach is to hold only an ID number in the cookie. That datum is then used to select the visitor's record from all those in a database. From that record, information can be extracted about the
visitor for use in the site. That data can be used to customize pages for the visitor.

Exercises

These exercises use one or both of the two sample databases available from the Apress website. The structures of the databases are described in Appendix A for Sailors.mdb and Appendix B for
Clothier.mdb.

1. Some visitors to the Clothier site prefer to see the items listed alphabetically by type (hat, shirt, etc.), other visitors prefer ordering by department (Men's Sportswear, Women's Formals).
   Create a way for visitors to set a preference, and then create a products page that lists items in a table in the preferred order.
   
2. Build on the above exercise. Within the grouping of type or department, allow the visitor to set a preference of ordering by price, either increasing or decreasing.