VB Prodigy Tutorial (continued)--vb ADODB Connection object Introduction

This article welcomes the non-commercial use of reprint, but need to indicate from the "Programming Network" and the corresponding URL link.

Connection Object Introduction

The connection object supports a number of properties that can be used to manipulate the current connection state or to obtain information about some basic connection objects. Some properties are read-only and others are read-write.

1.Attributes Properties

The Attributes property sets or returns an integer value that indicates one or more attributes of an object. For connection objects, the Attributes property is read/write and its value may be one or more of the following XactAttributeEnum values ( Default is 0).

▲adxactcommitretaining: Performs a reserved commit, that is, starting a new transaction by automatically invoking CommitTrans. This constant is not supported by all providers.

▲adxactabortretaining: Performs a reserved upper-middle, that is, by automatically invoking RollbackTrans to start a new transaction. This constant is not supported by all providers.

2.CommandTimeout Properties

The CommandTimeout property sets or returns a Long value that indicates the time, in seconds, to wait for the command to execute. The acquiescence value is 30, which indicates the time to wait before executing the command before terminating the attempt and generating an error. Use the CommandTimeout property on the Connection object or command to allow the Execute method call to be canceled due to network congestion or delays caused by overloading the server. If the command execution does not complete within the time interval set in the CommandTimeout property, an error occurs, and ADO cancels the command. If you set this property to zero, ADO waits indefinitely until the command completes.

3.ConnectionString Properties

The ConnectionString property sets or returns the string value that contains the information used to establish the connection to the data source. Use the ConnectionString property to include a series of argument= separated by a semicolon by passing The detailed connection string for the value statement can specify a data source. ADO supports the four parameters of the ConnectionString property, and any other parameters are passed directly to the provider without ADO processing.

▲provider parameter: Specifies the name of the provider to use for the connection.

▲file Name parameter: Specifies the file name of the specific provider that contains the preset connection information.

▲remote Provider Parameter: Specifies the provider name to use when opening a client connection.

▲remote Server parameter: Specifies the path name of the server to use when opening a client connection.

Example code that uses the ConnectionString property of the Connection object

Public Sub connectionstring_example ()

Dim Connection1 as ADODB. Connection
Dim Connection2 as ADODB. Connection
Dim Connection3 as Adodb.connection
Dim Connection4 as ADODB. Connection
' Do not use data source name (DSN) to open connection
Set connection1=new ADODB. Connection
Connection1. Connectionstring= "Driver={sql Server};" &_
"Server=myserver;uid=sa;pwd=password;database=pubs"
Connection1. Connectiontimeout=30
Connection1. Open
' Use DSN and ODBC tags to open the connection.
Set connection2=new ADODB. Connection
Connection2. Connectionstring= "dsn=pubs; Uid=sa; Pwd=password; "
Connection2. Open
' Use DSN and OLE DB tags to open the connection.
Set connection3=new ADODB. Connection
Connection3. connectionstring= "Data source=pubs; User Id=sa; Password=password; "
Connection3. Open
' Open the connection using DSN and a single parameter instead of a connection string.
Set connection4=new ADODB. Connection
Connection4. Open "Pubs", "sa", "pwd"

End Sub

Note: If you do not have the data Access Object in the Check Reference dialog box, the program will make an error. In the above program section, you first define the variables for 4 connection objects. These 4 variables correspond to 4 different ways of joining each other. Statement set Connection1=new ADODB. Connection is to create a new Connection object, and then call the Connection object's open method to open the database connection.

4.ConnectionTimeout Properties

The ConnectionTimeout property sets or returns a Long value (in seconds) indicating when the connection is open. The default value is 15, which indicates the time waiting to establish a connection before terminating the attempt and generating an error. Use the ConnectionTimeout property of the Connection object when a connection attempt must be discarded because of a network congestion or a delay caused by the overload of the server. If the time elapsed before opening the connection exceeds the time set on the ConnectionTimeout property, an error occurs and ADO cancels the attempt. If you set this property to zero, ADO waits indefinitely until the connection is opened.

5.DefaultDatabase Properties

The DefaultDatabase property can set or return the name of the default database on the specified Connection object.

Example code that uses the Defaultdababase property of the Connection object.

Public Sub defaultdatabase_example ()
Dim Connection1 as ADODB. Connection
Set connection1=new ADODB. Connection
Connection1. Connectionstring= "Driver={sql Server};" &_
"Server=myserver;uid=sa;pwd=password"
Cnn1. Open
Cnn1. defaultdatabase= "Pubs"
End Sub

6.IsolationLevel Properties

The IsolationLevel property indicates how the connection object handles the object.

7.Mode Properties

The Mode property sets or returns the value of one of the following ConnectModeEnum, indicating the available permissions used to change the data in connection.

▲admodeunknown: Default value. Indicates that the permission has not been set or cannot be determined.

▲admoderead: Indicates that the permission is read-only.

▲admodewrite: Indicates that the permission is write-only.

▲admodereadwrite: Indicates that the permission is read/write.

▲admodesharedenyread: Prevents other users from opening the connection with Read permission.

▲admodesharedenywrite: Prevents other users from opening the connection with write permission.

▲admodeshareexclusive: Prevents other users from opening the connection.

▲admodesharedenynone: Prevents other users from opening the connection with any permissions.

8.Provider Properties

The Provider property indicates the name of the current data provider, or the provider name used without the specified name when using the open () method. However, when calling the open method, specifying the provider in multiple places can have unpredictable consequences. If no provider is specified, this property defaults to Msdasql (Microsoft OLE DB Provider for ODBC).

Example code that uses the provider property of the Connection object.

Public Sub provider_example ()

Dim Connection1 as ADODB. Connection
Set connection1=new ADODB. Connection
Connection1. Provider= "microsoft.jet.oledb.3.51"
Connection1. Open "C:\samples\northwind.mdb", "admin", ""

End Sub

9.State Properties

The State property is available to all applicable objects and is used to indicate that its object status is turned on or off. You can use the state property at any time to determine the current status of the specified object. The property is read-only and returns a long value of one of the following constants.

▲adstateclosed: The default value indicates that the object is closed.

▲adstateopen: Indicates that the object is open.

▲adstateconnecting: Indicates that the Recordset object is connecting.

▲adstateexecuting: Indicates that the Recordset object is executing a command.

▲adstatefetching: Indicates that the row of the Recordset object is being read.

Example code that uses the state property of the Connection object.

Public Sub state_example ()

Dim Connection1 as ADODB. Connection
Dim statestring as String
Set connection1=new ADODB. Connection
Connection1. Connectionstring= "dsn=pubs; Uid=sa; Pwd=password; "
Connection1. Open
Select case Connection1. State
Case adstateclosed
Statestring= "adStateClosed"
Case adStateOpen
Statestring= "adStateOpen"

End Select

' Shows the status of the connection.

MsgBox "Connection1. State: ",, statestring

End Sub