This article mainly introduces the detailed explanation of adodb.recordset.open(rs.open) method parameters. Friends in need can refer to it.
ADO Recordset object
The ADO Recordset object is used to hold a recordset from a database table. A Recordset object consists of records and columns (fields).
In ADO, this object is the most important and most commonly used object to operate on data in the database.
ProgID
set objRecordset=Server.CreateObject(ADODB.recordset)
When you first open a Recordset, the current record pointer will point to the first record, and the BOF and EOF properties will be False. If there are no records, the BOF and EOF properties are True.
Recordset objects can support two types of updates:
Immediate update - Once the Update method is called, all changes are written to the database immediately. Batch Update - The provider will cache multiple changes and then transfer these changes to the database using the UpdateBatch method.
In ADO, 4 different cursor (pointer) types are defined:
•Dynamic Cursors - Allows you to view additions, changes and deletions made by other users
•Keyset Cursor - Similar to a dynamic cursor, except that you cannot see additions made by other users, and it prevents you from accessing records that have been deleted by other users. Data changes made by other users are still visible.
•Static cursor - Provides a static copy of a recordset that can be used to find data or generate reports. Additionally, additions, changes, and deletions made by other users will not be visible. This is the only cursor type allowed when you open a client Recordset object.
•Forward cursor only - allows only forward scrolling in the Recordset. Additionally, additions, changes, and deletions made by other users will not be visible.
The type of cursor can be set through the CursorType property or the CursorType parameter in the Open method.
Note: Not all providers support all methods and properties of the Recordset object.
Friends who want to know more about ADO Recordset objects can go here to view more content.
If it is just reading and does not involve update operations, then use 1, 1
If it involves read and update operations, you can use 1,3 or 3,2
Open method prototype of Recordset object:
Open([Source],[ActiveConnection],[CursorType],[LockType],[Options])
CursorType Cursor type:
Const adOpenForwardOnly = 0
The forward cursor is the default cursor and provides the fastest running performance. Use it to open the recordset and get all the results in order from pair to end. It does not support backward scrolling and only allows one-way movement between results.
Const adOpenKeyset = 1
A static cursor reflects the status of the data in the table when the cursor is first opened. The cursor cannot find out whether the data rows in the underlying table have been updated, deleted, or new data has been added. However, unlike continent cursors, which can only move forward, static cursors can scroll forward and backward between results.
Const adOpenDynamic = 2
Keyboard-driven cursors can query some, but not all, changes in the underlying data rows in a table. In particular, it accurately reflects whether the data has been updated. But it cannot find out whether other users have deleted data rows (deleted data rows will leave holes in the recordset). Keyboard-driven cursors support forward and backward scrolling through results.
Const adOpenStatic = 3
Dynamic cursors are the richest cursor type. When the cursor is open, you can query any changes made to the table by other users and support scrolling.
LockTypeLock type:
Const adLockReadOnly = 1
The default locking type, read-only locking allows multiple users to read the same data at the same time, but cannot change the data.
Const adLockPessimistic = 2
Open the data object with pessimistic locking. This approach assumes that other users will have access to the data while you are editing the record. At this point once you start editing a record, other users cannot access the data.
Const adLockOptimistic = 3
Open the data object using optimistic locking. This approach assumes that no other users will be accessing the data while you are editing the record. Other users cannot access the record until changes are made.
Const adLockBatchOptimistic = 4
Use this type when performing multi-row batch updates
Options parameters:
The Options parameter indicates the type of command string used to open the recordset. Telling ADO information about the contents of the string being executed helps to execute the command string efficiently.
adCMDTable. The string being executed contains the name of a table.
adCMDText. The string being executed contains a command text.
adCMDStoredProc. The string being executed contains a stored procedure name.
adCMDUUnknown. The contents of the string are not specified. (This is the default value.)
To put it simply:
RS.OPEN SQL,CONN,A,B
A: ADOPENFORWARDONLY (=0) is read-only, and the current data record can only be moved downwards
ADOPENSTATIC (=3) read-only, the current data record can be moved freely
ADOPENKEYSET(=1) can be read and written, and the current data record can be moved freely
ADOPENDYNAMIC(=2) can be read and written, the current data record can be moved freely, and new records can be seen
B: ADLOCKREADONLY(=1) default value, used to open read-only records
ADLOCKPESSIMISTIC(=2) pessimistic locking
ADLOCKOPTIMISTIC(=3) optimistic locking
ADLOCKBATCHOPTIMISTIC(=4) batch optimistic locking