The very first step in accessing database information is to establish a connection with the database source. ADO provides the Connection object to manage connections between a Web page and a database.

To establish a connection, first create an instance of the Connection object.

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

Then open the connection with the specified parameters:

connObj.Open ConnectionString, UserID, Password

which establishes the physical link to a data source. When the method successfully completes, the connection is "live" and you can issue commands against it and process the results.

The ConnectionString parameter specifies a data source, while UserID and Password are optional arguments that may be omitted if not needed when establishing the link.

The connection string typically specifies the name, location, and type of database you want to access, along with any other parameters it may require, and passed as a series of argument=value statements separated by semicolons. For example:

"Driver={SQL Server}; Server=bigsmile; Database=pubs; 
   UID=sa; PWD=pwd"

NOTE: The connection string MUST NOT contain any spaces, either before or after the equal ( = ) sign, and must be written as a single line without any line breaks. The code examples shown here are done so only for readability.

The ConnectionString can be set before opening the Connection object

connObj.ConnectionString="Driver={SQL Server}; 
   Server=bigsmile; Database=pubs; UID=sa; PWD=pwd"
connObj.Open

or can be assigned to a reference variable, that is then passed as the argument to set the connection parameter during the Open method call.

strConn="Driver={SQL Server}; Server=bigsmile; Database=pubs; 
   UID=sa; PWD=pwd"
connObj.Open strConn

The UserId and Password information must be passed ONLY once, either in the optional UID and PWD arguments in the string details, or as arguments to the Open method of the Connection object itself, but NOT in both, as the results may be unpredictable.

In most Intranets and Local Area Network ( LAN ) applications, connection strings are usually "predefined" in a Data Source Name ( DSN ).

A Data Source Name ( DSN ) contains all the information needed to locate, identify, and communicate with a particular OLE-DB or ODBC compliant database driver — the program that passes information to and from a Web application and the database. The DSN can take the form of an entry in the Windows NT registry or a text file.

Basically, a DSN is built using the ODBC Administrator in Control Panel in the Web server. With ODBC, we have a choice of creating a User, System, or File DSN.

The User and System DSN reside in the Windows NT registry. The System DSN enables all users logged on to a particular server to access a database, while the User DSN limits database connectivity to a specific user with appropriate security credentials.

The File DSN, which takes the form of a text file, provides access to multiple users and is easily transferable from one server to another by simply copying DSN files. For these reasons, the File DSN is more widely used.

Below is what a FileDSN is typically made up of:

[ODBC]
DRIVER=Microsoft Access Driver ( *.mdb )
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=281
DefaultDir=C:\Inetpub\wwwroot\aspxtreme\shared\data
DBQ=C:\Inetpub\wwwroot\aspxtreme\shared\data\dbtutor.mdb

NOTE: DSN files have a .dsn extension and by default reside in the \Programs\Common Files\ODBC\Data Sources directory, unless you specify otherwise.

As an alternative to setting a DSN, you can "compose" an ADO connection string that refers directly to an explicit provider, data source, user ID, and password ( identical to the connection string for the same DSN ), and pass that as the ConnectionString argument when opening the connection.

This has the advantage of easily porting applications on different Web servers, as there is no need to define DSNs on the server, which normally, only an administrator with root access can do. Plus, since the connection string is applied directly inline, this is more efficient programmatically.

In this case, though, you have to know the exact parameter names and values that need to be passed on to the connection string.

TIP: To get the connection string information for a given provider, create a FileDSN using the ODBC administrator in Control Panel, then open the file in any text editor.

The following shows different ways of connecting to a database using the ADO Connection object.

• Open a connection without using a Data Source Name

Set conn1=Server.CreateObject( "ADODB.Connection" )
conn1.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=dbName.mdb"
conn1.ConnectionTimeout=30
conn1.Open

• Open a connection using a File DSN and ODBC tags.

Set conn2=Server.CreateObject( "ADODB.Connection" )
conn2.ConnectionString="FileDSN=Tutor.dsn"
conn2.Open

• Open a connection using a System DSN and OLE-DB tags.

Set conn3=Server.CreateObject( "ADODB.Connection" )
conn3.ConnectionString="Data Source=Pubs; 
   User ID=sa; Password=pwd;"
conn3.Open

• Open a connection using a System DSN and individual arguments instead of a connection string.

Set conn4=Server.CreateObject( "ADODB.Connection" )
Conn4.Open "DataSourceName", "admin"

When you have concluded your operations over an open Connection, use the Close method to free any associated system resources.

connObj.Close

Closing an object does not remove it from memory; you may change its property settings and use the Open method to open it again later. To completely eliminate an object from memory, set the Connection object variable to Nothing.

Set connObj=Nothing

The tutorials in this series use "DSN-less" connections similar to the first method, and also use an alternate method of establishing a connection, described in the following section.