Connection Object Properties

Because the Connection Object is the backbone to the data, you would expect it to have a few configurable properties giving developers control over their data connection. The following table lists the properties that you configure for the data connection.

Properties of the Connection Object

You do not need to set any of the above properties in order to open a connection to your database, however, they will give you greater control over your connection. The following excerpt will explain each Property in detail:

Attributes: Because the Attributes Property is provider-dependent, you should check whether your provider supports either feature. Microsoft Access databases do not support transactions, therefore, this property does not apply to them. The Attributes property can both be read and written to. This property may contain either the value of adXactCommitRetaining (131072) or adXactAbortRetaining (262144).

Note: For the values above, you can use either the name or the numeric value associated with it (as shown in parentheses). If you wish to use the property text name, you must include the adovbs.inc file.

You can use these properties to control how transactions are re-engaged once they have been committed or aborted. If a connection is retained, it renews itself after an update or abort. adXactCommitRetaining will begin a new transaction automatically after a call to CommitTrans. adXactAbortRetaining will automatically begin a new transaction after the RollBackTransaction is called. The Attributes property is settable at any time.

CommandTimeout: The CommandTimeout property allows both reading and writing. You can use this property to set the duration, in seconds, that a SQL query is allowed to execute. If the query takes longer then the specified time in seconds, the query is aborted and the execute method fails. This property is useful in slow network connections, backlogged servers, or large database access.

An example of large database problems would be a Microsoft Access database growing to a file size of 50 Megabytes. Microsoft Access can support databases as large as 2 Gigabytes, however, file access begins to slow once the database size reaches 50 MB. If your database grows as large as 50 MB, or you anticipate it growing to that size, you will want to consider upsizing to Microsoft SQL Server 7.0 as explained in the Upgrading from Access to SQL Server section of this guide.

ConnectionString: The ConnectionString Property allows you to specify the database you wish to connect to. This property can be both read and written to. It is through this property that you can connect using either an ODBC DSN or DSN-less connection to the database. These connection types will be covered later by example.

The ConnectionString Property is made up of a series of associations, similar to pairs inside of Perl. Each pair within the association has a name and a value assigned to it. Semicolons separate each pair inside the ConnectionString. The following is a list of the ADO recognized names inside the ConnectionString with their abbreviations in braces:

Connection String Parameters

An example of the ConnectionString is illustrated below.

________________________________________________________

<%
Dim cn
' Open a connection using the Microsoft ODBC provider.
Set cn = Server.CreateObject ("ADODB.Connection")
cn.ConnectionString="driver={SQL Server};server=bigsmile;uid=sa;pwd="
cn.Open
cn.DefaultDatabase = "pubs"
%>

________________________________________________________

This example opens a connection to the pubs database on the bigsmile server using the username sa and a blank password.

ConnectionTimeout: This property sets how long, in seconds, the open method will try to open the database. If the database does not open within the specified time, the operation is considered a failure. This property is both readable and writable.

CursorLocation: This property lets ADO know where the data from the Open command will be kept. The Connection Object does not use the value set in this property, but the Recordset Object inherits its value. Valid settings for this property are shown in the below table. Note that numeric values must be specified unless the adovbs.inc file is included in your ASP pages. For more information on this file, please visit the Microsoft adovbs.inc file section of this guide. The below table shows the three different locations for the data to be kept: the client, the server, and a client batch.

ADO Cursor Location

The first location, the client, is a special cursor location. The client location will allow the data to be queried from the server and then transmitted back to the client. The client then makes the modifications to the data. Once the modifications are made, the modified set of data, or Recordset, is then transmitted back to the server for updating. The client batch is the next one of interest. The client batch is a method where the data is transmitted in batches back and forth to the server and client. The client may modify 20 records and then send back that Recordset for a batch update. Microsoft Internet Explorer is the only browser that supports both of these methods. For purposes of development and illustration, the server will be used as the data location.

DefaultDatabase: The DefaultDatabase property can be both read and written to. This property tells the connection which database to connect to. When ODBC connections are made, you will specify the default database that was created for you, making this property unnecessary. When using a DSN-less connection, you can either use this property or specify the database in the Open method call.

IsolationLevel: The IsolationLevel property allows you to set the level of isolation before the transaction begins. The level of isolation determines if a change to the database will occur before or at the end of the transaction. It also determines what degree of visibility you currently have to data changed by other transactions. A fully isolated transaction has no overlap with another transaction occurring at the same time. The following phenomena are commonly used to characterize isolation levels:

Dirty Read: Transaction A changes a row. Transaction B reads the changed row before transaction A commits the change. If transaction A aborts the change, transaction B will have read a row that is considered to have never existed.

Non-repeatable read: Transaction A reads a row. Transaction B updates or deletes that row and commits this change. If transaction A attempts to reread the row, it will receive different row values or discover that the row has been deleted.

Phantom: Transaction A reads a set of rows that satisfy some search criteria. Transaction B inserts a row that matches the search criteria. If transaction A re-executes the statement that reads the rows, it receives a different set of rows.

According to these phenomena, the isolation levels defined by OLE DB are as follows:

Chaos: All transactions can overwrite each other’s changes before the changes are committed. This setting is very similar to not having any transactions.

Read Uncommitted (Browse): A transaction operating at the Read Uncommitted level can see uncommitted changes made by other transactions. At this level of isolation, dirty reads, non-repeatable reads, and phantoms are all possible.

Read Committed (Cursor Stability): A transaction operating at the Read Committed level cannot see changes made by other transactions until those transactions are committed. At this level of isolation, dirty reads are not possible, but non-repeatable reads and phantoms are possible.

Repeatable Read: A transaction operating at the Repeatable Read level is guaranteed not to see any changes made by other transactions in values it has already read. At this level of isolation, dirty reads and non-repeatable reads are not possible, but phantoms are possible.

Serializable (Isolated): A transaction operating at the Serializable level guarantees that all concurrent transactions will interact only in ways that produce the same effect as if each transaction were entirely executed one after the other. At this isolation level, dirty reads, non-repeatable reads, and phantoms are not possible.

The below table shows the name of the value, the numeric value, and the related phenomena from above.

Isolation Levels Explained

As you can see, some settings are the same, but have different names. In such cases, you can use either setting.

Mode Property: The Mode property lets the connection know exactly how to lock the data that it is trying to access. This setting can be used to constrict concurrent access to the same data source, guaranteeing the accuracy of the data.

Mode Property Constants

Provider: The Provider Property allows you to identify the data source provider. This property can be both read and written to. The default value of the provider property is MSDASQL.1, which controls access to all current ODBC data sources.

Version: This property allows you to display the current version of the ADO that the server is running. At the time of this printing, the version is MDAC 2.1.1.3711.11 (GA).