February 27, 2021
Hot Topics:

Book Review: Beginning Visual Basic 6 Databases

  • By John Percival
  • Send Email »
  • More Articles »

So cursors are the beasts that let you move from row to row and maintain the contents of the grid as the user scrolls through the rows. Cursors come in many sizes and colors. Take a look at the Data control property above. The DefaultCursor is always selected - this is the most powerful cursor. It permits the user to scroll forward and backwards in the recordset as well as update and delete records. This is the most expensive (in terms of memory and speed) type of cursor. After all, the cursor has to be prepared for any eventuality. Consequently, this default cursor can actually slow down your application. Why? Because it has to keep track of all of the things we just talked about. This takes time and memory.

With ADO, the understanding of cursors is extremely important. In fact, it is critical in order to get our recordset to do the things we want. We need to open certain ADO recordsets with specific types of cursors - in order to get the .RecordCount property, for example. Well, every time a new Recordset object is created, a cursor is automatically opened. But before we open a recordset, we can specify the type and location of cursor to use when retrieving our results. The .CursorType property allows us to set a cursor as forward-only, static, keyset-driven, or dynamic. Each type of cursor has its pros and cons. In the next chapter we will cover each of these and why they are used.

This is very important because the type of cursor we use determines whether a Recordset object can be scrolled forward/backward or updated. The type of cursor also affects who can see changed records. Keep in mind that the most efficient cursor is used by default. This is read forward only. If you only need to read the data, one time, and only move forward, this cursor is for you and there is no need to change the default. But if you need something a bit more robust, you need to bypass the default. Let's summarize the cursor options.

Types of Cursors

Cursors can be either updateable or non-updateable. If you only need to display information and not diddle with it, the non-updateable is the fastest. The provider simply passes you the data and forgets about it! There is no need to keep tabs on the data to see if it has been changed. Therefore, this is the fastest cursor to use.

Scrollable cursors, which can be updateable or non-updateable, permit you to move (scroll) back and forth in the recordset. If you only need to blast out some data to a grid or HTML page, a non-scrollable cursor will give a performance boost. This is because there is no need to track where you are in the recordset.

Keyset-Driven cursors take note of a key you may have in your database. Under normal circumstances, when you request records from a data source, you get the entire package. However, when a Keyset cursor is selected, only the keys are returned, giving a performance boost. Then, when you scroll through the recordset, the data for the keys is retrieved. However, when you first invoke the cursor, both keys and data are returned so you can see something in your bound control. When you scroll, and the data is not in the current batch of data, the Keyset fetches the next block. This way it only has to manage small keys rather than large chunks of data.

Dynamic and Static cursors determine what data is available in the cursor at any point in time. As the name implies, Static cursors only contain data that was placed in the cursor when it was created. However, with a Dynamic cursor, any new records that are added to the recordset are available. It's like a living cursor.

We can use our old friend the Object Browser to see the cursor types available to us:

With ADO, we have the choice of four types of cursors. We can simply use constants to tell the recordset which to use:

Cursor type Description Constant
Static cursor This is the one to use for generating reports or finding data. Additions, changes, or deletions by other users are not visible adOpenStatic
Forward-only cursor This is the default. It is identical to the Static except that you can only scroll forward. The fastest cursor this side of the Pecos Mountains adOpenForwardOnly
Dynamic cursor Additions and deletions by others are visible. All movement is supported. But some providers don't support this cursor type adOpenDynamic
Keyset-driven cursor This is similar to a Dynamic cursor except you can't see records others add. If another user deletes a record, it is inaccessible from your recordset adOpenKeyset

We can also tell the recordset how to lock our data while it's being manipulated via the cursor:

Lock Type Description
AdLockReadOnly Default. Read-only: you cannot alter the data.
AdLockPessimistic Pessimistic locking, record by record-the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.
AdLockOptimistic Optimistic locking, record by record-the provider uses optimistic locking, locking records only when you call the Update method.
AdLockBatchOptimistic Optimistic batch updates-required for batch update mode as opposed to immediate update mode

Page 20 of 23

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date