VBScript and Active Server Pages

by Peter G. Aitken

ASP using server-side scripting with VBScript provides a browser-independent way to adapt your HTML-based Web pages to the users who surf them.

It’s amazing how useful it can be to connect your Web page to a database. For some tasks, such as running an online auction or a searchable links database, a Web database connection is utterly essential. You can bet your bottom dollar that eBay AuctionWeb, Excite, and Yahoo! have some powerful databases running on their Web servers! There are other tasks for which Web database programming can be very useful, although most programmers would not immediately make the connection. Examples include maintaining a guest book and keeping track of how effective your site advertising is. I am sure plenty of other uses for Web database connections are just waiting to be discovered.

But how difficult is it to program database-aware Web pages? I was very surprised to find that it is a lot easier than I expected, due largely to my experience with Visual Basic. After reading this column you may be pleasantly surprised, too.

There are two main ingredients in the formula: Active Server Pages and ActiveX Data Objects. Remember that I cannot cover these topics in depth in a single column or even a series. If you are not familiar with VBScript, Web publishing, database programming, or ActiveX Data Objects you will have to fill in a lot of details, but this column should at least get you started.

Before I get to the details, I must mention that the techniques I will be describing are possible only if your Web server is running Microsoft software—specifically, Microsoft Internet Information Server version 3 (or later) and Microsoft Peer Web services running on Windows NT. It is also available on the Microsoft Personal Web Server on Windows 95/98. There may be equally easy ways to create a database-aware Web page on other types of servers, but I lack experience to describe them here.

Active Server Pages

The name Active Server Pages (ASP) is very descriptive: ASP refers to Web pages that exist on the server and are active. But what exactly does this mean? An ASP Web page is a plain text file, just like standard HTML pages. It consists of an HTML document with script code embedded in it. The script language is most commonly VBScript or JavaScript, but it can be any scripting language supported by the server software. Script text is separated from other page elements by being enclosed in <% ... %> tags. ASP Web pages are differentiated from regular Web pages with an .ASP extension rather than .HTM or .HTML. Usually, an ASP page contains a tag identifying the scripting language that it uses, like this:

<%@ Language=VBScript %>

So let’s see how Active Server Pages work. When a user navigates a browser to an ASP page:

  1. The server receives the request for the ASP page and loads it from server disk.
  2. The server software executes the script in the page.
  3. A new HTML page is generated based on both the “plain” HTML in the ASP page and on the output of the script logic.
  4. This newly generated HTML page is served out to the user’s address and viewed in the user’s browser.

I think you can see where the “active” comes in. When you navigate to an Active Server Page you are not simply downloading a static HTML document, but rather are running a program on the server. The output—the page the user finally sees—can be customized using all the flexibility of a true programming language. A very simple example would be an ASP page that looks at the time on the system clock and displays a different image on the page depending on whether it is morning, afternoon, or evening. On a more complex and useful level, an ASP page can receive information from the user and display a customized page, query a database and display the results, or any number of other potentially useful things.

Many people think of VBScript as a client-side scripting language, which indeed it is. When used on the server side, in an ASP page, it is essentially the same language with some additional capabilities. Most notably, server-side VBScript can read and write disk files, something not permitted on the client side for security reasons. On the server, however, your scripts have read/write permission only in your set of folders, so there is no chance of a script causing mischief where it should not. Another advantage of server-side scripting is that it is browser-independent. Since only a “pure” HTML document is returned to the browser, there is no need for the browser to support a specific scripting language—which is required for client-side scripting. Of course, client-side and server-side scripting tend to be used for different purposes, but the important point is that you can use Active Server Pages with VBScript or any other scripting language without having to worry about browser compatibility.

A Simple Example

Let’s take a look at a simple ASP example, one that does not involve any transfer of user-specific information from the browser to the server. Suppose you are a professional photographer, and want to display some of your best work on your home page. You want to display only a single photo at a time, but have different photos displayed at different times so that repeat visitors to the page do not always see the same image. With ASP, you have several potential approaches to this problem. One would be to display a different image at different times of the day. Another would be to use a cookie to keep track of which of the available images, if any, a particular visitor has already seen, and then display a new one. A third approach, and the one I’ll use here, is to display a different image based on a random number.

In this example, assume eight different photos in files named image1.jpg through image8.jpg. First, use VBScript code to generate a random number between 1 and 8:

numbanners = 8
randomize
x = int(rnd() * numbanners) + 1

After this script executes, x has a value between 1 and numbanners. Then, generate the HTML code as follows (assuming you keep your images in the IMAGES folder):

<img src="IMAGES/image<%=x%>.jpg">

Here you see a variation on the script tag. When an ASP page contains <%= ... %> it instructs the server to write the value of whatever expression is within the tag to the output HTML. In this example, if x has the value 6, the resulting HTML will contain the following:

<img src="IMAGES/image6.jpg">

This, as you probably recognize, is a standard HTML tag for displaying an image. Each time the ASP page is loaded, a different random number is generated, and the user sees a different image. Listing 1 presents the entire code for this simple ASP page.

Connecting to a Database

One of the beauties of using VBScript and ASP for your Web database connection is that you do not have to learn anything new—at least, not if you have done much database programming in Visual Basic. Any Microsoft Web server should have ActiveX Data Objects, or ADO, installed. ADO is Microsoft’s new database object model, partially introduced with Visual Basic version 5 and now fully supported in version 6. (See the VDM cover story for Nov/Dec 1998: “Much ADO About Database Access” by Eric Harmon.) If you have worked with ADO at all, you know how much easier it is to use than older database technologies such as RDO. All this power is available to you in your Active Server Pages.

Well, perhaps not quite all. When creating a standalone Visual Basic database program, the easiest way to use ADO is by means of the ADO Data Control, which you place on a form and can easily program to serve as the link between your program and the data source. Since many of Visual Basic’s intrinsic controls can be bound to an ADO Data Control, programming a functional database front end is a piece of cake. Unfortunately, neither the ADO Data Control nor the data bound controls can be used on a Web page. Still, even without these controls, ADO makes database access relatively easy. The programming, however, is textual rather than purely visual.

At the heart of the ADO model are two objects: Connection and RecordSet. A Connection object represents the link between your page and the data source; that is, the database file. A RecordSet object represents a set of records returned from the data source in response to a query. You have the option of using these objects directly in a regular Visual Basic program, permitting you to use ADO to access a data source without using the ADO Data Control. In an Active Server Page, this is the only way to go.

The first required step is to create a Connection object using the Server object’s CreateObject method. Since you are dealing with an object reference you must use the Set keyword:

set con = server.createobject("adodb.connection")

Next, establish a link between the Connection object and your database. The easiest way to do this is to create a connection string that specifies the type of database and the specific database file. Here’s a very simple connection string that specifies a Microsoft Access database file named mydata.mdb:

constr = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=mydata.mdb;"

Connection strings can get a lot more complicated; see the sidebar “ADO Connection Strings the Easy Way” for a time-saving tip. Once you have your connection string, you establish the connection by passing the connection string to the Connection object’s Open method:

con.open constr

Once the connection is established, you next create a RecordSet that contains the desired records from a table in the data source. This requires putting together a Structured Query Language (SQL) query that selects the desired fields and records from the table. This is not the time or place to go into the details of SQL, so please take my word that the SQL query used here selects all fields and all records from a database table named sales. Here’s how you would create the RecordSet:

set rs = con.Execute("select * from sales")

You can see that the Connection object’s Execute method returns a reference to a RecordSet. After this script statement executes, the RecordSet contains the records from the table and you can use the RecordSet object’s methods and properties to access and manipulate the data. Here, for example, is the VBScript code required to display all of the data in the RecordSet. This script creates an HTML table with the field names displayed in the first row and the data displayed, one record per row, in the remainder of the table.

