>Hi Guys,
>I am converting a VFP app to SQL Server Database. This app has stock control and about 50 users use the system simultaneously. A lot of inventory management. I need to use pessimistic locking, So the stock on hand gets incremented or decremented depending on the type of transaction. In VFP I issued a Rlock() on the record, updated and then released it, how do I achieve this in SQL. Any help will be appreceiated.
>
>TIA


Fred,

The following works fine for me:

BEGIN TRANSACTION

UPDATE
MyTableName WITH (ROWLOCK) --this will lock all records affected
SET
MyColumn = MyColumn + MyNewValue
WHERE
PK = MyCondition

--read the UPDATED Content, at this point all other users have to wait
SELECT MyColumn FROM MyTableName WHERE PK = MyCondition


--release the lock
COMMIT TRANSACTION


--Aditionally, you can take a look at "SET TRANSACTION ISOLATION LEVEL" setting on the server
--Hope this helps