SQLTeam.com | Weblogs | Forums

Prevent 2 users from updating the record at the same time


#1

Dear All
I am using sql server as a back end and ms access as a front end (adp)
I have 2 tables with a relation ship between them one to many
I am using an android device to enter to the system remotly
the problem is happend when I open a record from my PC and when I enter the same record from the Android device they both can update the record
is there a way to make the record be edited by one user eithe from the PC or the Android Device?
Best regards
Ashraf


#2

I think you can have a column with a random data, like a guid, so, when you read the data you save that random value on a session variable, and then when you save the data, if the random value en DB is the same session variable, it means no body has changed the values from another device, so you can save the data and you update the random field with new random value. I think that in frameworks like .Net it can be done automatically,

Another approach is to lock the row while you are updating, like a connected mode, but I prefer disconnected mode to avoid locking tables,


#3

We have an EditNo column in our tables, when the record is updated the EditNo is incremented (if the user code / SProc doesn't do it then a Trigger does [i.e. when Inserted.EditNo = Deleted.EditNo]

The APP gets the current EditNo when it gets all the other columns [to display on the form] and includes that in the POST, so the code which updates the DB just checks if that user has the same EditNo as the current row. The "second user" who attempts to update that row gets a "Sorry, someone else updated that record already" message. Also, if the first user presses BACK and tries to resubmit some data, maybe having modified the form somewhat, that also fails