12.6.1- Creating a Connection to a Database

Creating a Connection to a Database

We've spent an awful lot of time explaining what this connection business is about, so lets start looking atthe real coding issues. This is the easiest way to create a Connection object in ASP:

Dim objConn

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

This uses the CreateObjectmethod of the Server object to create an instanceof the Connection object – in much the same way,we've used the same method to create instances of other objects in earlierchapters. The programmatic identifier (or ProgID)for the ADO Connection object is ADODB.Connection. So that we can use the object withinour code, we've given it a name – objConn.

Opening a Connection

So, the two lines above are enough to create a Connection object. However, we've done nothing with ityet. Just because we have created a Connectionobject, that doesn't mean that we're connected to the database! In order toactually establish the connection we use the Openmethod of the objConn Connectionobject. The syntax for using the Open method is:

objConn.Open ConnectionString,UserId, Password, Options

In fact, we used this in the examples we've seen so far inthis chapter. For example:

  objConn.Open strConnect

Here, we've only specified the first argument – theconnection string – and the other arguments assumed default values. In fact,all four arguments are optional arguments – we don't have to specify anyof them at the time we call the Open method.Instead of the above line, we could achieve the same effect by setting theconnection string using a special property of the Connectionobject – its ConnectionString property. After that we call the Open method without anyarguments, like this:

  objConn.ConnectionString = strConnect

  objConn.Open

Here's an example where we use the Openmethod directly, passing the user ID and password as well as the connectionstring:

objConn.Open strConnect, "ChrisU",""

If you pass user ID/password details in the second andthird arguments like this, but you alsospecify user ID/password details in your connection string then– according tothe ADO documentation – the results are unpredictable. It is wise to avoid thiskind of potential confilict of information by choosing one method of input onlyand sticking to it.

The above examples assume that strConnectis the connection string, like the one we created in DataStore.aspearlier in this chapter. But of course, we could equally well write an explicitconnection string as the first argument instead:

objConn.Open "Provider=SQLOLEDB;Persist SecurityInfo=False;" & _

             "User ID=sa;" & _

             "Initial Catalog=Movie;Initial FileName=C:\MSSQL7\Data\Movie2000.mdf"

Closing the Connection

Once you have finished with a connection, you should Close it, in order to freeassociated system resources:

objConn.Close

This doesn't actually remove the object from memory; so youcan Open it again if you need to. Once you'veclosed the connection, you can change the connection string and otherproperties, and use the same object to open a different connection.

Alternatively, if you've finished with the object you canremove it from memory, by setting the name of the object to Nothing:

Set objConn = Nothing.