VBScript Function Quickly Queries Databases

I created a simple VBScript function named fetchSQL that quickly queries Microsoft SQL Server databases for data, then returns that data in an array. Listing 1 contains the code for this function.

I place this function in an Active Server Pages (ASP) file named commonfunctions.asp, which I then include on every page from which I want to make a SQL call. I use another ASP file, connect.asp, to store the code needed to connect to the target database. The connect.asp file contains the code

<%application("connect") =  "Driver={SQL Server};  server=server;  Uid=uid;  Pwd=pswd;  Database=db "%>

where db is the name of the database (e.g., Northwind) you want to query, server is the name of the server on which the database resides, uid is the username to use to log on to the database, pswd is the password for that username. I include connect.asp on every page from which I want to make a SQL call.

In a script, you can call the fetchSQL function, then use VBScript's IsArray function to test whether any records are returned. For example, suppose you want to query the Northwind database for employees who live in Washington. For each of those employees, you want to retrieve the first name, last name, title, and employee ID. Your script might look like the one in Listing 2.

As the code in Listing 2 shows, if the IsArray function returns a Boolean value of True (which means data is present), the script writes the data to a table. If the IsArray function returns a Boolean value of False, the script writes the message No data. You can find the code for this sample script (as well as the code for commonfunctions.asp and connect.asp) in the 94748.zip file, which you can download by clicking the "Download the Code Here" link.

—Sharon M. Levine

Share Your Scripting Experiences

Share your scripting discoveries, comments, solutions to problems, and experiences with products. Email your contributions to [email protected]. Please include your full name and phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $100.