<!Doctype Html Public - W3c Dtd Html 4.0 Transitional En > Using Active Server Pages

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">Using Active Server Pages AndODBC To Maintain
Databases On A(n) Windows 95/98/NT Web Server
Introduction

Introduction

The Internet is rapidly becoming more interwoven into our daily commerce, marketing, research, shopping, business, education, and recreation. CommerceNet reported that the United States has at least 92 million users [16 years old and older] accessing the Internet.[1] Most of the user counts are computed from databases distributed by Internet Service Providers (ISP). Colleges and universities often function as an ISP for their campus. Since most colleges and universities refuse to share their user database, they often count as one user in the statistics; in reality, there are often thousands of confidential users behind the educational domain names. Some companies are also large enough to be their own ISP and also choose to keep their databases confidential; these too statistically count as one user. Until this trend changes, the statistics reflecting user counts will continue to be both low and approximate.

CommerceNet also tells us that the number of new users has increased 16% in the last 9 months and that users involved in web purchasing have increased 40% in the last 9 months. [1] Since the numbers related to colleges, universities, and large businesses that serve as their own ISP are relatively constant, these growth statistics are staggering.

The amount of information available on the Internet is expanding rapidly; one of the speakers on Tech Web said that the amount of information is growing at a rate of more than 15% per month. More than one hundred search engines enable users to search huge databases for desirable information. In addition to web page information, Internet databases are being used to store information about hundreds/thousands of topics; included would be personal information, corporate information, geological and map-type information, organization information, statistical information, etc. Keeping information current is one of many practical problems facing almost all databases.

Databases can be maintained in a number of ways; maintenance includes adding new records, deleting old records, editing/changing existing records, and querying/searching the database for some type of conditional match. The four most common approaches database maintenance are (1) stand-alone applications written in a high level language, (2) applications written in a database programming environment, (3) a combination .idc & .htx pages which access the ODBC client on a Windows NT/98/95 web server, and (4) active server pages that access the ODBC client on a Windows NT/98/95 web server.

The stand-alone applications are often written in a high level language such as C++ or Java. They enable concurrent users to alter centralized databases stored on a local area network. These stand-alone application allow users to add new records, delete old records, edit/change existing records, and query the database. These applications are often tremendously expensive and time consuming to write/test/and maintain. A few long distance users, using a communication package such as PC Anywhere, may be able to access the database by taking over an existing workstation thus emulating a local user; this tends to be slow and ties up workstations. Successful usage, for the most part, is limited to the outreach of the local area network. Good Graphical User Interfaces (GUI) are essential.

Applications written in a database programming language, such as FoxPro, Access, Paradox, provide another alternative. These too enable more than one user to alter information that resides on a local area network. Some of the environments that fall into this category require a shell and others compile into a stand-alone application. Great GUI-based applications can be easily constructed; these will enable the Users to add new records, delete old records, edit/change existing records, and querying the database. It is possible for a few long distance users to use a communication package, such as PC Anywhere, to take over an existing workstation and pretend to be a local user; this tends to be slow via the modem and ties up workstations. Successful usage, for the most part, is limited to the local area network. Good Graphical User Interfaces (GUI) are essential. These applications are generally much less expensive to write/test/and maintain than the stand-alone applications; they are certainly easier to modify. Microsoft FoxPro is an example of a database programming environment which can compile a stand-alone application; the install disks include the .dll's necessary to run the application; there are no fees paid to Microsoft for each application used. Microsoft Access is an example of a database programming environment which requires that at least a minimal shell be installed on each workstation that is to use the database application; paying for the additional legal copies of Access increases the costs.

The last two solutions are restricted to the Windows 98/95/NT operating systems and require that a web server be installed. NT Server 4.x automatically includes a web server called Internet Information Server; the current version is IV. Microsoft’s Personal Web Server can be added to any Windows 98/95/NT Workstation. The Personal Web Server is now included on many of the installation cd’s; it can also be downloaded from Microsoft. For those with limited experience with the installation of Microsoft’s Personal Web Server, this author has written a tutorial, called Installing Personal Web Server [Win 98], that may be found at the URL cited below.

Let us now assume that the Web Server Software is now loaded on an NT Workstation, an NT Server, a Windows 98 workstation, or a Windows 95 workstation. The control panel on the system will include an applet called ODBC. This applet serves as an interface between private database files and public web pages; after downloading the proper drivers, the ODBC can be used to communicate with more than one hundred and sixty different database formats (i.e. Oracle, Dbase, FoxPro, Access, etc.).

