To read the article online, visit http://www.4GuysFromRolla.com/webtech/042599-1.shtml

Connecting to a Database


The power of Active Server Pages comes through when we tie databases to our web sites. ASP uses an object library called ActiveX Data Objects, or ADO; another fine TLA (Three Letter Acronym). ADO can be used to connect to any ODBC-compliant database. ODBC-compliant databases include MS-SQL Server, MS Access, Informix, Oracle, FoxPro, Excel, etc. Chances are, if you are using a modern database, it is ODBC-compliant.

OK, so how does one use a database in his/her web page? Well, I think it is easier to think of it if we break it down into steps. Here is the list of steps we need to follow to connect to a database:

  • Create an object to connect to the database
  • Provide that object with information regarding the location and type of our database
  • Command that object to open a connection with the database

To do the first step, we will use one of ADO's objects, the Connection Object. To create an instance of the connection object in ASP, we need only issue the following commands:

<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
...

This will fulfill step one of three: creating an object to connect to the database. Now we must provide this object with the information to connect to the database. There are two ways to provide this information. The first way is through the use of a System DSN. A System DSN is a file containing information about a particular database. This information includes the physical location of the database on the computer, what type of database it is (SQL, Access, etc.), and other pertinent information. (DSN stands for Data Source Name, another TLA)

You can create a System DSN in Windows by going to Start/Settings/ Control Panel/ODBC. There is a System DSN tab. From there you can Add a System DSN of your choice (Oracle, Informix, SQL, Access, etc.). There is a really great tutorial on how to do this at ActiveServerPages.com. I'd highly recommend you check this out if you've never created a System DSN before.

Once you have a System DSN, you need to let the connection object (objConn) know that this is the database you want to connect to. To do this, we but need add the following line:

objConn.ConnectionString = "DSN=SystemDSN Name"

So whatever you named the System DSN you created, put it after the DSN= in the connection string.

I mentioned that there were two ways to get the database information to the connection object. One way is to use a System DSN; the other way is to use what is called a DSN-less connection. This approach uglies up your connection string a bit, because you need to supply all of the important information in the connection string, since there isn't a DSN which holds that information. Access databases are the ones that typically use DSN-less connections. Here is an example of a DSN-less connection string.

objConn.ConnectionString = "DBQ=C:\WebShare\MyDatabase.mdb;DRIVER={MS Access (*.mdb)}"

DBQ tells the connection object the physical path to the database. DRIVER tells the connection object what type of database we're using; in this case, an Access database. There is a good tutorial on AcitveServerPages.com on how to connect to a database using a DSN-less connection.

OK, so now we've completed steps one and two: we've created an instance of a connection object, and provided it the information needed to connect. Now we just need to tell the connection object to open a connection with the database. This is done with the following method:

objConn.Open

That's it! You've now established a connection to a database! Be proud of yourself!

Here are some related articles on the site:

Happy Programming!


Article Information
Article Title: Connecting to a Database
Article Author: Scott Mitchell
Published Date: Sunday, April 25, 1999
Article URL: http://www.4GuysFromRolla.com/webtech/042599-1.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers