Kevin Spencer

August 2002

Contents

Introduction
Active Server Pages and Databases
Microsoft FrontPage and ASP

Introduction

Many people are intimidated and confused by the concept of databases. So, to begin, let us discuss what databases are and how they work, and remove the mystery that surrounds them. (12 pages)

Note   For security information about working with Web sites and Web servers, see FrontPage Security Best Practices.

What is a database?

Simply put, a database is a container for storing, organizing, retrieving, and manipulating data. Data is simply information. It can be any kind of information: numbers, text, dates, media (such as pictures, sounds, and movies), and even executable code.

Most people have a difficult time differentiating between the database itself and the interface used to work with the data. This is probably due to the fact that almost all database management systems include interface software with the database. In some cases, such as Microsoft® Access, the interface software and the database reside in the same file (the .mdb file). However, there is an important difference between the database (the storage mechanism) and the interface (the front end, or mechanism used for accessing that data). The interface for a database is a software application of one kind or another. It can be a program created by the database vendor, such as the Microsoft SQL Server™ Enterprise Manager, or a different kind of interface such as a command-line tool or an Active Server Pages (ASP)–based Web application.

In computers, there are basically two kinds of data: volatile and persistent. Volatile data is stored in memory and disappears when it goes "out of scope" in one way or another. Persistent data is stored persistently in some medium (magnetic disc, CD, DVD, etc.) that can hold onto the data indefinitely. A database provides a way to persist (save) data in such a medium.

The file system on a computer is a primitive database of sorts, as the files are a storage mechanism for data (file content). File management system utilities (such as Microsoft Windows® Explorer, My Computer, etc.) present an interface for retrieving, storing, and working with that data.

A Web site is data

A Web site on the Internet is made up entirely of data. The Web pages are stored in the file system and streamed to the browser by the Web server as a stream of bytes. However, simply using Hypertext Markup Language (HTML) files for a Web site has its limitations:

• Static content. An HTML file has severely limited capabilities for delivering dynamic content. With the use of JavaScript, you can do a little to add dynamic content to a Web page. But for security reasons and because the code executes in the client browser, JavaScript is limited in what it can do. An HTML page with JavaScript is basically a static document.
• Web site maintenance. Because HTML files are static content and because "content is king" on the Internet, maintaining a Web site with static content is a constant job. It requires nearly constant manual editing of the HTML pages. And a Web site typically starts small but ends up large, particularly if that site is maintained by a business. Even with productivity software such as the Microsoft FrontPage® Web site creation and management tool, the amount of working hours necessary to maintain a site of static content can be enormous in the long run.
• Web site organization. As a Web site grows, keeping everything organized can become a daunting task. Take a site that has a catalog in it, for example. As the catalog grows, many pages are added, modified, and removed during the lifetime of the catalog. I have seen Web sites that have thousands of static documents in a catalog. Because a file system is limited in its organizational capabilities (basically limited to the use of folders to organize the documents), working with all those documents is not at all cost effective.

Typical uses of Internet databases

Typical uses for dynamic, database-driven content in Web sites today include:

• Product catalog/shopping cart applications
• Collecting customer information and feedback
• Providing users with personalized Web page content
• Employment services
• Dating services
• Discussion forums
• Providing access to business data for businesses in multiple locations
• Travel services
• Auction Webs
• Providing the ability to easily change the layout and content of a Web site using a browser

The solution