The third database maintenance solution, mentioned above, is a combination .idc & .htx pages which access the ODBC client on a Windows NT/98/95 web server. These pages are simple to code; entire applications can easily be written with only a text editor. All applications will be completed with HTML forms; these forms will not be as slick and flexible as that custom GUI written in a programming language or the custom GUI developed within a database programming environment. But this approach does enable users, from all over the world, to add new records, delete old records, and query databases in any of the one hundred and sixty supported formats. Although an individual user can indeed edit/change a record, the record lock-out functionality which would prevent concurrent users from changing the same record at the same time are greatly lacking with this approach. A second concern is security. The .htx file is loaded into the browser; at this point the source code can be viewed by the user. With a little luck and savvy, a knowledgeable user can create his/her own .htx files to access you .idc files; this makes it difficult/impossible to allow only a subset of the users to perform a specific function; this a security risk. Those seeking more information about this approach may consult “Introduction To Programming ODBC On Windows 95/NT/NT Server for Database Distribution & Access via the Web”.[2]

The fourth database maintenance solution is active server pages that access the ODBC client on a Windows NT/98/95 web server. These pages are also easy to code; entire applications can easily be written with only a text editor. All applications will be completed with active server pages; neither will these pages be as slick and flexible as that custom GUI written in a programming language or the custom GUI developed within a database programming environment. This approach enables users, from all over the world, to add new records, delete old records, edit/change, and query any of the one hundred and sixty supported database formats successfully. This approach offers much greater security than the .htx/.idc approach.

It is the purpose of this paper to describe and illustrate how to configure the ODBC client, how to add new records, how to delete old records, and how to query a sample database. This paper shall assume a working knowledge of HTML. A working knowledge of active server pages would be quite helpful.

The Database File

For purposes of discussion, let us suppose that an Access Database, called SecurityDatabase.mdb, resides at the root of drive C. (See Below!) When a database is installed on the web server, the necessary ODBC drivers are also installed. If the web server does not have the database installed, then the ODBC drivers will have to be downloaded and installed; this process differs from database to database and is beyond the scope of this paper. Microsoft Access and the appropriate ODBC drivers have been installed on the server illustrated below.

The Security Database is opened and the Tables tab is selected; in order to keep our example simplistic, the Security Database has only a single table, called Users.

The contents of our very simple Users table can be seen below. Make note of the field titles [Name, No, Password, and IDNo]; these will be needed later.

Configuring The ODBC Applet

The ODBC applet can be found in the control panel. Using the mouse, double-double click on ODBC. (See Below!)

Using the mouse, select the System DSN tab. (See Below!)

Using the mouse, select the MS Access Database Driver. Using the mouse, push/select the Add button to Add a new ODBC client. (See Below!)

Using the mouse, select the Microsoft Access Database Driver. Using the mouse, push/select the Finish button. (See Below!)

Enter “Security” for the Data Source Name. Enter “Access Database Users Security” for the Description. Using the mouse, select the Select button. (See Below!) The Data Source Name may not match any of the other Data Source Names on the web server; it must be unique. Make note of the Data Source Name [Security]; it too will be needed later. The Description may be anything you choose.

Select the database to be associated with this Data Source Name. Using the mouse, select Drive C. Using the mouse select SecurityDatabase.mdb as the Database Name. Using the mouse, select/push the OK button. (See Below!)

It is now time to save the new User Data Source. Using the mouse, select/push the OK button. (See Below!)

Security should now be included as a User Data Source in the ODBC Data Source Administrator. The configuration is complete. Using the mouse, select/push the OK button. (See Below!)

About The Web Server

Folder \InetPub is the root folder for the web server; it can be installed or moved to any of your logical drives. Folder InetPub resides at the root of drive D in the illustration below. Folder \InetPub\wwwroot is the root folder for the web server. \InetPub\ftproot is the root folder for the web server.

The Active Server Pages shall be placed in the wwwroot folder or within folders placed in the wwwroot folder.

The scripting for the active server pages can be done in VBscript or JScript; VBscript shall be used in this paper. A file, called Adovbs.inc includes all of the constants for VBscript. A file, called Adojavas.inc includes all of the constants for JScript. In order to make these files easier to find, I would recommend placing a copy of them in folder \InetPub\wwwroot\Common. Applications can be updated or re-installed on different drives. Placing these in Common directory will also keep web site functionality independent from the location in which Access, FoxPro, etc. are loaded.

SQL Queries With Active Server Page (ASP): DisplayUser01.asp

Active server pages must have a .asp extension. The source code for DisplayUser01.asp is listed below. An explanation will follow. The line numbers to the left have been added only for discussion and are not part of the source file. ASP code is inserted within the HTML code; ASP blocks are going to begin with <% and end with %>.

DisplayUser.asp [As It Resides On Web Server]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 / <%@ LANGUAGE = VBScript %>
<% Option Explicit %>

