12.5.1- Describing the Connection

<< 12.5.0- What is a Connection?

Chapter12

12.6.0- The Connection Object >>

Describing the Connection

When we use our telephone to make a connection, we describe the connection we want by punching the telephone number into the telephone's keypad. It's a similar situation when we create a connection between a web page and a data store – we need to provide the Connection object with details of the connection we're trying to create. The difference is that a data connection requires a little more information – things like the type of data store, its location, what part of the data store, etc.

For example, an Access database is identified by the name and location of the .mdb file. SQL Server, on the other hand, is designed to handle more (and larger) databases, and there is often more than one machine running SQL Server – so in order to uniquely identify a database, it could be necessary to provide enough information to identify the data server as well as the name of the database. However, you may have noticed that this wasn't necessary in our example. This is because MSDE is a reduced version of SQL Server and doesn't require the same amount of information.

 

Every time you communicate with a data store, you'll need some form of connection. Sometimes you'll create the connection yourself; other times you might allow the system to create it for you. Either way, you're using a connection. Before we can connect to a data store, we need some way of knowing what it is and where it is. There are three ways to supply this information when creating a connection:

 

• Connection Strings
• Data Link Files
• Data Source Names

 

We'll look at each of these in turn, and we'll also consider why some of them are preferable to others.

Connection Strings

A connection string is a simple character string that lists all of the information needed to connect to a source of data. Of the three methods listed above, they're probably the most difficult to use because you have to write the strings yourself, instead of using some neat wizard. However, they give you the most power. We used connection strings to specify the connection details for the .mdb and .mdf data stores in the Connect.asp example above. A typical connection string will contain some or all of the following key pieces of information (dependent on the type of data store we are connecting to):

 

