The 3 most important recordset properties

When developing your ASP (ADO û ActiveX Data Objects) applications, there are three recordset properties that will help you determine whether your code will run smoothly, or whether you will be up all night pulling your hair out trying to figure out WHY otherwise perfectly good code just wonÆt work, no matter what you do û

Those are Cursor Type, Lock Type, and Cursor Location. A good understanding of what these properties are and how they work is essential in having an application that will run as efficiently as possible û given its needed level of functionality.

Cursor Types:
This determines what type of cursor you would like for the recordset. Different cursor types support different directions of movement, and some support methods and properties that others do not (i.e. bookmarks, recordcount, etcà).
adOpenForwardOnly ( 0 ) -- Default
Forward only cursor. You can only move forward through the recordset and only one row at a time. This cursor will improve performance if you are only making a single pass through the recordset.
adOpenKeyset ( 1 )
Keyset cursor. The data you receive is fixed, you do not see additions or deletions. However, the data in the fixed set is up to date. All types of movement are supported.
adOpenDynamic ( 2 )
Dynamic cursor. The data is not fixed. The data you see is up to date. All types of movement are supported. It should be noted that some providers do not support bookmarks.
adOpenStatic ( 3 )
Static cursor. The data is fixed û so this is like a snapshot of the data, and all types of movement are supported.

There are two times that you can set a cursor type, and those are before you open it, or on the same line that you open the recordset. IÆll go into that syntax at the end.

Cursor Locations:
Next is the cursor location. There are four options here. You have your choice of where to declare this, as well. If you declare the cursor location for the connection object that you will use for the recordsetÆs data connection, the recordset inherits that setting by default. You can also set it before you open it.
adUseNone ( 1 )
Indicates no cursor location
adUseServer ( 2 ) -- Default
Use server side cursor (assuming that the server will support it).
adUseClient ( 3 )
Use the Microsoft client-side cursor
adUseClientBatch ( 3 )
Use the Microsoft client-side cursor (exists for backward compatibility)

A server-side cursor (when available) handles data concurrency issues better than client-side cursors. Client-side cursors must be used when creating disconnected recordsets and custom recordsets (this one is cool û IÆll have to write a FAQ on it).

Let me stop right here for just a second and say that the cursor type and cursor location have a direct relationship. If the cursorLocation property is set to adUseClient or adUseBatchClient, then only adOpenStatic cursor type is supported. So, even if you specify another cursorType, once you set the location to either adUseClient or adUseClientBatch ADO will downgrade the cursor type to adOpenStatic û so it stands to reason that you might not even want to declare one explicitly if you do use the client side cursor.

Lock Types:
HereÆs the biggie for those of use who provide web interfaces for databases. This one is the one that determines what kind of record locking (or concurrency) your recordset will have.
adLockReadOnly ( 1 ) -- Default
A read only recordset. No record locking is provided on the database server since there can be no updates whatsoever to this type of recordset.
adLockPessimistic ( 2 )
Pessimistic locking is used (go figure). The provider will attempt to lock the record once editing begins on the recordset.
adLockOptimistic ( 3 )
Any guesses here? ThatÆs right, Optimistic locking is used where the provider only provides record locking once the update method is used on the recordset.
adLockBatchOptimistic ( 4 )
Optimistic batch locking is used. Locks are issued only when the updateBatch method is used on the recordset, and not during field assignments.

Your choice of locking is dependent mostly upon the business rules for your data. Pessimistic locking should be used if you canÆt afford a dirty read û that is, pessimistic locking will lock all records that you select from the database until you explicitly release the lock on them. Airline reservation systems would have to use such a locking mechanism to ensure that two people werenÆt reserving the same seat at the same time. They can be expensive (talking server resources), especially over the web because you have to maintain a constant connection to the database while your user goes to the bathroom to think about whether he wants an aisle or window seat. Optimistic locking is much more forgiving, but it does introduce some problems as well. Dirty reads are possible, but if you use error trapping, and research the different states of a recordset (i.e. û originalValue and underlyingValue), you can check to make sure everything is ok before you do an update, and using those same properties, you can even resolve the issue without ever throwing an error back at your users. ThatÆs beyond the scope of this FAQ, however.

Ok, letÆs look at some syntax û
Here, IÆm going to create a recordset and set itÆs properties with the long form. WeÆll assume I already have a connection object called æconÆ (thereÆs already a FAQ on how to make a connection to a database) û
See that? I just closed the connection, I still have my data, and I've freed up some server resources. If you use a client side cursor, then you donÆt even need to keep your active connection. Can you say, ôMy web site can now support more visitorsö? I can. Thinking in this vein is especially important if you are using a database management system such as Access, that doesnÆt have built in safeguards and record locking like MS SQL Server. DonÆt get me wrong, IÆm not saying you donÆt have to think about these issues if you use an Enterprise Scale RDBMSà believe me, itÆs VERY important, and you DO have to think about them. All IÆm saying is that you could make a case that says itÆs even more important if you arenÆt using such a system. Carefully planning out how you will retrieve and deal with your data can vastly improve the performance of your site.

So now youÆre thinkingà ôWell, if I donÆt have an active connection to the database, why even bother setting the locktype, since itÆs not going to have any real effect on the database, itselfö, right? True enough, BUT if you donÆt set a locktype, then you have accepted the default of adLockReadOnly, and therefore you cannot update it. DonÆt want to update it, you say? Fine, thenà donÆt set it. ;-) See how easy this is? You just have to think about all the things that you want to do with your recordset, and the proper attributes will simply present themselves.

Ok, I said I would cover both ways of setting your options, so here is the short form. Once you know what is what and who is who, coding is all about shortcuts.
The open method is followed by your source (SQL Statement), followed by your connection object, followed by your cursor type, followed by your lock type, and you can follow those with options such as adCmdStoredProc, adCmdTable, or a host of others which again, are beyond the scope of what weÆre talking about here. Cursor Location is the only one of the three that canÆt be declared ôinlineö with your other declarations. It has to go on itÆs own line. For reference sake, here is the ôofficialö syntax of the open method:

recordsetobject.open source, ActiveConnection, CursorType, LockType, Options

You can separate fields you do not wish to declare with just plain ole commas (e.g. rs.open sql, con, ,adLockOptimistic) -- and you can OR more than one option together if you need to.

And leave off anything at the end û such as how I left off the options part of that statement.

Ok, one last thing before I shut up û all the constant enum names (e.g. adLockThis and adUseThat) that were used to explain the different types of attributes here are all contained in the handy-dandy adovbs.inc include file that can easily be found by typing that file name into the text box at your favorite search engine. It MUST be included on every page where you want to use the enums rather than the contants (e.g. 1, 5, 3) unless you declare them yourself before you use them.

Well, gee, I hope I havenÆt forgotten anything. Feel free to add your own FAQ if I have to beef up whatÆs been said here. I hope this makes someoneÆs life a little easier sometime.

Happy Coding!