<HTML>
<HEAD<TITLE>DisplayUser01.asp</TITLE</HEAD>
<%
Dim Conn, UsersRecSet
Conn = Server.CreateObject ("ADODB.Connection")
Conn.Open "Security"
Set UsersRecSet = Conn.Execute ("SELECT * FROM Users")
%>
<BODY TEXT="#000099" BGCOLOR="#FFFFFF" LINK="#000099"
VLINK="#000099" ALINK="#000099" BACKGROUND="Paper.jpg">
<CENTER> 
Display Users [List] - DisplayUser01.asp 
Dr. Thomas E. Hicks 
Trinity University 
</CENTER> 
<HR>
<% Do Until UsersRecSet.EOF %>
Name : <% = UsersRecSet ("NAME") %> 
No : <% = UsersRecSet ("NO") %> 
Password : <% = UsersRecSet ("PASSWORD") %> 
ID No : <% = UsersRecSet ("IDNO") %> <BR 
<% UsersRecSet.MoveNext
Loop
Conn.Close %>
</BODY</HTML>

Line 1: <%@ LANGUAGE = VBScript %> Scripting languages must be identified on Personal Web Server (PWS). Those using Internet Information Server (IIS) might trust their network administrator to select the correct default, but I would recommend beginning each ASP page with <%@ LANGUAGE = VBScript %> when using VBScript and <%@ LANGUAGE = JScript %> when using Jscript.

Line 2: <% Option Explicit %> forces the programmer to explicit declare all variables; it should follow the language selection at top of page.

Line 3:  file adovbs.inc includes the constants for VBScript. Put near top of page!

Line 7: Dim Conn, UsersRecSet declares/creates variables Conn and UsersRecSet.

Line 8: Conn = Server.CreateObject ("ADODB.Connection") creates a connection object called Conn.

Line 9: Conn.Open "Security" associates the connection object with the ODBC System Data Source Name called Security.

Line 10: Set UsersRecSet = Conn.Execute ("SELECT * FROM Users") fills variable UsersRecSet List with the results of the SQL query; select all fields (*) from the Users table in the database associated with the ODBC Security. A UsersRecSet pointer is positioned at the beginning of the UsersRecSet. The record at which the UsersRecSet pointer is associated is called the active UsersRecSet record.

Line 20: <% Do Until UsersRecSet.EOF %> is the beginning of a post-test loop that continues the UsersRecSet pointer gets to the end of the UsersRecSet; the loop shall process all of the users that meet the conditions of the SQL query.

Line 21: Name : <% = UsersRecSet ("NAME") %> the HTML shall render Name : . Immediately following will be the value stored in the “Name” field of the active UsersRecSet record. It will display “Tom”, then “Maurice”, then “Gerald”, etc. as the loop is processed. The HTML will then do a line break/feed;

Line 22: No : <% = UsersRecSet ("NO") %> the HTML shall render No : . Immediately following will be the value stored in the “No” field of the active UsersRecSet record. It will display “222”, then “333”, then “111”, etc. as the loop is processed. The HTML will then do a line break/feed.

Line 23: Password : <% = UsersRecSet ("PASSWORD") %> the HTML shall render Password : . Immediately following will be the value stored in the “Password” field of the active UsersRecSet record. It will display “Dr. Web”, then “Dr.Parallel ”, then “Dr. Harley”, etc. as the loop is processed. The HTML will then do a line break/feed.

Line 24: ID No : <% = UsersRecSet ("IDNO") %> <BR the HTML shall render ID No: . Immediately following will be the value stored in the “IDNo” field of the active UsersRecSet record. It will display “1”, then “2 ”, then “3”, etc. as the loop is processed. The HTML will then do two line breaks/feeds.

Line 25: <% UsersRecSet.MoveNext moves the UsersRecSet pointer to the next record.

Line 26: Loop cycles control back to the beginning of the loop at line 24; lines 24-30 are re-executed until the pointer reaches the end of the UsersRecSet.

Line 27: Conn.Close %> closes the connection object.

A local ASP page can not be loaded directly into the browser. ASP pages reside on a web server and are then distributed to the client. Suppose this web page were stored on a server whose IP address is 131.194.131.38 and whose DNS name is carme.cs.trinity.edu. Either of the following URL’s could be loaded to renderDisplayUser.asp.

When testing this page on the web server, either of the URL’s above will work; the URL below may also be used from the web server.

The top portion of Netscape’s rendering of DisplayUser.asp is shown below.

The ASP page distributed to the user is free of any ASP code; the web server is actually generating this page on the fly and distributing it to the client. Note that there are no traces of the file and database information; this information resides securely on the web server. It is much more secure than the idc/htx approach.

&lt;!Doctype Html Public - W3c Dtd Html 4.0 Transitional En &gt; Using Active Server Pages

<!Doctype Html Public - W3c Dtd Html 4.0 Transitional En > Using Active Server Pages