Locking in ADO

What backend you are using?

With optimistic locking you only know that another user has a lock on the record when you issue the .Update call. The only thing you can do is trap any error that occurs on this call and flag that there is a lock, retrying the update as appropriate. If you were to change to pessimistic locking, you would get an error on the first attempt to change a field value in the record and would have to go through the same warning/retry logic.

Alternatively you could add a field or fields to the table in which you could place the user id and a datetime indicating that the record is locked by a specific user; check that this field is empty before modifying the record and update this field to place a "lock" before you do the actual update. This works well in some scenarios however you would have to add additional maintenance options which allow you to clear locks that have been placed and not released correctly.

Adding to TimCottee's comments, In your case, you might need pessimistic locking, since you wish to restrict opening the forms, if I am right.

To do Locking, it depends on the backend, that you use.

For a serious application you should try to rely on the Database engine to handle your locks. Implementing your own is like re-inventing the wheel.
My suggestion is to use optimistic locking. If upon editing someone else has that record locked you will get a message like"Couldn't update, Currently locked".
You should trap this error and display a message like above
and get the user to retry. Don't loop on this because you may drain resources but instead get the user to press a button.
Also be careful what cursor you use - dynamic is very resourseful.

Thanks for all these reponse.
I would like to provide more details about my requirements.
I am using Vb6 and sql server 7

My problem is , I want to get a hold on the application when two or more user are opening the same form and are on same record.

I do get error but its not specific. it says
"Method of object failed " 
Is there any way that i can find out that the error is because of locking.

ADO does the locking internally.. but is there any way i can know that the record is locked. SO that i can display a proper message to other user accessing the same record.

I know that that dynamic recordset are expensive but according to my requirements, i need to use it because i don't want to use refresh incase of any addition or deletion.

I would also like to know .. which is the best way to design a master form.
1. Should i use Databound class, Databound recordset.
2. Should i do all the assigning manually.
 Eg. on every move of the recordset. i will say
txt_empno=myrec("empno")

You should use the connection's errors collection:

For intError = 0 To UBound(cnn.Errors)
  Msgbox cnn.Errors(intError).Description
Next

To retrieve more informative descriptions of the errors encountered (if any).

As for the other part, it is generally considered better to use unbound controls, in fact many experts consider using bound control an abomination. The bound controls generally add extra overhead and reduce the flexibility that you have when using code to control things. Bound controls are great when you first start as you think you can get something going really quickly. Soon after that you find that they are really a nightmare and go back and strip it all out and replace it with code. Much better to go with the code from the start.

This is what i was thinking .. that unbound data form is much flexible than the bound ones... and now after all this comments i am confident.
Thanks guys..

But i m still searching for my first quesiton, regarding the locking. Though ADO does it internally, how can i find it out.

Thanks.

I will make my problem specific...
1.I am using ado reference 2.6
2.Using dynamic recordset.
3.Optimistic locking.
4.Unbound Method.

When two users are on the same form. Using the same record. The first one deletes the record and second one goes to update it. It gives error "Method of object failed"

I want to handle this situation.

Can anyone provide me a solution.
Thanks.

You are getting this message because ADO can longer perform the update method. You trap this error and display
"Record had been Deleted " is that is the case..

Even when one user has updated, and another user tries to update, you could trap the error and display, something like "Record Already Changed By Another User."

I will make my problem specific...
1.I am using ado reference 2.6
2.Using dynamic recordset.
3.Optimistic locking.
4.Unbound Method.

When two users are on the same form. Using the same record. The first one deletes the record and second one goes to update it. It gives error "Method of object failed"

I want to handle this situation.

Can anyone provide me a solution.
Thanks.

I know i have to trap the error but how??
I mean the error it gives is totally different and meaningless.

Try using the following code, to get the number for your errors, and then you could then test for the number and display proper error message.
To find the error number:

    On Error GoTo errhandle:

    cn.Execute yoursqlstatement
    Exit Sub

errhandle:
    Dim err1 As ADODB.Error
    For Each err1 In cn.Errors
        MsgBox err1.Number & " - " & merr.Description
    Next

To trap the number:(you can do something like this:).

errhandle:
    Dim err1 As ADODB.Error
    For Each err1 In cn.Errors
        If err1.Number = -2147217864 Then
           Msgbox "Record Already Changed By Another User."
        Else
           MsgBox err1.Number & " - " & merr.Description
        End If
    Next

You could either give an option to re-try or re-update the values or cancel the update, this is optional.

For a complete ADO Error handling you could use the code as in this link:
http://support.blenks.com/kb/visual_basic/ADO/INFO%20Extracting%20Error%20Information%20from%20ADO%20in%20VB.htm

The only way to do what you are looking for is to have a server side cursor with pessimistic locking.  I would use unbound controls that way you can control via code how long a person can keep a lock on a record.  Implement a timer of some sort that would start when the person edits the first peice of information and stop when you call update.  If the timer fires then call cancelupdate and notify the user that they have lost their lock on the data.  I suggest this because with pessimistic locking, it is possible that a user could start editing data and then go out to lunch before committing the changes.  If this happens, no one will be able to use the locked record until the moron gets back from lunch.

BTW:  ADO client side optimistic cursors never really lock a record.  When you cal the update method, ADO builds a standard SQL UPDATE statement using the modified fields and the primary key.  There is an obvious problem if you don't select the primary key in your query.  There is another problem also.  If user 1 and user 2 both run their queries simultaneously and then user 1 modifies field 1 and next user 2 modifies field 2.  If neither field 1 or 2 are part of the primary key, both updates will succeed even though the data has been changed.  The following statement will fix the later of the above 2 problems:

      rs.Properties("Update Criteria") = adCriteriaAllCols

Thanks all of u.
i will work out with either of the lock (optimistic or pessimistic) and trap the error .

Hi Hakims,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Split points between: valli_an and TimCottee

Hakims, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer

Per recommendation, force-accepted.

Netminder
CS Moderator

TimCottee: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20328621