Connecting to a Database From an ASP
We have talked about what is needed to connect to a database from an
ASP and the VBScript features that make this easier. Now we will
develop how you should actually create the connection.
There are two major ways to connect to a database from an ASP.
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.
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.
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.
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.
These three lines of code are quickly described in the following bullets.
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.
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.
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.