There are two major ways to connect to a database from an ASP.

1. System DSN Connections
2. DSN-Less ADO Driver Connections

We will focus on the ADO Driver types of connections because these are what I have been able to implement on this server.  The DSN or Data Source Name connections appear to be a bit cleaner, but it seems you need to have more direct access to certain things on the server in order to implement them.  If you are running Windows 2000 or have direct access to the web server you are more likely to want to use this approach.

ADO Driver Connections.  Since we will be making use of Access to develop our tables and our first examples are based on this and the use of VBScript, we will make use of the Access Driver as our first example.  The Jerke book makes use of SQL to interact with the data sources so we will have to modify this approach slightly in the near future.  I figure it is nice for you to see the beginnings of how all of this could be implemented in a VB and Access sort of environment.  While it wouldn't be very robust for frequently visited web sites, it certainly can be useful.

I will present some code snippets that you can modify to fit your own webs.  The snippets assume that you have an Access database with name yourdatabasename.mdb in a databases subdirectory of your root web named yourwebname.  The table within this database that you want to work with is called tablename.  We will work an example in the next web page for this week.

First we must develop the usual HTML that signals the server to use the ASP.dll.  We also have a statement that tells the server to include some information from elsewhere on the server.

<%@ Language=VBScript %>

<% Option Explicit %>

<!--#include File="adovbs.inc"-->

The first two statements should be familiar.  If you've worked with C++ you have included header files and libraries.  The third statement has a similar purpose.  The included file has a number of things in it  that are useful for connecting to databases.  It also includes the "definitions" of some ADO constants that we will use as parameters.  These are similar to the VB constants you've probably used in the past such as vbOkOnly, vbYesNo, vbYes and vbNo.

The adovbs.inc file is automatically included on the server when the IIS = Internet Information Server is installed.  It is in the C:\Program Files\Common Files\System\ado\ directory on the server.  It needs to be copied to your root web by the server administrator.  What I have done is e-mailed it to you as an attachment and you should upload it to your root directory..  

If you are more than a user on the server the nature of this #include command is likely to change so that you can access the file differently.  Unfortunately, we are all just little, nearly totally unimportant users, on the faculty server.

The next snippet of code has to do with establishing the connection.

' Open a connection to our Access database
' We will use ADO Driver connection

Dim objConn

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=F:\cisweb\YourUserName\databases\YourDatabaseName.mdb"

objConn.Open

This is actually just four lines of code due to the   _  line continuation character combination.  Remember you must have a space in front of the underscore in order for it to work.

• You first need to dimension a name or your connection object.  
• Then you set this variable to an ADODB type of connection.
• Then you must specify the driver and the entire path to your database on the faculty server.
• Then you actually open the connection.

Now that the connection is open you need to open the particular recordset.  This is done with a set of code similar to the following.

' Instantiate a recordset object and retrieve the info from the tablename table

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "tablename", objConn, , , adCmdTable

These three lines of code are quickly described in the following bullets.

• You need to dimension a name for your recordset object.
• Then you create the Recordset object
• Then you open it based on the connection you developed above.  We will describe more about the objRS.Open command later in this page.

The last thing you need to do after implementing these connections is to make sure you close them after you are done.  This ensures a much cleaner operating environment and is very important.

' Clean up the ADO objects

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing

You really do need to close the recordset first, then the connection and set each of them to Nothing.

The Open Method for the Recordset Object.  We have just made use of the objRS.Open command in the previous set of code.  I probably need to say a little bit more about it.  In general the Open method will be of the form

objRecordsetInstance.Open Source, ActiveConnection, CursorType, LockType, Options

This is why the command above has so many commas.  By not filling in any values for certain parameters the default values are used.

• The Source is likely to be some table or even the results of an SQL query.

• The ActiveConnection is already opened connection.

• The CursorType can be

  • adOpenForwardOnly - for a forward only moving cursor or fire hose cursor

  • adOpenStatic - this scrollable cursor able to make use of all move commands in VBScript.  The static refers to the user's inability to modify records.

  • adOpenKeyset - another scrollable cursor with slightly more involved features.  Now the user can modify records.

  • adOpenDynamic - the last scrollable cursor with the highest level features.

• The LockType can be

  • adLockReadOnly - records are read only and can't be modified

  • adLockPessimistic - records are locked immediately when someone tries to edit, no one else can.

  • adLockOptimistic - records are locked only at time of an update

  • adLockBatchOptimistic - records aren't locked until a batch update occurs.

You need to include the adovbs.inc in order to make use of these constants.  The defaults are for the forward only cursor and the read only lock. This is only a brief survey which we will elaborate on when needed.

In this course we will be using Access databases and VBScript to interact with them.  While this is nowhere near as high powered in terms of volume and speed, it still is a viable framework in which to develop.