Basically Visual

March/April 1999

by Peter G. Aitken (c)1999

Originally published in Visual Developer magazine

VBScript and Active Server Pages for Easy Web Database Programming

It is amazing how useful it can be to connect your web page to a database. For some tasks, such as running an on-line auction or a searchable links database, a web database connection is essential. You can bet your bottom dollar that the 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. Some examples include maintaining a guestbook and keeping track of how effective your site advertising is. I am sure there are plenty of other uses for a web database connection, waiting to be discovered.

But how difficult is it to program database-aware web pages? When I first tackled this problem, 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. 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 these techniques are possible only if your web server is running Microsoft software - specifically, Microsoft Internet Information Server version 3 and better 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 you won't find out about 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 the 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 is separated from other page elements by being enclosed in <% ... %> tags. ASP web pages are differentiated from regular web pages by having the ASP extension rather than HTM or HTML. Usually, an ASP page contains a tag identifying the scripting language that it uses:

<%@ Language=VBScript %>

Let's see how Active Server Pages work. Here is what occurs when a user navigates their browser to an ASP page:

1) The server receives the request for the ASP page and loads it from disk.

2) The server software executes the script in the page.

3) A new HTML page is generated based on the "plain" HTML in the ASP page and on the output of the script logic.

4) This newly generated HTML page is returned to the user and viewed in their 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 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 based on that information display a page customized for that user, query a database and display the results, or any number of 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 is it 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 different 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, the one I'll use here, is to display a different image based on a random number.

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

numbanners = 8
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/stamp_banner6.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 result is that the user sees different images. Listing 1 presents the entire code for this simple ASP page.

Listing 1. Using ASP to display different images to the user.

<%@ Language=VBScript %>
<title>Using ASP to rotate images</title>
numbanners = 8
x = int(rnd() * numbanners) + 1
<img src="IMAGES/image<%=x%>.jpg">

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. 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 stand-alone 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. Even without these controls, ADO makes database access relatively easy.

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 (the database file), and a RecordSet object represents a set of records returned from the data source in response to a query. You can use 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 real 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 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: constr

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 and 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 and 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.

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 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">
<% ' Loop once for each field in the recordset.
for i = 0 to rs.fields.count - 1 %>
<%= rs(i).name %>
<% next %>
<% ' Be sure we are at the first record.
' Go through the records one at a time.
do while not rs.eof %>
<% ' 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 %>
<% next %>
<% rs.movenext
loop %>

For now I hope you realize 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 - you need some way for your page to interact with the user. This means accepting input from each user.

Getting User Data

Much of what can be done with web databases depends on getting information from the user. The Hypertext Markup Language standard includes a method by which the user can enter information into a form displayed in his or her browser, then submit the form 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 are within an HTML form, which includes a Submit button that automatically sends the data entered on the form to the server. Forms also usually include a Reset button which erases all data from the form. 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 of course 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 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 soon) to send the data to RESULTS.ASP.

Line 3: Display a text box that is 20 characters wide and is named "country".

Line 4: Display a Submit button that displays the text "Send data" on it.

Line 5: Display a Reset button that displays the text "Clear" on it.

Line 7: Marks the end of the form.

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 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. For example:

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

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 either case, 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, while 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 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.

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 - 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 includes such strategies as submitting your link to search engines, exchanging links with others who have pages on related topics, and joining banner advertising programs. With the techniques I will explain you can have a precise picture of where your sites 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 URL's 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:

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

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

Now, whenever someone navigates to your page the script code can identify where they linked from. This information can be placed in a database for you to analyze as needed.

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 tables. You access VizData from Visual Basic's Add-ins menu. For this project, create a new database, using the Microsoft Access version 7 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 (but 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 1 shows the VBScript code required to retrieve any 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.

Listing 1. VBScript code to log web page visitors into the database.

<% ' See if the request source is identified.
req = Request.QueryString("source")
' If so, log it in the database.
if req <> "" then
' Open the connection to the database file.
cs = "Provider=Microsoft.Jet.OLEDB.3.51;"
cs = cs & "Persist Security Info=False;"
cs = cs & "Data Source=visitors.mdb;"
set ldb = server.createobject("adodb.connection") cs
' Create the RecordSet object.
set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = ldb
rs.CursorType = 1 ' adOpenKeyset
rs.LockType = 3 ' adLockOptimistic
rs.Source = "Visitors"
' Open the RecordSet and add the new record.
rs("SourceName") = req
' Use the VBScript now() function to
' get the current date and time.
rs("VisitDate") = now
' Clean up.
set rs = nothing
set ldb = nothing
end if %>

This has been an admittedly quick and shallow introduction to the world of Web database programming and ASP. 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 technique. 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.