ASP: CursorType and LockType

Tweet

With ASP we usually connect and retrieve data from a database. We do it almost every day and we use the relative code without really thinking about it. Things are working and we get what we need, however... do we really know what we are doing?
I’m talking about two particular properties: CursorType and LockType.
In my experience, when I first used ASP to open a recordset, I used the two properties without really understanding their significance. But, because I am basically a curious guy, I wanted to understand what those numbers are, why I needed to use them and why I got different results, when changing them.
Let’s see together what are those two properties.

First of all, we need to consider a common snippet used for opening a recordset:

strSql = “SELECT * FROM myTable”
objRs.Open objConn, strSql, 1, 2

The first line sets our SQL query while the second line is what we use to open the recordset (objRs.Open).
The parameters we pass are:
1) objConn: our connection;
2) strSql: the SQL query;
3) 1: the CursorType;
4) 2: the LockType.
As you can see we normally use those two properties.

CursorType
As the word says, CursorType defines the type of cursor for the recordset.
When opening a MS Access table, the cursor is normally positioned on the first row, top-left position. This cursor defines the record and the field where we are, and where we can perform update, delete or insert operations.
In ASP, the use of CursorType is exactly the same: it allows us to navigate through the recordset and to execute operations.
There are 4 cursor types:
1) Forward Only: the default value. It is used when we need to move only forward through the recordset (ADO = adOpenForwardOnly; value = 0).
2) Static: we use it when we need to move forward and backward between records, and it doesn’t reflect changes made by other users (ADO = adOpenStatic; value = 3).
3) Dynamic: it allows us to move forward and backward and it reflects changes made by other users (ADO = adOpenDynamic; value = 2).
4) Keyset: again, it allows us to move forward and backward, it reflects changes made by other users, apart from insert and delete operations (ADO = adOpenKeyset; value = 1).
We can set the CursorType using two methods. Assuming that objRs is the recordset, we can set it with:

objRs.CursorType = 1

or

objRs.CursorType = adOpenKeyset

Otherwise, we can set it directly when opening the recordset (as in the first example):

objRs.Open objConn, strSql, 1

or

objRs.Open objConn, strSql, adOpenKeyset

LockType
It defines the lock type when updating the recordset.
Ok, I know it doesn’t explain much. However... remember the MS Access example above? Imagine your database is used just by one user. In this case there aren’t any problem when updating records, there won’t be any operation conflict.
Imagine now that there are two users working on the same database. What happens if both are working on the same record? Which one of the two recordset operations will be considered valid?
LockType helps us in that matter. When we know different users would operate on a recordset, we can decide what happens, setting 4 types of lock types.
1) Read Only: this is the default value and it allows just to read records (no update, insert or delete). Remember that this is the default value and that it is the less server resources consuming (ADO = adLockReadOnly; value = 1).
2) Pessimistic: it denies access to the record when an edit request is executed. It means that all the other users won’t be able to access the record. It is the most used lock type when updating records (ADO = adLockPessimistic; value = 2).
3) Optimistic: it denies access just when the update request is confirmed. Until that very moment, other users will be able to view and update the same record (ADO = adLockOptimistic; value = 3).
4) Batch Optimistic: it allows to edit more than one record in a batch. The lock is activated only when confirmed (ADO = adLockBatchOptimistic; value = 4).
As seen for CursorType, LockType can be set in a similar way:

objRs.LockType = 2

or

objRs.LockType = adLockPessimistic

As a side note, remember that the two properties used in objRs.Open are first CursorType and then LockType. That means we need to always set the CursorType, if we don’t use the default LockType.

Ok folks. That’s all for today. As usual, let me know what you think…