If your Web site has any kind of content that needs to be changed frequently (and whose doesn't?), using a database back end to organize and deliver content is much more cost efficient and productive than using static HTML pages. By combining the database back end with active server technologies such as ASP, you can create a Web site that automates most of the work for you. It requires a higher investment of time and money at first, but more than pays for itself in the long run.

Active Server Pages and Databases

In the mid-1990s, Microsoft realized the need for server-side technology that provides the ability to deliver dynamic content in a Web page easily—and developed Active Server Pages. While the Common Gateway Interface (CGI) had been around for a number of years, it had some limitations that made it expensive and difficult to work with. Both technologies employ the idea that executable code can be developed to generate HTML and stream it to the browser. However, ASP has several advantages:

• Executable code and HTML reside in the same script. By combining HTML and executable scripting in a "scripted Web page," much of the HTML content for a page can be rendered literally in the page, making for a more readable script and separating executable code from HTML content. With WYSIWYG (what you see is what you get) tools such as Microsoft FrontPage, HTML can be rendered quickly and easily.
• ASP supports common scripting languages. By default, ASP employs Microsoft Visual Basic Scripting Edition (VBScript), which is a stripped-down version of the popular Visual Basic development system. However, ASP also can employ JavaScript and even Perl, depending on the programmer's preference.
• COM components extend the capabilities of ASP. The Component Object Model (COM) was developed by Microsoft around the same time as ASP was. COM objects are dynamic-link libraries (DLLs) that have a common programming interface for client applications to use. ASP has the ability to work with COM components, which extend the capabilities of ASP to be able to perform just about any task on the server. COM objects often are used to generate e-mail, work with the server's file system or network, and even work with databases.

ActiveX Data Objects

ADO, or ActiveX® Data Objects, is a COM component that was developed in the mid-1990s by Microsoft, as part of its Universal Data Access initiative. ADO addresses the issue of the many databases that are available, each with its own proprietary methods for storing, retrieving, and manipulating data. In principle, all databases perform the same types of operations. ADO provides a single programming interface for working with a huge variety of databases, negating the need for the developer to know how the database works internally. All that is necessary is for the developer to provide some information that tells ADO what kind of database to connect to and where the database is located. ADO communicates with databases using Structured Query Language (SQL), a language that was developed to provide a single, text-based, non–platform-specific language for working with all kinds of databases.

ADO communicates with the database, and ASP provides a dynamic Web interface for working with the data.

Microsoft FrontPage and ASP

Microsoft FrontPage 98 introduced the Database Region Wizard (renamed the Database Interface Wizard, or DIW, in FrontPage 2002) to its suite of productivity tools. This FrontPage component writes ASP/ADO code with HTML to automate the creation of database-connective pages. It was designed with typical database activities in mind and can provide a quick and relatively easy Internet database application in minutes. Because DIW uses ASP and ADO to communicate with databases, it can connect to any Open Database Connectivity (ODBC)–compliant database, which means that it can connect to about 95 percent of databases manufactured today.

The Database Interface Wizard is somewhat limited in what it can create. For most database applications, DIW can do a good job of getting the basic parts in place and even do a nice layout. But you can go further and tweak the generated ASP/HTML code if you learn a bit about ASP scripting and HTML. In fact, you don't even need to use DIW to develop ASP applications with FrontPage. ASP/ADO is powerful technology. If you can program, you can do virtually anything with databases using ASP and ADO. As a professional programmer, I've used FrontPage to develop ASP/ADO applications for years. I didn't use DIW because the applications that I was developing were more complex than the wizard could provide. But I used FrontPage to create the HTML in the page and added ASP scripting afterward. The ability to create HTML in the WYSIWYG editor increased my productivity tremendously.

However, if you're not a programmer, you can get a good-looking basic database application up and running with the Database Interface Wizard in less than an hour.

Understanding the Database Interface Wizard

The Database Interface Wizard creates a Web interface for a database. If you recall from the beginning of this article, I noted that databases can have different front ends, or interfaces. DIW creates ASP pages, which are the interface, and ADO is the technology that the interface uses to connect and interact with the database.

The Database Interface Wizard creates one of three basic types of interface:

• Results Page. This is a page that sends a SQL SELECT query to the database and returns the results in whatever format you choose. It can be used to display information of many different kinds, which can be filtered in any number of ways.
• Submission Form. This page is for collecting data from the user using an HTML form. It sends a SQL INSERT statement into the database.
• Database Editor. This page is used to edit data (SQL UPDATE) in the database. It also can be used to add new records (SQL INSERT).

These pages can be combined in different ways to create most types of database interface. After the ASP/ADO/HTML code is generated in the ASP page, you can edit the HTML and even tweak the ASP code if you desire.

The Database Interface Wizard also can be used to create a Microsoft Access database, even if you don't have the Access program. Remember that only the database file is needed to work with ASP. And remember that you can connect to any kind of ODBC-compliant database with DIW.

For more information about using the FrontPage Database Integration Wizard, see the following links:

• Database Power with Microsoft FrontPage 2002
• Data-enabling Webs with Microsoft FrontPage 2002
• Using the Database Interface Wizard in FrontPage 2002

Moving beyond the wizard

As mentioned earlier, ASP and ADO are capable of doing almost anything with a database. Even if you wish to create complex integrated applications, you can still use FrontPage as your ASP editor. You can even use the Database Interface Wizard to create your database connection. Then you can hand-code ASP into your Web and create a world-class Internet application. I have used FrontPage for years to do professional Internet database applications. You can easily crank out your basic HTML layout in the WYSIWYG editor, switch to HTML view, and add in your active server scripting.

To be most successful in this endeavor, it is best to architect your ASP application using a three-tier model, with an interface tier (the HTML that the user interacts with in the browser), a business tier (the ASP scripting that connects to the database and renders data in the HTML interface), and a database tier. In practical terms, using FrontPage, this occurs in three steps:

1. Create your database and database objects (tables, stored procedures, etc.).
2. Create your interface in FrontPage Editor as HTML, in Normal view.
3. Add ASP scripting to your interface in FrontPage Editor, in HTML view.

Note: FrontPage is a powerful WYSIWYG HTML-development tool and uses the browser Document Object Model to make sure that your HTML is correctly formed. When FrontPage encounters badly formed HTML, it generally tries to correct it, although you can configure it to leave your HTML alone. As an ASP script contains a mixture of scripting and HTML, you can write an ASP script so that it "completes" some incomplete HTML in the page, and therefore, after the script is generated, the HTML it streams to the browser is perfectly legal. FrontPage can't interpret the ASP scripting in the page, and therefore evaluates only the HTML that is in the page without the scripting for correctness. However, if you design your ASP pages correctly, separating executable code from content as much as possible, you can continue to work with the HTML interface in Normal view, which enhances your productivity greatly. Otherwise, you may end up having to edit both your ASP scripting and HTML code in HTML view (or a text editor), which seriously slows down your ability to work with the HTML—and thus your productivity.

Sample Application: Web Site Survey

As an example, let's take a look at gathering information about users. Typically, this involves using a form on your Web site to gather the information. By using the Microsoft FrontPage Save Results component, you can have the data collected to a file, sent to you in an e-mail message, or entered into a database. But we want to collect the data in the database, e-mail the user with a confirmation, and add personalized dynamic content to the user's pages when the user returns. In addition, we want to automate the sending of a newsletter to those who request it in the survey.

For this project, we will be using FrontPage 2002 and Access 2002 on a Windows 2000 Server–based platform running Internet Information Services (IIS) version 5.5.

We begin with the design of the Access database:

After creating the database, I import the .mdb file into our FrontPage Web. When I drag the file into the Web, FrontPage recognizes the file as an Access database and offers to create a connection for me, which I graciously allow (saves me the trouble of creating and editing the Global.asa file). FrontPage also recommends putting the file in a folder named "fpdb," which is fine with me. However, from this point on, I will do all my coding by hand.

A few words about database security might be appropriate here. A file-based database, such as an Access .mdb file, is just that: a file. When you put a file into your Web structure, the file usually can be downloaded by typing the path to the file in the browser (e.g., http://localhost/survey/fpdb/survey.mdb). When FrontPage imports and sets up a connection to an Access database, it does several things to make sure that the file is not "hacked" in this way:

• It removes "browse" permission for the fpdb folder, which prevents anyone from using their browser to find out the contents of the folder.
• It removes "read" permission for the Anonymous Internet user account, which is the user account that ASP runs under, preventing anyone from typing the URL to the database file in order to download it.

It is important to make sure that these permissions are set for the folder in which you place your database file, if you are using a file-based database. Another alternative, if you set up your connection by hand, is to place the database file outside the Web folder structure. With ODBC and OLE DB, the physical location of the database does not have to be inside the Web structure. It can be anywhere on the local area network of the Web server.

Also, FrontPage disallows server permissions to download the Global.asa file (in the root folder of the Web) containing the connection string. The connection string contains information about the database that you don't want users to see. This is particularly important when using a database server, such as Microsoft SQL Server, which can be accessed across the Internet. Make sure that Global.asa is disallowed for download in IIS.

Afterwards, I make note of the connection string in the Global.asa file, because I will be referencing it in my code:

Application("Survey_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/survey.mdb"

Now we want to create a form to collect data from the users, using FrontPage. For this purpose, I want the user to be able to input data and, after submitting it, have the ability to make changes. So I name the file with a .asp extension: SurveyForm.asp.

Now we're ready to add the ASP scripting. Because this page will be used for both entering and editing data, we will use a conditional statement to determine what to do. I add the following code to the top of the page, above the HTML (see my comments in italic for explanation):

<!--#INCLUDE FILE="adovbs.inc" -->

Adovbs.inc is a Microsoft include file for ADO constant definitions.

<%
' ** Prevents browser page caching
Response.Expires = 0

Global variable definitions

Dim strSQL, objConn, objRS, strUserName, objMail, strBody, strConfirm
' ** Form Fields variables
Dim strID, strFirstName, strLastName, strEmail
Dim strCity, strState, strZip, intAge
Dim intSex, strHowHeard, strConnectionType, strMore
Dim blnNewsletter, strComments

Subs and functions

This Sub gets the values from the Request.Form collection and puts them into global variables, which are used to in our code and to populate the form after submission.

Sub GetFormFields
strFirstName = Request.Form("FirstName")
strLastName = Request.Form("LastName")
strEmail = Request.Form("Email")
strCity = Request.Form("City")
strState = Request.Form("State")
strZip = Request.Form("Zip")
intAge = CInt(Request.Form("Age"))
intSex = CInt(Request.Form("Sex"))
strHowHeard = Request.Form("HowHeard")
strConnectionType = Request.Form("ConnectionType")
strMore = Request.Form("More")
blnNewsletter = GetBool(Request.Form("Newsletter"))
strComments = Request.Form("Comments")
End Sub

SQL uses the single quote as a string delimiter. This function escapes single quotes in a SQL string by doubling them.

' ** This function escapes single quotes in a string
' ** to be used in a SQL Statement 
Function SQLEscape(str)
SQLEscape = Replace(str, "'", "''")
End Function

An HTML checkbox has one of two possible values. If unchecked, the value is a blank string. If checked, the value is the "value" property of the checkbox. This function returns a 1 if the checkbox is checked, and a 0 (False) if not. In a database bit field, 0 is False, and 1 is true. This function evaluates the checkbox and returns a value which we can use in the database.

' ** This function returns a 1 or 0 for a checkbox value
Function GetBool(strValue)
if strValue = "" then
   GetBool = 0
else
   GetBool = 1
end if
End Function

Program execution code begins here. We can check whether the form has been posted by looking for the ID value from a posted (INSERTed) record, which will be stored in a hidden field in the form.

' ** This hidden form field contains the ID
' ** (AutoNumber) of an INSERTed record
' ** If it's not blank, we're doing an "UPDATE"
strID = Request.Form("ID")
if strID <> "" then

If the form has been posted, we need to use the GetFormFields Sub to populate our form field variables.

GetFormFields
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("Survey_ConnectionString")

We build the query string here, making sure to use the SQLEscape() function to escape any single quotes that may be in certain form fields, which do not restrict string input.

strSQL = "UPDATE tblSurvey SET FirstName = '" &_
SQLEscape(strFirstName) &_
   "', LastName = '" & SQLEscape(strLastName) &_
"', Email = '" &_
SQLEscape(strEmail) & "' , City = '" &_
SQLEscape(strCity) & "', State = '" & strState &_
"', Zip = '" & strZip & "', Age = " & intAge &_
", Sex = " & intSex & ", HowHeard = '" & strHowHeard &_
"', ConnectionType = '" & strConnectionType &_
"', More = '" & strMore & "', Newsletter = " &_
blnNewsletter & ", Comments = '" & SQLEscape(strComments) &_
"' WHERE ID = " & strID
objConn.Execute(strSQL)
objConn.Close
Set objConn = Nothing

For sending e-mail, we use the built-in IIS COM object called Collaborative Data Objects for Windows NT Server (CDONTS). It uses the local Simple Mail Transfer Protocol (SMTP) server to send the e-mail message.

' ** Now Send the email
if Request.Form("FirstName") <> "" then
   strUserName = Request.Form("FirstName")
elseif Request.Form("Sex") <> "" then
   strUsername = "Sir"
else
   strUserName = "Ma'am"
end if
strBody = "Dear " & strUserName & "," & vbCrLf & vbCrLf &_
   "Thank you for your input. We hope that your input " &_
   "will help us " &_
   "to serve you better." & vbCrLf & vbCrLf &_
   "Regards, " & vbCrLf & VbCrLf &_
   "Uncle Chutney"
Set objMail = CreateObject("CDONTS.Newmail")
objMail.Send "unclechutney@takempis.com", _
   Request.Form("Email"), _
   "Thank you for taking our survey", strBody
Set objMail = Nothing
strConfirm = "Your record has been updated"

If this is not an UPDATE, we can find out whether the form has been submitted for INSERT by checking any required form field. In this case, we use the City field.

' ** If not UPDATEing, check to see whether
' ** the form has been submitted.
' ** "City" is a required field, so if the form is being submitted
' **  for the first (or second) time, this field will not be blank
elseif Request.Form("City") <> "" then

If the form has been posted, we need to use the GetFormFields Sub to populate our form field variables.

GetFormFields
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("Survey_ConnectionString")

We need to get the AutoNumber ID field from the new record now for any future UPDATE that may occur. Because this number is generated automatically when the record is added, we need to get it from the record as soon as it is added. To do this, we open an updateable RecordSet and use the AddNew() method to insert a new record. As soon as we call the Update() method of the RecordSet, we can read the ID field.

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open "tblSurvey", objConn, adOpenKeyset, _
   adLockOptimistic, adCmdTable
objRS.AddNew
objRS("FirstName") = strFirstName
objRS("LastName") = strLastName
objRS("Email") = strEmail
objRS("City") = strCity
objRS("State") = strState
objRS("Zip") = strZip
objRS("Age") = intAge
objRS("Sex") = intSex
objRS("HowHeard") = strhowHeard
objRS("ConnectionType") = strConnectionType
objRS("More") = strMore
objRS("NewsLetter") = blnNewsletter
objRS("Comments") = strComments
objRS.Update
strID = objRS("ID").Value
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
strConfirm = "Thanks for your input! Please " &_
   "check to see if you have made any mistakes, " &_
   "and if you make changes, press the 'Submit' " &_
   "button to save them."
else
strConfirm = ""
end if
%>

Now we need to add the data generated by this scripting to the page. Most of this can be done in Normal view in FrontPage. For example, by double-clicking a text box, you can fill in the "value" box with an ASP tag. The following illustration shows this:

• Check boxes and radio buttons. These are checked when the token "checked" is added to the input tag. By using a conditional ASP statement, you can programmatically check them. Example:
  <input type="checkbox" name="Newsletter" value="ON"
  <%if blnNewsletter then Response.Write "checked"%>>
  

• Drop-down list boxes. The selectedIndex property determines which option is selected. I like to put most of the work for this on the client side, by using some JavaScript with a bit of ASP scripting inserted. Example:
  for(var x = 0; x < document.FrontPage_Form1.State.options.length; x++)
  {
     if(document.FrontPage_Form1.State.options[x].value == "<%=strState%>")   {
        document.FrontPage_Form1.State.selectedIndex = x;
        break;
        }
  }

Other than that, I just used a JavaScript alert to inform the user of the results of his or her actions. Of course, if the form hasn't been submitted, no alert is necessary, so I conditionally execute the alert:

<%' ** JavaScript alert if form was submitted for INSERT or UPDATE
if strConfirm <> "" then%>
alert("<%=strConfirm%>");
<%end if%>

As you can see, the code and content are neatly separated, making the script easy to maintain and change.

We now can use the data collected to create statistical reports, send e-mail messages and newsletters, and create personalized Web pages for the user.

A Few Words About Dynamic Content

Remember that, if you can learn ASP scripting, you can use it to dynamically change the content of your Web pages. If you have a large Web site, you can use ASP to customize the look, feel, and content of your entire Web site. You can store the layout and configuration settings in your database, and use the data you fetch from the database to build the layout of your pages. ASP is a powerful tool and has as many uses as you have the imagination to invent them.

I have created licensed Internet ASP applications that enable the client to change the entire layout and other design characteristics of their Web site in a browser. By developing these sites with FrontPage, I have given the client the ability to make changes to the HTML in FrontPage without having to know much of anything about HTML or programming.

Automating the process of making layout changes to a Web site is highly cost effective, especially with large enterprise Web sites. The company I work for, AutoMark, maintains more than 4,000 automobile dealership Web sites that all use the same underlying active server technology. Yet each has a unique look and feel, all of which is entirely under the control of the client dealer using a browser interface.

In short, there is no limit to what you can automate in Web site development using ASP and databases. And FrontPage is one of the best productivity tools for this sort of development.

Next month, we will discuss dynamic Web content in detail.