Tutorial:
Database - ASP - MapPath Connections
Dr.
Thomas E. Hicks
Computer Science
Department
Trinity University
Once you have a working web server, one may connect to the server
There are times one must move a database to another folder or another drive. With an ODBC connection, one simply changes the interface connection and all is done. The problem is that the ODBC connection requires administrative access. Since normal users may not create or change such connections, this is often not an available option to those maintaining web sites.
When a database is moved with a MapPath connection, each and every page
interfacing with the database must be individually changed/altered to reflect
the new location. This is much more of a hassle than ODBC connections, but it
makes database interface possible to those leasing web site space on a
commercial Internet Service Provider (ISP) This often makes it possible
for students to do database interface on a university web server; the
administration is not going to provide or manage ODBC for the students. This
often makes it possible for small companies to do database interface on an ISP
web server; the ISP is not going to provide or manage ODBC for the customers.
This often makes it possible for teachers to do database interface on a school
web server; the administration is not going to provide or manage ODBC for the
teachers.
Both connection types have advantages and disadvantages. It is the purpose of this paper to describe the MapPath option. You may find out information about ODBC connections with the following tutorial:
Database - ASP - ODBC Connections
In order to use ODBC to enable web pages to update and query a database, the host computer must be running web server software. One of the Tutorials below might help you install web server software on your computer.
IIS 5 Install on Windows XP Pro
IIS 5 Installation on Windows 2000 Pro/Server
It might even help to read a tutorial that describes the need for Database Web Applications.
Database Need For Web Applications
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.
Each record in the Users table contains fields Name, No, Password, and IDNo; these will be needed later.
The datatypes of each field may be seen below.
The Security Database above was is ultra trivial application which had only one database table. Relational databases often have many tables; this is true of Access databases as well. This database may be downloaded.
The customers table stores the video store customer entity. The Movie table stores the video store movie entity. The Video Database is a relational database; the Transact table stores the relationship of movies checked out by the customers.
The Customer.dbf table contains 24 records. Each record of the customer table contains fields Name, No, and Phone. The table designer view shows the datatypes associated with the customer fields.
The Movie.dbf table contains 38 records. Each record of the customer table contains fields Name, No, and Category. The table designer view shows the datatypes associated with the movie fields.
The Transact.dbf table contains 101 records. Each record of the transact table contains fields Name, No, and Phone. The table designer view shows the datatypes associated with the customer fields.
This database may be downloaded.
In order to configure ODBC, one must have administrative privileges. All users using Windows 98 have administrative privileges. Windows NT, Windows 2000, and Windows XP can be configured for individual log in accounts; in order to set up ODBC on these configurations, one must have administrative privileges.
MapPath connections require no administrative privileges.
We are going to place all of the ASP files in folder C:\Inetpub\wwwroot\ASP. We could place the database files anywhere on the drive. For security reasons, it is not a good idea to place them in the wwwroot directory.
For purposes of this tutorial, we shall assume that the Video database files are located in C:\Video. For purposes of this tutorial, we shall assume that the Security database is located in C:\.
'-------------------------------------------------------------------------
' Establish ODBC Connection To The Server
'-------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")
ConnString = "SourceType=DBC; SourceDB=" &_
Server.MapPath("../../../Video/Video.dbc")
Conn.Open "Driver={Microsoft Visual FoxPro Driver}; " & ConnString
|
The ASP code to connect to the Access Security database is as follows:
'-------------------------------------------------------------------------
' Create The Console Object & MapPath Connection To The Server
'-------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")
ConnString = "DBQ=" & Server.MapPath("../../../Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString
|
Not quite as simple. Because of the relative positioning described above, the location of the databases may be traced three levels up from the current web page.
Although it is not the purpose of this tutorial to teach ASP or HTML, a example is necessary to verify that the connection works.
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<% Response.Expires = 0 %>
<!-- #include virtual = "../../Common/adovbs.inc" -->
<%
'========================================================================
'========================================================================
'==== ConnectMapPathSecurity.asp ====
'========================================================================
'==== ====
'==== Purpose : Demonstrate the MapPath connection to the ====
'==== Security database located three levels up at C: ====
'==== ====
'==== Written By : Dr. Thomas E. Hicks Date: 03/25/2003 ====
'========================================================================
'========================================================================
'-------------------------------------------------------------------------
' Declarations
'-------------------------------------------------------------------------
Dim Conn, UserSQL, UserRecordSet, ConnString
'-------------------------------------------------------------------------
' Create The Console Object & MapPath Connection To The Server
'-------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")
ConnString = "DBQ=" & Server.MapPath("../../../Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString
'-------------------------------------------------------------------------
' Read All Of The User Names
'-------------------------------------------------------------------------
UserSQL = " Select Name " &_
" From Users"
Set UserRecordSet = Server.CreateObject ("ADODB.RecordSet")
UserRecordSet.Open UserSQL, Conn, AdOpenDynamic, AdLockOptimistic
Do While NOT UserRecordSet.EOF
%>
<html><head>
<title>Dr. Thomas E. Hicks - ConnectMapPathSecurity.asp </title>
</head>
<BODY>
<% = UserRecordSet.Fields("Name") %><BR>
<%
UserRecordSet.MoveNext
Loop
%>
</BODY></HTML>
|
You can see the results below:
