INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- Turn Off Ad Banners
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...I have learned more through this forum than I did on a two day course. Thanks to everyone for their help and other postings that I have found useful..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft: Active Server Pages (ASP) FAQ
|
ASP 101
|
The 3 most important recordset properties
Posted: 3 Apr 01
|
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) –
dim rs
set rs = server.createobject (“ADODB.Recordset”)
rs.activeconnection = con
rs.cursortype = adOpenStatic
rs.cursorlocaton = adUseClient
rs.locktype = adLockOptimistic
rs.open “SELECT * FROM myLittleTable”
set rs.activeconnection = nothing
con.close
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.
dim sqlStatement
sqlStatement = “SELECT * FROM myLittleTable”
rs.cursorLocation = adUseClient
rs.open sqlStatement, con, adOpenDynamic ,adLockBatchOptimistic
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! 
|
Back to Microsoft: Active Server Pages (ASP) FAQ Index
Back to Microsoft: Active Server Pages (ASP) Forum
My FAQ Archive
 Email This FAQ To A Friend |
|
 |
|
