asked on

VB6 Record locking examples in VB6

Can anyone provide a few concrete examples in VB6 of record locking in ADO, with error detection and retry. (I read some MSDN and experts-exchange articles, but couldn't find what I need)

1. Example of optimistic lock, and what to do if I want to retry:
Sub Opt1()
Open recordset Rs1
' change it:
Rs1! Name = "Jane"
Rs1!Type = 14
Rs1.Update
if (record could not be updated because someone else updated it before we got to it)
How Can I try the update again?
How can I get the new "current value of Rs1" (that another user updated; the Resync?), do my "change it" again, and try again?
End If

2. Example with pessimistic lock
Sub Opt2()
Rs1.Open ' is it at this point the lock is obtained on the record?
Rs1!Name = "Jane" ' or is it at this point that the lock is obtained?, the first time you try to change it
' is there an explicit way of saying "I would like to lock this record now"
Rs1!Type = 14
Rs1.Update ' do I need to trap for any errors here?

3. Any other examples or suggestions, especially with parameters to look for and error trapping

Thanks

ASKER CERTIFIED SOLUTION

prosh0t

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amp834

ASKER

Hi prosh0t, and thanks for your reply.

1. Does pessmistic locking mean that at RsRec.Open time, the record is locked, or that it is locked when I try to do an edit to RsRec?

2. Can the error checking be more specific, i.e. not any error, but if it's a lockout error?

And, just in case,
3. Are there any settings on "cn" (ADODB.Connection) I should be aware of for the locking to work?

SOLUTION

prosh0t

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Just a word of caution depending on your provider, you may not even have record locking, but rather page locking. That mean that when you use adLockPessimistic you could in fact be locking more than one row.

This is the reason, most developers do not use any type of locking, but rather depend on other mechanisms.

amp834

ASKER

prosh0t, one more clarification

So if I have just one record in the recordset, I will continue to have the lock until I close the recordset. And if I have multiple records, then moving the cursor to another record will release the lock on the record I just moved away from.

Is this correct?

Anthony Perkins

>>Is this correct?<<
Not necessarily. See my previous comment.

prosh0t

acperkins is right... what kind of provider and database are you using? For example, if you're using Jet 4.0 with Access (very common combo), record-locking (rather than page) is on by default (see http://msdn2.microsoft.com/en-us/library/aa189633(office.10).aspx) and everything will work as you described unless you change it. But for other db's and providers I'm not sure.
1

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amp834

ASKER

Thank you both for your help. I also found the Resync doesn't always work (there are some strange cache problems as well), though Requery or (Rs.Close and Rs.Open) will work ok if necessary, when I find that someone else has updated before I got a chance to do so.