Introduction

When you start writing web pages you generally begin with basic HTML (Hypertext Markup Language). You can write the HTML either directly or through a web page development program, but the net effect is that you get text-based code that your browser knows how to interpret. The biggest problem with HTML is that it can only be used to display static data. It cannot be used to extract data from a database. To get past this minor inadequacy (and to have more dynamic content), Microsoft introduced the concept of Active Server Pages. ASP code is not just static information. It can be used to do different things depending upon user actions and, more important, it can be used to access data from databases. Unfortunately, unless you want to tie yourself into a program with server-side extensions like FrontPage or Dreamworks, the easiest way to get data from a database is through our good friend SQL. In this chapter we will be constructing a few very simple Active Server Pages to show how SQL can interface with HTML to give the viewer database information. Some basic knowledge of HTML is required, but we will try to take things slowly, one step at a time.

Definitions

Basics

The major thing to realize about Active Server Pages is that the whole concept is a bit convoluted. It is part HTML code and part Visual Basic, and it is the responsibility of the person who is writing the code to keep everything in sync. One of the authors (guess which one) goes back to the early days of Assembly language when spaghetti code was more the norm than the exception. He has often compared the basic ASP coding process to the worst days of Assembly code. With that in mind, let’s begin to construct ASP.

The first requirement in writing ASP code is to have a web server that is capable of handling it. This generally means running Internet Information Services. IIS is included with Windows 2000, XP, Vista, and all flavors of Windows Server. In the case of Vista, it is no longer an automatically included component, however. You will have to install it manually. IIS functions as the middleman. It is responsible for making the inquiries to the SQL back end, formatting the data that is returned, and sending the information to the client. In short, IIS functions as the middle tier in a three-tier architecture, where SQL Server is the first tier, IIS is the second tier, and Internet Explorer on the destination computer is the third tier.

The second requirement in writing ASP code that can access a database is to have a database engine on the computer. While this can be full SQL Server, you can also use the Jet database engine that comes with Access.

Finally, you will need to have a method of joining the database engine to the IIS program. We are going to use the KISS (Keep It Simple, Stupid) principle here and just use good ol’ ODBC to tie everything together.

It is, unfortunately, beyond the scope of this book to show how to set up a web server or ODBC driver installation, so we are going to assume that these components are in place.

For our development of code we will be using the HTML view of Microsoft FrontPage. While FrontPage is not a perfect tool for code development, and it is no longer a component of Office, it does color-coordinate the parts of the code and makes viewing the code a bit easier than if a pure text editor like Notepad were used. A word of caution, however: Don’t try to view your web page in the FrontPage viewer and expect to see anything remotely like the final displayed page. Also, do not go back into normal mode and try entering anything. The probability of the parser destroying your work is almost at the 100 percent level. With these caveats given, let’s proceed.

Building the Components

For this example we are going to take the Customers table that we used in the last chapter and show how it can be done in ASP. You will note that the SQL statements are almost identical to the ones from the previous chapter. What is different is the framework around the SQL.

ODBC Connection

The first thing that has to be done when accessing data from a database is to declare the connection. We have discovered that an ADO ODBC connection works well. Setting up the ODBC connection is partially dependent upon your operating system, but once you get past the basics the setup is straightforward.

1. Using Windows 2000, go into the Control Panel and select Administrative Tools. On the Administrative Tools screen, select Data Sources (ODBC). This brings up the ODBC Data Source Administrator window.

If you are using Windows Vista, go into the Control Panel, select Administrative Tools (choose Classic View if necessary) and then select Data Sources (ODBC) to bring up the ODBC Data Source Administrator window. This sounds identical to Windows 2000 in operation but the screens are totally different! Luckily, the ODBC Data Source Administrator screen is identical (see Figure 15.1).

Figure 15-1. ODBC Data Source Administrator window

2. Any of the three types of DSNs can be created and all work identically. Generally we select System DSN since it can be used by anyone on the host computer. The only real advantage of creating a file DSN is that it is portable and can be copied from machine to machine. That is not an issue for this demonstration, so select System DSN.

3. Select Add to create a new DSN connection by bringing up the Create New Data Source window (see Figure 15-2).

