What is a recordset?
A recordset is a collection of records that are returned when we request data from a
database with SQL. ADO contains the Recordset object,
to make it possible to manipulate these data. You can amongst others loop through the records in a recordset, change records or add records (as long as the cursor isn't
ReadOnly), storing the data in a file, XML or an array.
What is a Cursor?
To be able to navigate between the records a recordset needs a cursor. The cursor stands
on the present record (that is when the records are in a recordset). There are different types
of cursors, with different possibilities. Below you can find an overview of the different cursors:
| Cursor |
Properties |
| adOpenForwardOnly |
Only looping from start to end through the recordset.
Updates from others are not visible.
|
| adOpenStatic |
Looping possible.
Updates from others are not visible.
|
| adOpenDynamic |
Looping possible.
Updates from others are visible.
|
| adOpenKeyset |
Like adOpenDynamic, but with some different details.
|
A detailed overview of cursor-properties can be found on
hier.
What is the Lock-type?
A recordset can be read-only, or changeable. Besides read-only there are
three kinds of changeable recordsets. Below you can find an overview with the possible
Lock-types:
| Lock-type |
Properties |
| adLockReadOnly |
Read-only |
| adLockPessimistic |
When changes are made in a record, this will be closed by other users.
When the Update method is called, the record will be changed and released
in the database.
|
| adLockOptimistic |
Changes are made in the database when the Update method
is being called. Only then ADO tries to get a lock.
|
| adLockBatchOptimistic |
Usefull when more changes hav to be made. First you adjust all the records in the
recordset and then you use the UpdateBatch method to
change all the records at once in the database.
|
Which Cursor- and Lock-type should I use?
A recordset that has more possibilities then is needed, wastes a lot of memory
an costs speed. Therefore it's needed to use the best type of recordset for what you
want. If you don't have to do updates, use the ReadOnly recordset. In other cases
(in general) use the Optimistic or BatchOptimistic.
Within ASP it's actually not necessary to see the changes from other users.
Therefore it's wise to use ForwardOnly or Static where possible. In
this table
you can see when to switch to Dynamic or Keyset cursors.
|