<table border="1">
<tr>
<% ' Loop once for each field in the recordset.
for i = 0 to rs.fields.count - 1 %>
<td>
<%= rs(i).name %>
</td>
<% next %>
</tr>
<% ' Be sure we are at the first record.
rs.movefirst
' Go through the records one at a time.
do while not rs.eof %>
<tr>
<% ' For each field, display the data in a table cell.
for i = 0 to rs.fields.count - 1 %>
<td valign="top">
<%= rs.fields(i).value %>
</td>
<% next %>
</tr>
<% rs.movenext
loop %>
</table>

This should help you see how easy it is, in an Active Server Page, to use VBScript to set up a connection to a database file. However, simply displaying the data in an existing database is usually not all you want to do. Generally, you need some way for your page to interact with the user. This means accepting input from each user.

Obtaining User Data

Much of what can be done with Web databases depends on getting information from the user. The HTML standard includes a mechanism by which the user can enter information into a form displayed in the browser, then submit that form with the contained information to the Web server. I am sure you have seen such forms on the Web; they can include elements such as text boxes and radio buttons that permit you to enter text, select options, and so on.

All such elements lie within an HTML form, which includes a Submit button that automatically sends the data entered on the form to the server. Forms generally include a Reset button as well, which erases all data from the form and allows a user to start fresh.

Let’s look at a simple example. The following HTML code displays a form with one text box and Submit and Reset buttons. I have numbered the lines so I can refer to them; these numbers are not part of the HTML.

1. <form method="POST" action="results.asp">
2. <p>
3. <input type="text" name="country" size="20">
4. <input type="submit" value="Send data">
5. <input type="reset" value="Clear">
6. </p>
7. </form>

Let’s look more closely and see what this code does. Line 1 marks the beginning of the HTML form. (This would be placed within the <body> … </body> tags of the HTML page.) When the Submit button is clicked, the form uses the POST method (more on this shortly) to send the data to results.asp.

Line 3 displays a text box that is 20 characters wide, named “country.” Line 4 displays a Submit button that shows the text “Send data” on it. Line 5 displays a Reset button that displays the text “Clear” on it. Line 7 marks the end of the form.

GET and POST

There are two methods that an HTML form can use to send data from a form: GET and POST. GET is the simpler technique, in which the data is tacked on at the end of the URL that the data is being submitted to—in the above example, results.asp. Data is formatted as key=value pairs and is separated from the URL itself by a question mark character. For example:

http://www.yoursite.com/results.asp?key1=value1

If we continue with the above example, suppose someone had entered “Germany” in the text box. When the form is submitted the URL sent will be as follows:

http://www.yoursite.com/results.asp?country=Germany

Using GET has the limitation that the total data sent cannot exceed 255 characters. POST does not have this limitation, because it sends the form data separately from the URL in an independent binary data packet. In both POST or GET, the data can be retrieved by the target ASP page by using the server’s Request object. If the form was submitted by GET, you use the QueryString method, whereas if the data was submitted with POST, you use the Form collection:

Value = Request.QueryString("key")
Value = Request.Form("key")

In both cases, key is the name of the HTML form element (that is, the edit box or other HTML form control) whose data you are retrieving. If key is not a valid name, both the above methods return a blank string.

We now have all the elements in place to do something useful. We can create an Active Server Page using VBScript, access database files with ADO, and retrieve data from the user by means of GET or POST.

ADO Connection Strings the Easy Way

Visual Basic can help you to create the connection strings for use in your ASP scripts. Start Visual Basic and place an ADO Data Control on a form. Display the control’s property pages. On the General tab, select the Use Connection String option and then click on the Build button. The Data Link Properties dialog will be displayed. On the Provider tab, select the correct provider for the database file you will be connecting to (use the Microsoft Jet 3.51 OLE DB provider for Microsoft Access database files). Use the Data Link Properties dialog box’s other tabs to set other details of the link, such as the name of the database file. When done, click on OK. Visual Basic will create the connection string and display it on the property page. Copy the connection string from the dialog box and paste it into your ASP script.

