The following is a brief introduction to ADO data control properties. It isn’t meant to encompass every situation-- especially in the area of cursors and locks.
The first article in our series, "Creating an ADO Data Control", introduced you to the ease with which you can define a recordset using the ADO data control. Before we continue with that series, you should familiarize yourself with that control’s many properties, which we've reviewed in Table A. We’ve discussed only those properties that play a part in the actual data retrieval and management process. (Remember, we’re not working with the standard data control--we’re working with the ADO data control.)
Table A: ADO data control properties
Property Description
|
ConnectionString |
A string that tells ADO how to connect to the data source. This string might contain an OLEDB provider, the name of a server, or it might identify just the database you’re accessing. |
|
RecordSource |
A string that identifies the actual data source. This string might be the name of a table. More than likely it will be a SELECT statement identifying only those fields and records you want to access. |
|
CommandType |
This property specifies the type of information identified by the RecordSource property. There are four possibilities: adCmdText (SQL), adCmdTable (table), adCmdStoredProc, and adCmdUnknown. The default property setting is adCmdUnknown. Specifying a more specific type improves performance. However, a mismatch will return an error. |
|
CursorLocation |
You can specify whether your cursor resides on the client or the server. A client-side cursor, adUseClient, improves response time, but increases network traffic as all the data must be sent to the client each time the cursor is created. On the other hand, a client-side cursor, adUseServer, produces less network traffic but requires additional resources from the server. In addition, a server-side cursor may limit the cursor’s functionality. |
|
CursorType |
You have four choices when determining the type of cursor you want for your recordset. Refer to Table B for more information on cursor types. |
|
LockType |
This property controls the locking behavior for the records in your recordset. Refer to Table C for more information. |
|
MaxRecords |
Limits the size of the recordset. The default is zero, which means the connection returns all records that satisfy the query. If you supply a value, regardless of the number of records the query matches, the connection will return only the specified amount. |
|
CacheSize |
Controls the number of records kept in the provider’s buffer and also controls the number of records retrieved into local memory at one time. |
|
CommandTimeout |
Determines the amount of elapsed time between the request for data and the cancel command. If you set this property to 0, VB will not cancel the request. |
|
UserName |
The user name you need to log into your data source. If this name isn’t included in the ConnectionString property, you must specify it here. |
|
Password |
The password you need to log into your data source. If this password isn’t included in the ConnectionString property you must specify it here. |
|
EOFAction |
Determines how the control reacts when you try to move past the last record when the current record is the last record in the recordset. The setting adDoMoveLast (the default), keeps the cursor at the last record; adStayEOF disables the control’s Next button; adDoAddNew moves past the last record and displays a blank form so you can enter a new record. |
|
BOFAction |
This property is similar to the EOFAction, except that it works with the first record instead of the last. In other words, if you click the Previous button while sitting on the first record, the adDoMoveFirst will stay at the first record; adStayBOF disables the Previous button. |
Once the ADO data control is defined, adding bound data fields is a snap. At that point, you’re about ready to start working with individual records. This is where the cursors come in. The type of cursor you choose has a dramatic effect on your application’s performance. Carefully consider the CursorLocation and CursorType properties. We’ve already discussed the CursorLocation. ;Table B contains more specific information about the CursorType settings.
Table B: CursorType arguments
Setting Description
|
adOpenDynamic |
Dynamic cursors require more overhead, but they immediately update changes to underlying tables. |
|
adOpenKeyset |
This option is similar to the dynamic setting, but you can’t see additions made by other users. You can see changes and deleted record aren’t available. |
|
adOpenStatic |
If seeing changes, additions, and deletions isn’t important, consider using a static cursor. You can still make changes, additions, and deletions if necessary. |
|
adOpenForwardOnly |
A forward cursor is similar to a static cursor except for one point. You can only move forward through the recordset. You won’t use this setting when working with data controls. |
That brings us to the LockType property. There are four lock settings that pertain to recordsets using ADO. Locking schemes are set by the DBMS and these settings will have unique responses in different systems. Table C explores the SQL Server lock settings.
Table C: LockType arguments
Setting Description
|
adLockReadOnly |
There are no locks on the data because you can’t change the data. That way, other users can still access and change data. |
|
adLockPessimistic |
Locks a record as soon as you retrieve it so other users can’t change it. |
|
adLockOptimistic |
Waits to lock a record until you actually update it. |
|
adLockBatchOptimistic |
Used when updating records in batches instead of one at a time. |
By default, ADO uses optimistic locking. That means a record isn’t locked until it’s actually updated. If a record is updated after it’s retrieved, an error occurs and the record isn’t updated. If you want to ensure that no one changes a record after you retrieve it, use pessimistic locking. That way, the record is locked once you retrieve it. This arrangement is less desirable as it increases overhead.
Comments