• Provider: the type of OLE-DB provider used in the connection.
• Driver: the type of ODBC driver, such as the ODBC Driver for Microsoft Access or ODBC Driver for Microsoft SQL Server (if you're using ODBC directly instead of OLE-DB)
• Initial File Name or Data Source: the physical database path and file name
• Initial Catalog: the name of the database
• User ID: the user name needed to connect to the database (sa is the default for the administrator user name)
• Password: the password of the user specified in the User ID field above
• Persist Security Info: a Boolean, set to True if you want Windows to remember your password for you

 

Writing a connection string is just a case of joining the various pieces of information together – separating consecutive pieces with semi-colons. Let's have a look at a few sample connection strings, to get a feel for how they are constructed to suit a particular set-up.

Some Sample Connection Strings

If we're connecting to an Access database then we might use a connection string like the following (this is the connection string from the Connect.asp example above):

 

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\datastores\Movie2000.mdb;" & _

"Persist Security Info=False"

 

Microsoft Access uses the Microsoft Jet engine, so here the OLE-DB provider we've specified is for the Jet engine (rather than Access itself). We've also specified the location and name of the Access database, as the Data Source, and the Persist Security Info Boolean.

 

If we were accessing the same database using the ODBC driver for Access (instead of an OLE-DB provider), then we might use the following instead:

 

"Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\datastores\Movie2000.mdb"

 

This time we specify the ODBC driver (using Driver) and the database name and location (using DBQ).

 

For a SQL Server-type database, the information required in the connection string is different again. Here's the connection string we used to connect to the Movie2000.mdf data file in the Connect.asp example above:

 

"Provider= SQLOLEDB;Persist Security Info=False;" & _

"User ID=sa;Initial Catalog=Movie;" & _

"Initial File Name=C:\MSSQL7\Data\Movie2000.mdf"

 

This adds the Initial Catalog and the user ID to the required information as well.

 

If the Movies database were properly installed into a SQL Server (rather than being accessed via MSDE as we're doing here), then it would be different again. In that case, a connection string such as the following should do the trick:

 

"Provider= SQLOLEDB;Data Source=MyDataMachine;" & _

"Database=Movie;User ID=sa;Password="

 

You're probably starting to get the idea – the structure of the connection string is dependent on the type of data store you're trying to connect to.

Connection Strings and Include Files

If you're quick off the mark, and you're mind is racing ahead, then you might be thinking of a situation in which you have a number of related ASP pages on your web site, each of which uses connection strings to the same database – and how, if you changed the location of the database then it would be a maintenance nightmare to change the connection string details in every single ASP file. Fortunately, there's a really simple way around this, in the form of server-side include (SSI) files.

We've already come across the notion of SSIs – remember that an SSI is a way to include the contents of one file within another. When ASP sees the #INCLUDE command, it looks for the FILE argument, which specifies the SSI (the file to be included). Then it places the contents of the specified SSI into the first file, in place of the #INCLUDE command (see the example below). Thus, the contents of the SSI behave as though they were typed directly into the first file.

 

In the context of database access, we can use SSIs to store our connection string details. Using this technique, we can write the connection string into an SSI, and then include the SSI into each ASP page that needs it. Thus, the connection string is only defined in a single place.

 

We recommend that you avoid file extensions such as .txt and .inc when naming your SSI files. Instead, always use the file extension .asp for you SSIs, which (as we explained in Chapter 10 ) provides additional security for your SSI code.

 

For example, we could create a new SSI file called DataStore.asp, with the following contents:

 

<%

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\datastores\Movie2000.mdb;" & _

"Persist Security Info=False"

%>

 

Here, DataStore.asp is a single central file that contains your connection details. Now, we can include this into each ASP page that uses such a connection, by writing the following line into each ASP page:

 

<!-- #INCLUDE FILE="DataStore.asp" -->

 

If the connection details change (for example, if the location of the data store changes, or if we manage to acquire a more efficient OLE-DB data provider), we don't need to edit each-and-every affected ASP file: instead, we just change the contents of DataStore.asp.

 

So let's go back to our example, and rewrite it so that the connection string is stored in its own little SSI.

1.    Open up Notepad or any other code editor, and type in the following connection string information. You'll recognize some of this code from the Connect.asp example, above. Again, ensure that the connection string reflects the location of the database on your machine:

 

<%

Dim strDatabaseType

 

'Choose one of the following two lines, and comment out the other

strDatabaseType = "Access"

'strDatabaseType = "MSDE"

'Now we use this selection to specify the connection string

If strDatabaseType = "Access" Then

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\datastores\Movie2000.mdb;" & _

"Persist Security Info=False"

Else

strConnect = "Provider=SQLOLEDB;Persist Security Info=False;" & _

"User ID=sa;Initial Catalog=Movie;" & _

"Initial File Name=C:\MSSQL7\Data\Movie2000.mdf"

End If

%>

 

Notice that we put the <% … %> tags around this information, to make sure that it is identified as a server-side script and executed on the server.

2.    Save this file as DataStore.asp, in your C:\inetpub\wwwroot\BegASPFiles directory.

3.    Copy the file Connect.asp to a new file, called Connect2.asp. Then open Connect2.asp in your ASP editor, and amend the contents of that file as indicated by the shaded lines:

 

<%

Option Explicit

Dim strConnect

%>

<!-- #include file="datastore.asp" -->

<HTML>

<HEAD>

<TITLE>Using an SSI to hold the Connection String</TITLE>

</HEAD>

<BODY>

 

<%

Dim objConn, objRS

Dim adOpenForwardOnly, adLockReadOnly, adCmdTable

 

adOpenForwardOnly = 0

adLockReadOnly = 1

adCmdTable = 2

 

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

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

 

objConn.Open strConnect

 

objRS.Open "Movies", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

 

While Not objRS.EOF

Response.Write objRS("Title") & "<BR>"

objRS.MoveNext

Wend

objRS.Close

objConn.Close

Set objRS = Nothing

Set objConn = Nothing

%>

 

</BODY>

</HTML>

 

4.    Don't forget to save your changes to Connect2.asp.

 

5.    Browse to the page Connect2.asp in your browser:

How It Works

In Connect2.asp, the cumbersome connection details have been replaced with a simple #INCLUDE command that inserts the connection string definition as required. Once we've performed that include, the strConnect variable is available for us to use with the Connection object's Open method (and anywhere else, if we want to).

 

The connection string is that which is defined in the SSI, DataStore.asp. If you ever need to update any of the information contained within the connection string, then you only have to do it once – within DataStore.asp.

Data Link Files

So we've seen how to create a connection string by hand – but what if you're unsure of what information to put in the string? There is a nifty little way to get Windows to create a string for you. Way back with the release of ADO 2.0 (which, admittedly, was only a couple of years ago), Microsoft introduced data link files – a method for connecting to your data store which avoided the need to type all of the code into a connection string by hand.

 

In Windows Explorer in Windows 98 or NT 4.0, you could create a data link file by selecting the New option from the File menu – and thus generate and store the necessary connection string information in a UDL (Universal Data Link) file. Since then, Microsoft has deemed the ready accessibility of this functionality as 'confusing' to novices, and has thus partially removed it from Windows 2000.

 

In other words, it's still there – but creating a UDL in Windows 2000 is just a little less convenient than it was in Windows 98 and NT 4.0. So how do you go about using it now? Let's see…

As we discussed, with Windows 2000 Microsoft has removed the ability to create a data link from a Windows Explorer menu. However, with a little sly jiggery-pokery, we can still create one – it just takes a little more effort.

 

1.    Start up Notepad, but don't add any code to the file. Select File | Save As…. In the dialog, change the Save As Type from Text Documents (*.txt) to All Files (so that Notepad doesn't try to save the file with a .txt extension). Then use the dialog to save the blank file as MovieLink.udl in your C:\InetPub\wwwroot\BegASPFiles folder:

 

 

2.    In Windows Explorer, find MovieLink.udl and right-click on it. From the resulting menu, select the Properties option. This will present you with the MovieLink.udl Properties dialog.

3.    Select the Provider tab and select the OLE-DB provider you wish to create a data link for (select Microsoft Jet 4.0 OLE DB Provider for an Access database, or Microsoft OLE DB Provider for SQL Server for MSDE):

 

4.    Now select the Connections tab. If you selected the Jet provider (for an Access database) in the previous screenshot, you'll get something that looks like this. Enter the full folder path to the Movie2000.mdb file, and check the Blank password check box, as shown here:

 

Alternatively, if you're using MSDE then the Connections tab will look like this. Here, you need to supply a server name (the name of the machine that is hosting the data), user ID and password, and specify that you're actually attaching a database file:

 

5.    Click on Apply and then follow this with OK to dismiss the dialog.

6.    Open up Notepad and view the MovieLink.udl. This is what it looks like if created in MSDE (I've used Format | Word Wrap for this screenshot, just so that you can see the entire contents of the string):

How It Works

There's two ways you can make this work. First, you could copy the connection string from the .udl file, and paste it into your ASP code, as shown here:

 

objConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;" & _

"User ID=sa;" & _

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

 

Note that in the above code fragment, I've broken the connection string at various points, using a string concatenation character, &, and a line continuation character, _, to make the code more legible.

 

Alternatively, you could reference the data link file by its file name in your ASP code:

 

objConn.Open "File Name=C:\InetPub\wwwroot\BegASPFiles\MovieLink.udl"

 

Either way, it saves some of the dirty work.

Data Source Names

The Data Source Name (DSN) is another way to establish your connection string without typing it explicitly – again, it involves getting Windows to do most of the hard work for you. DSNs boast the advantage that they are very simple to use.

 

However, DSNs are now considered to be an outdated method of establishing a data connection. Unfortunately they use ODBC drivers – so you lose many of the advantages that come with using the OLE-DB providers, because DSNs don't support them. As we've remarked before, OLE-DB is the way that Microsoft are encouraging people to go – it is faster and more efficient, and you're going to have to get used to it anyway in the end.

 

However, at the moment, DSNs are still in everyday usage – many people still use this method of creating a connection in preference to either explicit connections strings or UDLs, simply because they're so simple to use. Therefore, you should be aware of how to create one.

Creating a DSN

You can use the ODBC Data Sources administrator to create a DSN automatically for you – you supply the information, and give it an identifier. Then you can use the identifier within your ASP code to access the DSN (and hence the data).

 

In Windows 2000, you'll find the ODBC administrator by selecting Start | Settings | Control Panel, then choosing Administrative Tools. There, double-click on the Data Sources (ODBC) icon. You'll be presented with the following dialog:

 

 

Yours may look slightly different from this, depending on which data sources are set up. The drivers shown above (and others, such as SQL Server, which isn't shown here) are supplied with a variety of Microsoft products, such as Microsoft Office.

 

The User DSN tab (above) shows all data sources for the user who is currently logged on. This allows you to have data sources that are only available for selected people who log onto the machine. This is no good for ASP, since ASP can only use System DSNs. So let's have a look at the System DSN tab:

 

A data source that appears in this tab is available to anyone who logs onto the local machine – including ASP itself. The screenshot above shows the System DSN tab on my machine – as you can see, this machine already has one SQL Server data source and one Access data source.

1.    To create a new data source for an Access database, start from the System DSN tab of the Data Source administrator, and click the Add… button. This will give you a list of available drivers. The driver is the underlying code that handles the connection for you, but you don't need to know anything about it except its name:

 

 

2.    Select the Microsoft Access Driver (*.mdb) and then click the Finish button. This presents you with second dialog, in which you can name the data source and pick the .mdb file to use. So first, type Movie into the Data Source Name textbox (this will be your DSN identifier) and add an appropriate description in the Description textbox.

 

 

Now click the Select… button to get the Select Database dialog – use this to locate the Access database Movie2000.mdb. (You could click the Create button to create a new database.)

 

3.    If you now click OK you'll see that your new data source has been added to the list.

 

How It Works

Once you've created a DSN using this method, you can reference it within a connection string, like this:

 

objConn.Open "DSN=Movie"

 

Instead of providing the normal connection string information, you simply pass the identifier of your DSN to the Open method; then ADO will look up the DSN and use the connection information contained there. It's very simple – but remember it can only be used for ODBC driver connections.

 

Again, we should remember that DSNs are a largely outdated method of specifying connection details, mainly because they use ODBC drivers instead of OLE-DB providers to handle the data. We include them here only because you may come across DSNs when working with legacy systems. We won't be using DSNs in this book.

<< 12.5.0- What is a Connection?

Chapter12

12.6.0- The Connection Object >>