I have the need to update a field "InStockQty" with an accumulated value of "InStockQty + 100" or some other value. In VFP I used "RLOCK" to lock the record and make sure my update is done before allowing somebody else to update this field, now how can I do the same with SF/.NET/MS-SQL?

Edhy Rijo

OK, this can get to be a complex topic.  However, there are in fact lots of examples out there on how to do this.  As you have probably already determined, doing this on a BO is not going to do any good as the data is disconnected.  You are going to have to do this as the SQL level.

There are a lot of samples out there.  Here are a few:

http://www.wwwcoder.com/tabid/68/type/art/parentid/191/site/6508/default.aspx

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Another point here.  Depending on what you are trying to do, concurrency may be an option as well as record locking can very easily tie into concurrency.  For example, if you are using row versioning, it would be very easy to determine if someone else has touched that record when you save.  If so, then you can take it into account. 

Another option would be transactions.  Going through transactions can also play into the process here as when you start a transaction you can prevent anyone else from touching the records until the transaction is complete.  This is actually the approach that we take in our medical software when this could be a potential issue.

Thanks Trent,

I think I will go with the Transaction since I want to try to keep all logic at the BO level since part of this application will escalate to be a Web Application.

Edhy Rijo

This is really the best way. 

Hi Edhy,



Not sure what the logic is here but don't forget the good old SQL trigger for keeping data values in sync.



Cheers, Peter

Hi Peter,

Thanks for the advice, I really have not experience with SQL Triggers, but will sure check it out.

Edhy Rijo

Hi Edhy.

I realise this is a fairly old thread , but I have a similar issue now and want to 'Lock' a record for similar Stock type update.

Just wondering if you would care to share what you did in the end here ?

Hi Gerard,
In this particular case I decided to use a BO.CustomFieldProperty (CFP) to show this value and calculate it on the fly with a stored procedure (SP).

Since through a Business Binding Source (BBS) you can read a BO in a report, it works just fine as long as the SP is optimized.  For other cases I simply use a BO transaction to save the data, not sure it will work as the VFP RLOCK.

Make sure you check out article links posted by Trent here since they are very interesting.

Edhy Rijo