Hi
You can solve this issue usign
TimeStamp column in the table. Because, whenever you modify the record, the Timestamp column will get updated automatically.
Scenario : Create Table Tb_Sample1
(
ID Int Identity(1,1),
Name Varchar(50),
Gender Varchar(1),
RowID Timestamp
)
Go
Insert Tb_Sample1(Name,Gender) Values('Aequea','M')
Insert Tb_Sample1(Name,Gender) Values('Salino','F')
Insert Tb_Sample1(Name,Gender) Values('Calcalino','M')
Insert Tb_Sample1(Name,Gender) Values('Setaceo','F')
Insert Tb_Sample1(Name,Gender) Values('Socaprio','M')
Insert Tb_Sample1(Name,Gender) Values('Alumino','F')
Insert Tb_Sample1(Name,Gender) Values('Vitriolic','F')
Go
Actual Records from the table:
ID Name Gender RowID
1 Aequea M 0x000000000005BCC1
2 Salino F 0x000000000005BCC2
3 Calcalino M 0x000000000005BCC3
4 Setaceo F 0x000000000005BCC4
5 Socaprio M 0x000000000005BCC5
6 Alumino F 0x000000000005BCC6
7 Vitriolic F 0x000000000005BCC7
Step: 1
User1:
User1 popolates 7 records , Each record will have Timestamp value generated on RowID column.
User2:
User2 also popolates 7 records , Each record will have same Timestamp value generated on RowID column.
Step: 2
User1:
User1 Modifies some record.
Update Tb_Sample1 Set Gender = 'M' Where Name='Vitriolic'
User1 popolates 7 records again (Refresh the page), Each record will have Timestamp value generated on RowID column and The modified row will have some different Timestamp value as given below.
Record modified :
ID Name Gender RowID
1 Aequea M 0x000000000005BCC1
2 Salino F 0x000000000005BCC2
3 Calcalino M 0x000000000005BCC3
4 Setaceo F 0x000000000005BCC4
5 Socaprio M 0x000000000005BCC5
6 Alumino F 0x000000000005BCC6
7 Vitriolic M 0x000000000005BCC8
User2:
User2 Tries to modify the same row (Name='Vitriolic'), We should not modify the row/data directly. You have to do the following validations.
1. You have to compare the RowID(Timestamp) of each row, Which row you going to modified with the Actual RowID from the Table.
So, Your RowID which you had on your page cache is
0x000000000005BCC7 , But the actual RowID from the Table will be
0x000000000005BCC8 .
Because whenever you modify a row the respective RowID(Timestamp) will also be modified automatically.
2. So, Your current RowID which you already got in your page will differ from the currently you got the RowID from table.
3. If the Both RowID is different then, You should not allow "User2" to modify the data, You have to raise some warning like "The same data modified by some other resource, So you have to Refresh the page and Try".
4. Once the "User2" page refreshed, then the modified information will be fetched.
5. Now the "User2" can try to modify the data.
Conclusion :
Whenever you try to modify the existing Data on multi-user environment application, Validating the Timestamp data of each row (Which row you trying to modify) is better solution.
If you have anything better solution appart from this, Please reply and share with others.
Cheers
Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions
Nav234, if this helps please login to Mark As Answer. | Alert Moderator