Web Workshop
Database Access using ASP
Ted Williams, MCSD, MCSE

List of Sections

Active Server Pages (ASP).

Active Server Pages(ASP). ASP is built into Microsoft's web server, named Internet Information Server (IIS). It is also available with the Personal Web Server on Windows 9x platforms. Third-party vendors, such as ChiliSoft have extended ASP to other web servers such as Netscape's web server and O'Reilly's WebSite for Windows. ASP is a platform for server-side programs written in either JScript or VBScript. JScript a Microsoft variant of JavaScript. VBScript is a subset of Visual Basic that is tailored to web programming.

The big advantage of ASP is that we can use a wide variety of COM components that provide additional functionality, such as email and database access.

Dynamic Pages. If the browser sends a URL requesting an HTML page to the web server, the web server just sends the contents of the file back to the browser without any modifications. If the browser sends a URL that specifies an executable program (*.exe), a Perl program (*.pl or *.cgi), or an ASP page (*.asp) the program may return a page that varies depending upon information received from the browser and upon information available at the server. Since the contents of the page may vary, pages returned from a program are called "dynamic pages".

Server-side Scripts. Scripting languages, such as JavaScript or VBScript can be executed on the web server. Scripts executed on the server are called "server-side scripts". ASP are distinguished by the .asp extension. ASP usually contain some scripts mixed in with ordinary HTML. Some web servers do not understand VBScript, but since we can choose what type of web server we use, the choice of languages is under our control.

Client-side ScriptsIn executing a server-side script, HTML is usually sent back to the browser. The page sent back to the browser may include JavaScript or VBScript. When this script is received by the browser, the script is executed by the browser. Scripts sent to the browser for execution are called "client-side" scripts. For example, Microsoft's browser, Internet Explorer (IE), can execute both VBScript and JavaScript. Unfortunately, not all browsers are capable of executing scripts, so client-side scripts must be used with caution. Web sites that cater to a wide variety of users do not use client-side scripts.

Since JavaScript works on a wider variety of server and browsers and we will stick to JavaScript for all of our ASP programming. Besides, most ASP examples are written in VBScript, our examples will supplement those found elsewhere.

The Plan. Our plan is to implement each application three times, as listed below:

• C++ using ISAPI. First, we write the program in the C++ language using the Internet Server Application Interface (ISAPI). ISAPI provides a mechanism for a C++ program to receive requests from the browser and to send dynamic HTML pages back to the browser.
• Perl using CGI. Second, we write a program in Perl using the Common Gateway Interface (CGI). CGI is an alternate interface that allows our Perl program to receive requests from the browser and to send dynamic HTML pages back to the browser.
• ASP using JavaScript. Third we write a program in JavaScript using Active Server Pages (ASP) to provide an interface to the browser.

This presentation is not a thorough discussion of ASP or JavaScript. Rather, we will focus more on some interesting examples of real ASP code.

Database Development. In developing a database for our use with C++, the Internet Database Connector (IDC) provided a simple way to interface a web page to a database using ODBC. In the version with Perl, the DBM (DataBase Management) library provided a way to implement a database. For many years, UNIX systems have a standard library called DBM that is also implemented for Perl in Win32 systems. DBM allows one to store an associative array (a hash) on disk. DBM provides persistent storage so that the associative array is saved between program executions. A hash is a Perl data structure that allows us to associate a database record with a key. This structure allows a simple way to have a table with a single index. More complex problems would require a more complicated approach.

JavaScript. The first incarnation of JavaScript, developed by Netscape, was named LiveScript. After Sun's Java language received a lot of notoriety, Netscape renamed LiveScript to JavaScript. JavaScript is a different language from Java developed by a different company. Since JavaScript is the only language that has been widely implemented by various browsers, it is currently the language of choice for all client-side scripts.

JScript. JScript is Microsoft's dialect of JavaScript. JScript contains several enhancements, including a mechanism for instantiating Active Server (COM) Components. Unfortunately, the database access methods used here are a non-standard feature of JScript.

The ADO Interface. Active Data Objects (ADO) is a general object-oriented interface to a wide variety of databases. It uses ODBC (Open Database Connectivity) interface and other interfaces as an underlying mechanism. The big advantage of ADO is that it morphs a variety of interfaces into the ASP mold.

The ADO Object Model ADO has just three main objects, Connection, Recordset, and Command. The Connection object provides a connection to the data source. The Command object provides a way to fetch or modify records using SQL (Structured Query Language). The Recordset object provides a way to access individual records fetched by the SQL command.

Each of these objects has a collection of properties. The Recordset object has a Fields collection which provides information about the individual fields in the record.

The Connnection Object

Once an instance of a connection object is established, it may be used to open a connection with an existing database using the method Open. To open a database, a Data Source Name (DSN) must be created using the ODBC Administrator program from as described in our April discussion of ASP. For example, suppose that we wish to connect to the pubs database using the DSN Books.

Set oC = Server.CreateObject("ADODB.Connection")
oC.Open "DATABASE=pubs;DSN=Books;UID=sa;Password=;"
oC.Execute "DELETE * FROM Contact WHERE State = 'LA'"
oC.Close
Set oC = Nothing

• The argument to the Open command is called the "Connection String" This string contains the information required to establish a connection through ODBC. In this case, we need the DSN, the user, and password required to connect to the database.
• There are several properties, such as the ConnectionTimeout property which could be tailored to special circumstances. In this case, we use the defaults.
• The last line is a Command that executes an SQL statement.
• It is important to release the resources when we are finished. Here we call close to release the connection and set oC to nothing to release the object.

Fetching Records. SQL can also be used to fetch one or more records as shown in this example:

Set oC = Server.CreateObject("ADODB.Connection")
oC.Open "DATABASE=pubs;DSN=Books;UID=sa;Password=;"
sSQL = "SELECT * FROM Contact WHERE State = 'LA'"
Set oRs = oC.Execute(sSQL, lRecs, cmdText)
oRs.MoveFirst
Do While Not oRs.EOF
	For Each oField in oRs.Fields
		Response.Write oField.Value
	Next
	Ors.MoveNext
Loop
oRs.Close
Set oRs = Nothing
oC.Close
Set oC = Nothing

• The open and SQL are similar to the earlier example. The SELECT * fetches all of the fields for this table. In this case we only select those records where the state is "LA"
• Then we fetch each record starting from first record in the recordset.
• Then we print out all the field values for that record.
• Be sure to release all of the objects and the connection when you are finished.

• ASP. Active Server Pages 3.0, Wrox Press. One thousand pages long, it includes lots examples of how ASP can be used.
• ASP & JavaScript. Using HTML 4 XML, and Java 1.2, Eric Ladd et al, QUE. A big book that touches on a wide variety of topics, but most topics are not treated in depth.
• HTML. HTML-The Definitive Guide, Third Edition, Musciano, O'Reilly (the Koala book). A description of the HTML tags including information about their implementation by various browsers.
• JavaScript. JavaScript - The Definitive Guide, Third Edition, David Flanagan, O'Reilly. An excellent reference on JavaScript 1.2.
• ASP. www.4guysfromrolla.com. Lots of ASP stuff. Search for "Sending Emails using ASP from CDO".
• ASP. www.asp101.com. More information on ASP.

Suggestions? Send email to: webmaster@tlwilliams.net
Last update: August 12, 2000