Figure 15-2. Create New Data Source window

4. Depending upon how much stuff you have installed on your machine, this may be a very short or very long list. For Access, select the Microsoft Access driver. For an SQL Server database, you would select SQL Server. Since we will be using an Access database, just select the Access driver and click the Finish button. You will now need to fill out the ODBC Microsoft Access Setup window shown in Figure 15-3.

Figure 15-3. ODBC Microsoft Access Setup window

5. Any name can be given to the data source and any description can be entered. The important parts of this form are the Database and System Database sections. Click the Select button to enter the name of the Access database and, if you are using a system database, select the Database radio button and enter the system database. When completed, the ODBC Microsoft Access Setup form will look something like Figure 15-4.

Figure 15-4. Selecting the database

6. If you have set up login names and passwords, you will need to select the Advanced button and set the appropriate advanced options (see Figure 15-5).

Figure 15-5. Set Advanced Options window

Code

The first step in accessing the data is to build the commands to create and open the ODBC connection. To do this we will use ADO to access the data.

set conntemp=server.createobject("adodb.connection")
conntemp.open "DSN=wordware"

That is the bulk of the overhead work. From this point on, all we have to do is open the appropriate SQL recordset. Once again we will build the SQL statement into a string variable, then use the ADO command to open the string.

mySQL = "SELECT * FROM customers”
set rstemp=conntemp.execute(mySQL)

Except for the slight difference in procedure calls dictated by ADO, you will note that the SQL code is identical to the code that we have used before to get all records from the Customers table.

The rest of the code is a bit different and you will note that there is quite a bit of setup to format the data that is being sent out to the browser, but the final result produces a result similar to the one we have seen before (Figure 15-6).

Figure 15-6. Display of full recordset

The first block of code that is needed is for some housekeeping. Unlike Access, which is relatively forgiving when it comes to empty recordsets, ASP is not quite so forgiving and will display nasty messages if you have not taken the no data condition into account. So, we first check to see if there is no data, and if there is no data we stop processing:

if  rstemp.eof then
   response.write "no data for<br>"
   response.write mySQL
   conntemp.close
   set conntemp=nothing
   response.end
else

If there is data, we can then call up the records in the recordset and forward them to the browser. Unfortunately, as mentioned, unlike Access you have to tell the browser everything and there is no really convenient way to do this except by brute force. We begin by setting up some header information in HTML:

<p align="center"><b><font size="5" face="Arial"
color="#990000">&nbsp;Customers</font></b><p align="center">
<table border="0" width="781" height="462">
<tr>
   <td width="20" height="31" bgcolor="#080830"
             bordercolor="#C0B068">
      <font color="#C0B068">Customer ID </font>
   <td width="122" height="31" bgcolor="#080830"
             bordercolor="#C0B068">
      <font color="#C0B068">Firstname </font>
   <td width="114" height="31" bgcolor="#080830"
             bordercolor="#C0B068">
      <font color="#C0B068">Lastname </font>
   <td width="118" height="31" bgcolor="#080830"
             bordercolor="#C0B068">
      <font color="#C0B068">Address</font></tr>
   <td width="61" height="31" bgcolor="#080830"
             bordercolor="#C0B068">
      <font color="#C0B068">City</font>
   <td width="20" height="31" bgcolor="#080830"
             bordercolor="#C0B068">
      <font color="#C0B068">State</font>
</tr>

Now we can move through the records one at a time, extract the data from the fields, and form at the data into an HTML format. The major thing this block of the code shows is the interaction between the HTML and the VB script. All formatting of information is done through HTML. All processing, including the looping operation and the pulling of the data from the recordset, is done in VB. This is where things get a bit messy. The system assumes that everything you are doing is HTML until you go into VB mode with the opening (<%) and termination (%>) symbols. The net effect of this movement between HTML and VB is that the system is not capable of determining where code blocks occur and stop in either the VB or the HTML segments. It becomes the job of the programmer to ensure that things start and stop in a consistent manner. For example, if you begin a loop, you have to make sure that the loop is terminated. If you begin a table row, you have to make sure that it is properly terminated, regardless of intervening VB code. The code in the above example looks like this:

<% do until rstemp.eof %>
    <tr>
       <td width="20" height="1"><%=rstemp("customerID")%></td>
       <td width="122" height="1"><%=rstemp("firstname")%></td>
       <td width="114" height="1"><%=rstemp("lastname")%></td>
       <td width="118" height="1"><%=rstemp("address")%></td>
       <td width="61" height="1"><%=rstemp("city")%></td>
       <td width="20" height="1"><%=rstemp("state")%></td>
    </tr>
<%    rstemp.movenext
   loop
%>

Note that we have carefully indented and bracketed the <tr> </tr> pair as well as the do…. loop statements.

One other thing in this code that is a bit unusual for Access VB programmers is the use of the alternate form of field designation where the field name is specified in quotes rather than by the dot notation.

Putting all the code together with a few more bits of formatting produces the final result of:

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
      charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">

</head>

<body>

<%

myDSN="DSN=wordware"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
mySQL = "SELECT * FROM customers"
set rstemp=conntemp.execute(mySQL)

if  rstemp.eof then
   response.write "no data for<br>"
   response.write mySQL
   conntemp.close
   set conntemp=nothing
   response.end
else
%>
   <p align="center"><b><font size="5" face="Arial" color=
            "#990000">&nbsp;Customers</font></b><p align="center">
   <table border="0" width="781" height="462">
      <tr>
      <td width="20" height="31" bgcolor="#080830" bordercolor=
                "#C0B068">
         <font color="#C0B068">Customer ID </font>
      <td width="122" height="31" bgcolor="#080830" bordercolor=
                "#C0B068">
         <font color="#C0B068">Firstname </font>
      <td width="114" height="31" bgcolor="#080830" bordercolor=
                "#C0B068">
         <font color="#C0B068">Lastname </font>
      <td width="118" height="31" bgcolor="#080830" bordercolor=
                "#C0B068">
         <font color="#C0B068">Address</font></tr>
      <td width="61" height="31" bgcolor="#080830" bordercolor=
                "#C0B068">
         <font color="#C0B068">City</font>
      <td width="20" height="31" bgcolor="#080830" bordercolor=
                "#C0B068">
         <font color="#C0B068">State</font>
      </tr>
<% do until rstemp.eof %>
   <tr>
      <td width="20" height="1"><%=rstemp("customerID")%></td>
      <td width="122" height="1"><%=rstemp("firstname")%></td>
      <td width="114" height="1"><%=rstemp("lastname")%></td>
      <td width="118" height="1"><%=rstemp("address")%></td>
      <td width="61" height="1"><%=rstemp("city")%></td>
      <td width="20" height="1"><%=rstemp("state")%></td>
   </tr>
<%    rstemp.movenext
   loop
%>
   </table>
<%
end if
%>
&nbsp;
</body>

Building SQL Statements

In the previous chapter we had an example of how conditional statements could be used with SQL statements to build record-sources for Access forms. The same process can be used to build data sources for web pages. Using our phone number example, we will show how an ASP page can be built with code and SQL to alleviate the need of having several queries and to reduce the number of web pages.

Figure 15-7. Selection page

The web form begins with the same basic layout as the Access form. When the user selects an option button and then clicks on the Select button (Figure 15-7), information for everyone in the database who has a phone number appears, as shown in Figure 15-8.

Figure 15-8. Selection page with results

We have cheated a bit on this code to do everything in one form. Basically the important part of the ASP code (from the standpoint of SQL) is contained in a SELECT statement similar to the one in Access.

allorone = request.form("allorone")
select case allorone
   case "Cust"
      mySQL = "SELECT firstname, lastname, phonenumber FROM
      Customers"
   case "Emp"
      mySQL = "SELECT firstname, lastname, phonenumber FROM
      Employees"
   case "All"
      mysql = "SELECT firstname, lastname, phonenumber FROM
      Customers UNION SELECT firstname, lastname, phonenumber FROM
      Employees”
   case else
      stopprocessing = true
end select

The actual code to open the SQL is as follows:

set rstemp=conntemp.execute(mySQL)

