VBScript and Active Server Pagesby 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.
Its 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 softwarespecifically, 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
So lets see how Active Server Pages work. When a user navigates a browser to an ASP page:
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 outputthe page the user finally seescan 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 languagewhich 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
Lets 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 Ill 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:
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):
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:
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 newat 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 Microsofts 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 Basics 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 objects CreateObject method. Since you are dealing with an object reference you must use the Set keyword:
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. Heres a very simple connection string that specifies a Microsoft Access database file named 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 objects Open method:
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. Heres how you would create the RecordSet:
You can see that the Connection objects 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 objects 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.
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.
Lets 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.
Lets 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 toin 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:
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:
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 servers 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:
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.
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 pages 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 sites hits are coming fromand where they are not coming from. Youll 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:
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 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 Basics 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 Basics 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!)
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 firstname.lastname@example.orgCopyright © 1999 The Coriolis Group, LLC. All rights reserved.