ASP Database Cursor

[Total: 0    Average: 0/5]

Cursor and Lock Types

The Recordset Cursor and Lock Type

The cursor type and lock type you specify when opening a recordset is important.

By default, the cursor type is a forward only moving cursor. This means that it makes only one trip through your rows of data and cannot go backwards. This type of cursor is called an adOpenForwardOnly cursor. The default lock type is is called an adLockReadOnly lock type. What this lock type does is that it only allows data to be read from, but not altered. Therefore, if you need to update information in a record in your database, you would need to use a different lock type. For most ASP applications, the default cursor and locktypes will be fine. But when you need to alter data, you will need to consider carefully which cursor and lock type best fit your situation. Determining these types have a direct effect on the performance of the dedicated server and therefore your ASP pages. Here is the code for your recordset object and how the cursor and lock types are used.

Set RS = Server.CreateObject(“ADODB.Recordset”)
RS.Open tablename, connectionname, cursortype, locktype, adCmdTable

Cursor Types

An adOpenForwardOnly cursor (as said above) only supports moving through a recordset in a forward motion. It does not support backtracking through a recordset and will only give you one time through the record. This method is the default because it is the fastest cursor to use.

An adOpenStatic cursor is different than an adOpenForwardOnly in that it can move backwards through the data. It cannot however, tell whether any data has been modified since the last time through.

The adOpenKeyset cursor is one better than the adOpenStatic in that it can detect whether data has been updated. It supports backwards scrolling through the content, but it cannot tell whether any rows have been deleted. This can leave a hole in your recordset.

An adOpenDynamic cursor is the most flexible cursor available to use. It fully supports scrolling and it also can detect any changes to the data since the last past through. This, however, is the slowest cursor to use.
Lock Types


An adLockReadOnly is the default lock type. It allows multiple users to read the same data at the same time, but does not allow any altering of this data.

An adLockPessimistic lock type prevents anyone from accessing your data as soon as you start editing it.

An adLockOptimistic lock type allows others to access the data only until it any updates have been added.

An adLockBatchOptimistic lock type is used when making batch updates.
If all you are trying to do is simply display data from your database, then you should use the default lock type. However, if you are updating any data, the most recommended lock type is adLockOptimistic because it still allows other users to view the data until you make your update and therefore causes the least amount of inconvenience and interferance. I hope this has helped to clear up any questions regarding the lock and cursor type of the recordset object. See ya next time!

~Geoff Swartz

Times Viewed: 11

Leave a Reply

Your email address will not be published. Required fields are marked *