I Want to Lock One Record

Please help! I have a VB application used by multiple users. MS SQL 2000 backend.

When a user opens a record for editing, it want to mark the record as locked so that if other users try to open this record, it will not be available.

Currently, I'm using a programmatic method that basically sets a field 'lock" to 1 if the record is in use and 0 if not in use. But this has the side affact of locking the record if the user reboots their workstation. In addition, I don't believe that this is the best method and approach to achieve my goal.

Thanks very much for your help in advance. All ideas are welcome.

crownlogI think u can make use of SQL Server's locking mechanism.
example:
U can put a special feild as a semphemore.
when a user opens a record,the program begin a transaction and update the field to 1 immdiately.When the user press save button in front-program,the transaction will commit.When the locker workstation reboots,the transaction will auto-released right now,and the other waiting program will get the control immdiately.|||enhydraboy,

Thanks very much. Might you have more details or an example in using MSSQL2K locking mech.

Regards
crownlog|||Nobody?

This is a bad design. It's the path that leads to...the dark side..

You should desgin the table so that it has an update datetime column.

Every time a row is modified, update that column.

If you need to know if the row was modified, check that column again and compare the datetimes...if they are the same, good to go...if they're different, show the user the changes...you could even build something to allow them to merge the 2...then apply the update...

The my own opinion (MOO)|||crownlog ... listen to Brett's advice. What if your user, after locking the row, goes to lunch or home for the weekend or Aruba for the week or Australia after embezzling $1M? best to not lock the row until ready to update!|||should read...

"best to let sql server manage ALL of your locking"|||Actually, the issue is that I need to open a record and keep it opened the entire time it is being edited by user1 - could be minutes. I need to keep all the other users from trying to open it while it is being edited by User1. I only need that record locked during the edit.

What's the best way to do it?|||Why is a bad design?
Is A timestamp field good?
What I can confirm is that is not what the crownlog requests.|||Actually, the issue is that I need to open a record and keep it opened the entire time it is being edited by user1 - could be minutes. I need to keep all the other users from trying to open it while it is being edited by User1. I only need that record locked during the edit.

What's the best way to do it?

Let me tell my solution in detail,but u must suffer my bad english first.
Everybody knows that RDBMS uses locking to keep consistency of updating and reading during cocurrent sessions.
So when we program in VB/VC/Delphi,we needn't care anything because sqlserver engine will do it better.

crownlog's case is that he want find a solution that will synchronize transaction between different clients(according to per application).
So we must establish a synchronization rules,we need a center-locking control.
There are many solution u can choose:
1 Unique Application server,u must program by yourself
2 Let SQL Server became a center-locking control server,because database locking control is very perfect.What u need do is understanding it and making use of it.

About "best to let sql server manage ALL of your locking",I haven't read the book.But I know if I unstand sql server well,I am not fraid to using it and I well let sql server serve for me saftly.|||crownlog ... listen to Brett's advice. What if your user, after locking the row, goes to lunch or home for the weekend or Aruba for the week or Australia after embezzling $1M? best to not lock the row until ready to update!
It's the problem crownlog must think it over.
How to realize his requests in UI function.
Using locking or using timestamp,U will not avoid the problem that when one body go home for sleeping without quit his appliction,the others must wait he come back to finish his work.
Maybe u must need administrator to force one to go out.So It's not the problem of techniques.|||Maybe a combination of the two. A binary field to determine whether the record is being currently used, and a time limit of say 10 minutes, so another field with a timestamp. Assuming all the systems are using a level 2 time clock server. You could have a script running in the background to clear out any locked fields and set it to 0 if the time has lapsed comparative to the timestamp. Also setting a timer on the page of your application, and maybe a timestamp refresh button. It would force the user to pay attention to what they are doing. If I'm not getting my idea across, just drop me an e-mail, and I'll try to get some sort of flowchart together to explain it better.