Record locking

[abenitez77]

I am using SQL server as my back end and I have multiple users that will be using this app. It is possible that 1 user will query say 20 records and another user could query the same records. I want to lock the records until the first user releases them. How do I do this? and how do I notify the second user that the records are locked by another user? I don't want the second user to be able to even view the records until the first user releases the records.

 

[Andrzejek]

I am sure others will give you some other ideas, but what I do is this:

In a main table I have a field called LOCKED_BY_USER
If I want to lock the record, I put user’s login name into it.

The logic is simple – when I hit the record, I check this field. If it is empty, I put my name in it and the record is mine. If it is NOT empty, somebody else’s name is in, the record is locked by somebody, and I show to the user “This record is locked by ABCD” (Update command button is disabled). When I move from record to record, I ‘unlock’ the record (make the field empty) and repeat the logic again. I just need to remember to unlock the record when I leave the application.

In my app, all users are out of the app at night, so there is a program that unlocks all records in case somebody crashed and did not have a chance to unlock it.

But you have another requirement – you don’t want to even display record(s) that are locked. You can always go for the records that have LOCKED_BY_USER field empty, but how are you going to tell the users which records are locked / not displayed?


Have fun.

---- Andy

 

[abenitez77]

i did think of what you are doing....what i didn't think of was disabling my save button. That makes a big difference. With your solution, I think i can live with them viewing the records...without the ability to save any changes.

Thanks!

 

[Andrzejek]

You are welcome..

Glad to help


Have fun.

---- Andy