Hi, all.

I've been trying for weeks to come up with a row-record locking scheme for a VFP app that uses SQL Server 2000.

As a simple example, we have a customer maintenance form for CUSTMAST. If one user goes into a record and starts to EDIT, we want to be able to lock the record in SQL Server, so that if a 2nd user comes along 10 seconds later and also tries to edit, the 'RLOCK' attempt would fail (even though there's no RLOCK in T-SQL).

I posed the solution that we could have a LOCKSTATUS column in CUSTMAST, that a value of 1 would be locked and 0 would be unlocked.

However, another developer suggested the following...

1- Use SQLSETPROP to set TRANSACTIONS to 2, and ASYNCHRONOUS to TRUE.

2- Attempt a simple update on CUSTMAST, where you set any field to the current value...for instance: nResult = SQLEXEC(nMyConn,'UPDATE CUSTMAST SET ADDRESS = ADDRESS')

3) If the 'mock update' succeeds, you know you have a LOCK. If nResult returns a 0 or -1, then you know it failed.

4) if it succeeded, go about your business, let the user edit, and then do your real UPDATE, and then finish with either an SQLCOMMIT or CANCEL/ROLLBACK to unlock the record.

Admittely, this first sounded a bit kludgy, but I can't think of any specific reason to discount it in a production environment.

Comments/thoughts?
Thanks,
Kevin