The UD 4 Connection

The UD 4 Connection

What is a connection string anyway? The connection string is how ADO (the DB access layer used in ASP) knows how to find your database. Please take some time to read this information on the different types of connection strings. I think it will give you a better understanding of what we will be trying to accomplish with this UltraDev connection tutorial.

We'll be covering OLE DB and ODBC.

Connection Strings are only string variables which contain database connection information. The only thing that makes them a connection string is that they are passed to ADO which will interpret them . Since they're going to be passed to ADO, they need to be in a format ADO understands (Provider) This is the important one. It tells ADO what provider it should connect to in order to access the data you want. It is often left off in which case it defaults to MSDASQL which is Microsoft's OLE DB Provider for ODBC. There are a number of providers supplied when you install MDAC, but We'll be focusing on MSDASQL, Microsoft.Jet.OLEDB.4.0, and SQLOLEDB which are the three most commonly used in UltraDev ASP pages

OLE DB Connection Strings

Since they became available (with MDAC 2.0 ) Most people have been recommending and using the OLE DB providers: Microsoft.Jet.OLEDB.4.0 and SQLOLEDB. Microsoft.Jet.OLEDB.4.0 is for use with Microsoft Access and SQLOLEDB is for use with Microsoft SQL Server. They generally offer better performance and reliability when compared to MSDASQL.Microsoft.Jet.OLEDB.4.0

This is the OLE DB provider for Access. To connect to an Access database using this provider, the only other attribute required to make a connection is the Data Source attribute which is used to specify the full path and file name of the Access .mdb file. A OLE DB Provider for Jet connection string would therefore look something like this:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\path\filename.mdb;

The only other commonly used parameters are User ID and Password which are used to specify user authentication values. A user name of "admin" and a blank password are the defaults we tend to use resulting in the following default connection string:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\path\filename.mdb; User ID=admin; Password=;

SQLOLEDB

The OLD DB provider for MS SQL Server is a little bit more complex. The Data Source parameter is once again used, but this time it specifies the name or address of the SQL Server. Because SQL Server can run multiple databases and we haven't yet picked one we need another parameter to indicate the databse we want to use. This parameter is called Initial Catalog. That combined with the same parameters as above for User ID and Password results in this basic form:

Provider=SQLOLEDB; Data Source=server_name_or_address; Initial Catalog=database_name; User ID=username; Password=password;

I'm not sure if it's more because of the networking required or the additional capabilities of the database, but there are more parameters you can specify when using SQLOLEDB than with MS Jet. You can get a list of all of them from the SQL Server ADO Programmer's Reference in the SQL Server Books Online. In particular, you're looking for the Provider-Specific Connection Parameters of the connection object.

That was a lot to take in all at once, but its right here so you can come back and review it later on if you want. Now Lets get connected!

There are two styles of ODBC connection strings: those that utilize a DSN (Data Source Name) and those that do not (often called DSN-Less connections). The DSN Connection first.

DSN Connections

DSN connections store their connection information in the Windows Registry. So in the Windows 98/NT Control Panel you'll find an Icon called "ODBC Data Sources" which provides a interface to collecting connection settings. In Windows 2000, it's been moved to the "Administrative Tools" Folder in the Control Panel and renamed "Data Sources (ODBC)", but it functions the same.

Windows 95/98/NT
Ok From Start,Settings,Windows Control Panel
Double Click on the ODBC Data Source Icon.

Windows 2000
From Start, Settings, Control Panel,Administrative Tools.
Double Click on the Data Sources ODBC Icon.

Ok now we'll set up our DSN