Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bacchus
Starting Member
2 Posts |
 Posted - 2010-11-24 : 12:13:41
|
| Ok I know there is a right and wrong way to do this so let me explain what i'm trying to do. I have a table of data that multiple desktop app(C# 3.5) users need to go thru and check the fields for errors. Each user will have a next button on the form and can edit and update the records. Once the record is corrected I don't want it to be displayed again. Obviously if user1 is on record1 i need it locked to prevent it being displayed to user 2. It will be a linear looping of the data to get all records corrected by multiple people. It would be nice for the user to able to go back to only the records he/she corrected. Whats the best way to do this? Hope this makes sense. Thanks. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-24 : 12:26:20
|
| MaybeHave a column LockedForUpdateThe user displays the row.If they want to update they click a button which sets the LockedForUpdate column - refuses if it's already set, then redisplays the entry (in case it has changed). When complete the column is cleared - could set another UpdatedBy column to show who did it or maybe you want an audit rail table.An issue would be if the user locks an entry then doesn't complete.You could hold the LockStartTime and remove it (in the check for lock and update) afte a time.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
bacchus
Starting Member
2 Posts |
Posted - 2010-11-24 : 12:31:47
|
quote:
Originally posted by nigelrivett
MaybeHave a column LockedForUpdateThe user displays the row.If they want to update they click a button which sets the LockedForUpdate column - refuses if it's already set, then redisplays the entry (in case it has changed). When complete the column is cleared - could set another UpdatedBy column to show who did it or maybe you want an audit rail table.An issue would be if the user locks an entry then doesn't complete.You could hold the LockStartTime and remove it (in the check for lock and update) afte a time.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
All good ideas. Keep them coming. I'm just out of the know on SQL Server features and wonder if there is a way to handle this without additional tables and fields. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-24 : 12:49:19
|
| You could issue a read from the client with a lock which would prevent other users getting the same lock.You woud release it when performing the update.You would need to issue the read in a transaction and with holdlock and hold the transaction open until the user updates.It would mean that your client would need direct access to the database (no asynchronous stuff) and would prevent entries in the tr log being released.I wouldn't do this.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Multiple users editing data from same table