Connection by George Hernandez

The Connection object defines the connection of the other ADO objects to the data source.

Basic Usage
Properties, Methods, and Events

Basic Usage

The Connection object is usually used in this order:

Create Connection
Open Connection
Use Connection
Close and Release Connection

Create Connection

Creating the Connection object instantiates it and makes it available for use.

In Visual Basic this is done with syntax similar to the following:

Dim cnnObject1 as New ADODB.Connection
Dim cnnObject2 as ADODB.Connection

In VBScript this is done with syntax similar to the following:

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

In JavaScript/JScript this is done with syntax similar to the following:

cnnObject1 = new ActiveXObject("ADODB.Connection");

Open Connection

Opening a Connection object forms a physical connection between the app and the database.

The syntax for this method is as follows:

connection.Open ConnectionString, UserID, passsword, options

The latter three arguments may unnecessary or redundant. EG: The UserID and password might already be included in the ConnectionString. The options argument may determine whether the method occurs synchronously (default) or asynchronously.

The ConnectionString is the trickiest of the four arguments. It has information used by the different entities between the app and the data. These entities can include the following:

The ADO Connection object itself.
The ADO DLL.
The data provider (eg ODBC)
The data source DBMS (eg Access or SQL Server).

Here ConnectionString syntax for typical ADO Connections:

[Provider=MSDASQL;] {DSN=name; | FileDSN=FileName}; [DATABASE=database;] UID=user; PWD=password

[Provider=MSDASQL;] DRIVER=driver; SERVER=server; DATABASE=database; UID=user; PWD=password

Here are some of the other arguments that may be contained by the ConnectionString argument:

Remote Provider
Remote Server
URL

Open Connection Examples

Here are some examples using an imaginary ADO Connection called cnnX:

If there is just one argument it is assumed to indicate a DSN name that uses an ODBC provider. That is the next two are equivalent:

cnn1.Open "myNorthwind"
cnn1.Open "Provider=ODBC;DSN=myNorthwind"

The user and password can be passed within in the ConnectionString argument or as another argument to the Open() method:

cnn1.Open "DSN=myNorthwind;UID=sa;PWD=pwd"
cnn1.Open ""myNorthwind", "sa", "pwd"

Here are DSN-less connections to SQL Server, MySQL, and MS Access (via absolute path, UNC path, and via the ASP Server object to acquire a relative path):

cnn1.Open "driver={SQL Server}; _
           server=myServer; _
           uid=myUserName; _
           pwd=myPassword; _
           database=myDatabase"

cnn1.Open "driver=SQLOLEDB; _
           server=myServer; _
           uid=myUserName; _
           pwd=myPassword; _
           database=myDatabase"

cnn1.Open "driver={MySQL ODBC 3.51 Driver}; _
           server=myServer; _
           uid=myUserName; _
           pwd=myPassword; _
           database=myDatabase; _
           port=3306; _
           option=3;"

cnn1.Open "driver={Microsoft Access Driver (*.mdb)}; _
           dbq=G:\InetPub\wwwroot\mySite\x\Northwind.mdb _
           uid=myUserName; _
           pwd=myPassword;"

cnn1.Open "driver={Microsoft Access Driver (*.mdb)}; _
           dbq=\\myServer\InetPub\wwwroot\mySite\x\Northwind.mdb; _
           uid=myUserName; _
           pwd=myPassword;"

cnn1.Open "driver={Microsoft Access Driver (*.mdb)}; _
           dbq=" & Server.MapPath ("..\x\Northwind.mdb") & "; _
           uid=myUserName; _
           pwd=myPassword;"

cnn1.Open "Provider=Microsoft.Jet.OLEDB.3.51; _
           Data Source=" & Server.MapPath ("..\x\Northwind.mdb") & "; _
           User ID=;Password=;"
'For Access 97

cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0; _
           Data Source=" & Server.MapPath ("..\x\Northwind.mdb") & ";"
'For Access 2000. Both actually default to no User ID or Password.

When testing out Access, do not have the database open in the background!

Connections variable names often have synonyms and are usually case insensitive :

Server = host = Data Source = DataSource = Address = Addr = Network Address. The name or network address of the instance of the data source, usually a database server.

Uid = User ID = Username = User Name. The user account to use when connecting.

Pwd = Password. The password for user account.

Database = Initial Catalog. The default database for the connection.

Port. The port MySQL is using to listen for connections. MS SQL Server is usually 1433. MySQL is usually 3306. Specify -1 to use a named-pipe connection.

These open connections to Microsoft Index Server and LDAP-compliant directory services:

cnn1.Open "Provider=MSIDX"
cnn1.Open "Provider=ADSDSOObject"

Use Connection

Typically, a Connection object is created, opened, and then used invisibly by other objects before it is closed and released. However it is possible to execute a command directly from a Connection object, especially for quick and dirty non-row returning commands. EG:

Set cnn1 = Server.CreateObject("ADODB.Connection")
cnn1.Open "DSN=myDB", "sa", "pwd"
strSQL = "INSERT INTO tblA (" & _
            "LogDate, " & _
            "TeamID, " & _
            "LicenseeID, " & _
            "MediaID, " & _
            "FileName, " & _
            "MediaType) " & _
          "VALUES ('" & _
            CStr(Date) & "', '" & _
            strTeamID & "', '" & _
            CStr(lngLicID) & "', '" & _
            strMediaID & "', '" & _
            strFileName & "', '" & _
            strMediaType & "')"
cnn1.Execute strSQL,, 1
cnn1.Close
Set cnn1 = Nothing

The Execute() method of the Connection object has three parameters:

connection.Execute CommandText[, RecordsAffected, Options]

CommandText is a string for a SQL statement, a stored procedure, an URL, or provider-specific text.
RecordsAffected is an optional Long variable returned from the provider with the number of records affected.
Options is an optional Long value that indicates what CommandText is. The most common values for Options are as follows:
- -1 unspecified
- 1 text
- 2 table
- 4 stored procedure
- 8 unknown. The default.

Close and Release Connection

This closes the physical connection between the app and the database. The syntax is as follows:

connection.Close

This uninstantiates the Connection object and releases the memory address used by the object. The syntax is as follows:

Set connection = Nothing

Properties, Methods, and Events

Properties	Methods	Events
Attributes CommandTimeout ConnectionString ConnectionTimeout CursorLocation DefaultDatabase IsolationLevel Mode Provider State Version	BeginTrans CancelMethod CloseMethod CommitTrans Often listed as "BeginTrans, CommitTrans, and RollbackTrans". Execute Open OpenSchema RollbackTrans Often listed as "BeginTrans, CommitTrans, and RollbackTrans". Save	BeginTransComplete CommitTransComplete Often listed as "BeginTransComplete, CommitTransComplete, and RollbackTransComplete". ConnectComplete Disconnect Often listed as "ConnectComplete and Disconnect". ExecuteComplete InfoMessage RollbackTransComplete Often listed as "BeginTransComplete, CommitTransComplete, and RollbackTransComplete". WillConnect WillExecute

Page Modified: (Hand noted: 2007-10-11 19:52:27Z) (Auto noted: 2008-01-02 02:18:23Z)