How to limit other user not to use the same record?

[jp7234]

Hi, everyone.

I use VB.NET and SQL SERVER 2005 to develop a web application. There is a problem, please give me some advice. Thank you very much.

In the project, there are a lot of web forms to be displayed. For example,

(1) There is a customer infomation list in Web form A. I click one of the row of the list and go to Web form B to edit the row.

(2) When I am editing the row in Web form B and not finish editting, other user select the same row in the customer infomation list from Web form A, and then go to Web form B to edit the selected row.

If so, I and he edit the same row. When commit the editting, one of us will lose the editted datum. How can I do?

My idea: when I select a row to edit it in Web form B, if other user selects the same row in Web form A and want to edit the row in Web form B, when Web form B used by the user is loaded, a Waring Message should be given and the row should not be display in Web form B used by the user.

As to my idea, How can I do for the idea in VB.NET and SQL SERVER 2005?

Other way to solve the problem?

Thank you very much!

I am anxious to get your replies soon.

 

[crvasquez]

The way I have accomplished this is by adding an flLock (and User) field in the database. When the user clicks on edit, that flLock value is checked. If flLock = Y then a message is prompted that it is being edited by another user.

You will also need an administrator page to clear the lock in case the user did not exit the appplication properly.

So the process works like this.

User clicks on the form - Call Method to check database to see if there are any records with their initials in the User field - if so -> clear the user name and set flLock = 'N'.

User clicks on Edit -> Call method to check to see if the flLock = 'N'.
---> If flLock = 'N' then set flLock = 'Y' and insert UsersName in the User field ELSE
---> If flLock = 'Y' then prompt user -> record is being modified.

User saves data - Call Method to check database set flLock = 'N' for the record that was justed edited and remove their name from the User Field.

User exits form (properly) - Call method to check to see if the flLock = 'N'. If yes, set flLock = 'Y' and insert UsersName in the User field.

Hope this helps...

 

[Cavar]

Or you could do concurrency checking by adding a timestamp column to each row. This column would have a default value of getdate() for when new rows are added and you would update it when you save your data.

So, when you query your record for editing you'll have the last time it was updated, then when you try to save it in the database you can check to see if that column in the database has changed since you first read the record.

If the value has changed you could requery the changed data and show the user what has changed, or just display a simple dialog stating that the data was changed by another user or just disregard it and save your changes and overwrite the current data.

CT

 

[petersgyoung]

Another way is to add an extra timestamp field. When a record is retrieved for editing, the timestamp and other fields are retrieved. During updating, the retrieved timestamp is compared with the timestamp in the database. If they are the same, the record change is committed with a new timestamp.