Track the Effectiveness of Your Web Site Advertising

If you have moved beyond a simple home page with pictures of your dog, you are probably making some effort to increase your page’s exposure through advertising. For a commercial site, evaluating the effectiveness of your advertising can be critical, as you do not want to waste effort and dollars that could be put to better use elsewhere. Even if your site is totally non-commercial, you want as many people as possible to take advantage of the information you have worked to provide, whether it be on restoring old cars, trout fishing in New Zealand, or cooking Chinese food.

“Web advertising” as a category includes such strategies as submitting your page links to search engines, exchanging links with others who have pages on related topics, and joining banner advertising programs. Using the techniques I will explain here you can have a precise picture of where your site’s hits are coming from—and where they are not coming from. You’ll be surprised at how easy it is.

The technique is based on the way the GET method works. GET sticks the user information at the end of the URL, as explained above, separated by a question mark. However there is no reason a GET request has to originate in an HTML form; you can simply create URLs in the proper format and the ASP script at the receiving end can use the information in the same way. Here, then, is what to do: For each form of advertising you use, submit a different URL, or, to be more precise, the same URL with different information tacked on at the end, identifying the source. When you submit your site to Yahoo!, for example, give them this URL:

http://www.yoursite.com/index.asp?source=yahoo

Then, if you join a banner exchange, give them the following:

http://www.yoursite.com/index.asp?source=bannerexchange

And if your friend Mike puts your link on his page, use this:

http://www.yoursite.com/index.asp?source=mike

Now, whenever someone navigates to your page, the script code can identify which source generated the “hit.” This information can be placed in a database for you to analyze at your leisure.

Creating the database file is an easy task thanks to Visual Basic’s Visual Data Manager, VizData. VizData lets you create a new database file in one of several formats and define the structure of database tables. You access VizData from Visual Basic’s Add-ins menu. For this project, create a new database, using the Microsoft Access V7 format, and create a table with three fields: an autonumber index field, a text field named SourceName to hold the identity of each link, and a date field named VisitDate to hold the date and time of each visit. I called the table Visitors and called the database file VISITORS.MDB, but of course you can use any names you like as long as you make the corresponding changes in the script code. This database file, which is currently empty, must be uploaded to your Web site where your ASP page can access it. (See the sidebar about testing first!)

Always Test Before Deploying

I strongly recommend testing your ASP applications before deploying them to your Web site. This is easy to do with Microsoft’s Personal Web Server. Install PWS on the same computer you are using to develop your ASP application, then "publish" your pages to the local PWS home folder. You can then use your browser to access the pages using the HTTP protocol even though they are on the same computer. Only when you are certain that your scripts are working properly should you make them available to the world on your real Web server.

Listing 2 shows the VBScript code required to retrieve visitor information sent along with the URL. This code goes in the body section of your HTML page. If no key value named “source” was sent by the requesting page, nothing is done. If a source is identified, the Visitors database file is opened and a new record added with the source name and the current date and time.

A Solid Start

This has been an admittedly quick and somewhat shallow introduction to the world of Web database programming and ASP. However, the fact that I can actually present some useful techniques in a few thousand words is, I think, a testament to the simplicity of the technology. With your existing knowledge of Visual Basic, the combination of Active Server Pages, ActiveX Data Objects, and VBScript makes Web database programming easier than ever. v


During daylight hours, Peter is a research scientist at Duke University Medical Center in Durham, North Carolina. Comments and suggestions may be sent to him at paitken@acpub.duke.edu

Copyright 1999 The Coriolis Group, LLC. All rights reserved.
©Aivosto Oy -