To enable a single form to handle the initial form without a button selected and handle the SQL processing after a button is selected, we have added the “else” case to set a flag notifying the system to not process the remaining ASP code on the page.

The second major deviation between this code and the Access code is the overhead needed to process the radio buttons on the form. Unlike Access VBA with a plethora of events that can trigger things happening, ASP has a very limited set of events that can trigger an action. The major way to input data into ASP is to use the POST method and to trigger it with a submit command.

<form action="asp2.asp" method="POST"><p>&nbsp;</p>
<p align="left" style="margin-left: 10">
<input type="radio" checked name="allOrOne" value="Cust">Customer
<input type="radio" name="allOrOne" value="Emp">Employee
<input type="radio" name="allOrOne" value="All">All phone
             numbers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input type="submit" value="select" name="select">.

We begin by telling ASP that the POST method will be used to open a new ASP page. The page that will be opened is asp2.asp, which just happens to be the current page. The two variables on the page that will provide data to the ASP page are allOrOne and the Select button. The allOrOne variable provides the option button values “Cust,” “Emp,” or “All,” depending upon the radio button that is selected. The button provides the value “select,” which is not used in this example. The real purpose of the button is to trigger the action.

When the button is clicked, the page asp2.asp is opened with the POST value allOrOne=Cust&select=select.

Once again we have Visual Basic overhead similar to our previous example to verify that the recordset actually does have information in it, the overhead to open the ODBC connection, and the actual Visual Basic code to pull the data from the recordset. We also have the HTML overhead to set up the fields and to arrange the data in the table. When everything is combined, the final result is as follows:

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>
<body>
<%
   myDSN="DSN=wordware"
   set conntemp=server.createobject("adodb.connection")
   conntemp.open myDSN

%>
   <p align="center"><b><font size="5" face="Arial" color=
            "#990000">&nbsp;Phone Numbers</font></b><p align=
            "center">&nbsp;<p align="center">
   <form action="asp2.asp" method="POST"><p>&nbsp;</p>

   <p align="left" style="margin-left: 10">
   <input type="radio" checked name="allorone" value="Cust">Customer
   <input type="radio" name="allorone" value="Emp">Employee
   <input type="radio" name="allorone" value="All">All phone
                numbers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
   <input type="submit" value="select" name="select">

      <%
      allorone = request.form("allorone")

      stopprocessing = false
      select case allorone
         case "Cust"
            mySQL = "SELECT firstname, lastname, phonenumber FROM
                     Customers"
         case "Emp"
            mySQL = "SELECT firstname, lastname, phonenumber FROM
                     Employees"
         case "All"
            mysql = "SELECT firstname, lastname, phonenumber FROM
                     Customers UNION SELECT firstname, lastname,
                     phonenumber FROM Employees"
         case else
            stopprocessing = true
      end select
      if stopprocessing <> true then
         set rstemp=conntemp.execute(mySQL)
         if rstemp.eof then
            response.write "no data for<br>"
            response.write mySQL
            conntemp.close
            set conntemp=nothing
            response.end
         else %>

         <table border="0" width="781" height="462">
         <tr>
         <td width="122" height="31" bgcolor="#080830" bordercolor=
                   "#C0B068">
            <font color="#C0B068">Firstname </font>
         <td width="114" height="31" bgcolor="#080830" bordercolor=
                   "#C0B068">
            <font color="#C0B068">Lastname </font>
         <td width="118" height="31" bgcolor="#080830" bordercolor=
                   "#C0B068">
            <font color="#C0B068">Phone Number</font>
         </tr>
<%       do until rstemp.eof %>
         <tr>
            <td width="122" height="1"><%=rstemp("firstname")%></td>
            <td width="114" height="1"><%=rstemp("lastname")%></td>
            <td width="118" height="1"><%=rstemp
                                          ("phoneNumber")%></td>
         </tr>
<%          rstemp.movenext
         loop
%>
         </table>
<%
end if
end if
%>
&nbsp;</body>

Summary

This chapter showed how SQL can be used within ASP code to facilitate writing web pages. It also showed how the use of Visual Basic code and SQL can be used to reduce the number of actual web pages needed by judiciously reusing a basic template and by modifying the SQL data source to fill in the necessary information.