OPEN DATABASE CONNECTION

The most basic purpose of ASP is to allow a website to connect to a database and show "Live data". It is called live data because ideally the database administrator will be updating the database routinely which will therefore automatically update the website.

So how do you do it? Well, it's actually pretty simple. First, you need to understand that there are two ways to connect to a database. You can use a DSN or DSN-less connection, both accomplish the same thing. A DSN is a Data Source Name that is setup on the server. You can think of it as a shortcut to your database because it contains the driver and database path information to your database. If you have your website hosted by an outside company like most people do, you will need to contact them directly and ask them to setup the DSN for you. You will have to tell them where your database is located within your website and you will have to give the DSN a name.
Not all hosting companies support ASP. If you are looking for a reliable hosting company for your ASP website, we recommend using ASPWebHosting.com.

Here is an example of a DSN connection:

<% DIM objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=myCONNECTION.dsn" objConn.Open %>

For those of you not familiar, we will run through the lingo. In the first section, the DIM line declares the variable objConn. The "Set objConn..." sets the connection object. "objConn.ConnectionString..." sets the connectionstring and the DSN and the last line "objConn.Open" opens the connection.

Personnally, we prefer to use DSN-less connections to our databases. The reason is that for maintenance and updating purposes, it is easier to make changes to database connections on your own rather than having to call or email your hosting company and wait for them to update your DSN.

There is a little more code involved with DSN-less connections, but it is worth it. Here is an example of a DSN-less connection:

<% DIM objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ Server.MapPath ("/mydatabase.mdb") & ";" objConn.Open %>

The only difference between this example and the DSN example above is in the "objConn.ConnectionString = ..." line. Instead of using "DSN = myCONNECTION.dsn" you actually write out the appropriate driver and the respective path to the database. Ideally, the connection string should all be written on one line, but for display purposes we put it on two. When you paste this to your code, just remove the _ at the end of the line and put the Server.MapPath on the same line.
For maintenance purposes, we recommend that you place your database connection in a separate file like /includes/connection.asp. Then, simply use an include statement like this:

to include your connection string in your pages. Then, if your database connection should ever change, you only have to edit it one time in your connection.asp file and it updates your connection throughout your website.

http://www.aspwebpro.com/tutorials/asp/dbconnectionopen.asp