Database: ado object cursor type (Connection, RecordSet) and three axes for displaying data records

 

For Connection:

The CursorLocation attribute, including the default position of the cursor service program. This value will be automatically inherited by the Recordset object using this Connection object.

 

There are two common types of cursors:

AdUseClient
Use the client cursor provided by the local cursor library. Many of the features that the local cursor engine usually allows may be unavailable for the cursor provided by the driver, so using this setting is good for those features to be enabled. AdUseClientBatch is synonymous with adUseClient and supports backward compatibility.

AdUseServer
Default value. Use the cursor provided by the data provider or driver. These cursors are sometimes very flexible and have additional sensitivity to changes made to the data source by other users. However, some functions of Microsoft Client Cursor Provider (such as disconnected record sets) cannot be simulated by server-side cursors. These functions cannot be used through this setting.

Note:

Using adUseClient means that the data needs to be transmitted to the client before the operation, and does not have synchronization (after the query is executed, another program changes the content in the database before obtaining the query result, the result of the client is to change the previous data ). It can better support functions such as the RecordCount attribute (because all the data has been transferred to the client, the program can know the number of data records ). However, this has a big impact on performance. If there is a large amount of data, the performance impact is more obvious because it is necessary to transmit all the data to the client after it is returned.

AdUseServer operates directly in the database and returns the result after obtaining the cursor pointing to the data. The processing speed is relatively fast. The specific data is waiting for the data to be obtained (such as calling MoveNext or other methods) before the operation. In many other ways, the adUesServer has limits. For example, before traversing a result set, the program cannot know the number of data entries in the result set.

 
For RecordSet:
CursorType:

The second method is commonly used in programs.AdOpenKeyset,

AdOpenForwardOnly= 0'' can only be read with MoveNext, And the backup of the database created while opening cannot instantly reflect the database record status, such as record editing and addition/deletion.

AdOpenKeyset= 1' a scroll up or down cursor creates a keyword list for the opened record, similar to the description of the record set. The data value is obtained only when the record is accessed, that is to say, you can see the modification information in real time, but you cannot immediately obtain the information about whether the data is deleted, because the keyword list is initialized in advance.
AdOpenDynamic= 2 '': it is completely scrollable. The latest data status can be obtained, and the execution efficiency will be reduced.

AdOpenStatic= 3 ''is completely scroll, but similar to adOpenKeyset, it first backs up the database file and then operates. You can disconnect the database and continue using it.

There are four types of ADO locks. We recommend that you read database data without updating it.AdLockReadOnly read-only lock;We recommend that you add, delete, and update a record.AdLockPessimistic lockTo prevent you from performing operations on the same record by others when updating a record, or entering invalid duplicate records by others at the same time. For example, there is an interval between user name registration and new name submission, to prevent other users from submitting valid names that you have already passed the re-query, or entering two identical names.

AdLockReadOnly= 1' read-only lock, operation record not allowed

AdLockPessimistic= 2' pessimistic lock. The operator locks the lock immediately after opening the lock until the modification is completed or the modification is abandoned. At this time, no one else can edit the lock.

AdLockOptimistic= 3'' optimistic lock: the lock is started only when the record is about to be updated, but it cannot be ensured that someone has changed the record before submitting the modification.

AdLockBatchOptimistic= 4'' optimistic batch lock. To use it in SQL server, you must open a scroll up or down cursor, such as adOpenKeyset and adOpenStatic.

Example (asp ):

When reading the database

Set res = server. CreateObject ("adodb. recordset ")
Res. open SQL statement, database connection, 1, 1

When updating the database

Set res = server. CreateObject ("adodb. recordset ")
Res. open SQL statement, database connection, 1, 2

Show three axes of data records
(That is, the combination of the ado control and the dataGrid. They are close friends of both men and women)

Adodc1.ConnectionString = strcn
Adodc1.CommandType = adCmdUnknown
Adodc1.RecordSource = strsql
Set DataGrid1.DataSource = Adodc1
Adodc1.Refresh
DataGrid1.Refresh