You need to wrap your code in a SQL transaction and add to your query a SQL table hint. The transaction will exclude others from querying the data. Here is what I recommend adding to your VFP code:

* Turn on the transaction
SQLExec(,'BEGIN TRANSACTION')

* Execute your SQL query but add a WITH clause like:
Select * from MyCounter With (TabLockX,HOLDLOCK)

* Perform your update
UPDATE....

* If everything went as expected Commit the transaction
SQLExec((,'COMMIT')

* else rollback the transaction
SQLExec((,'ROLLBACK')

Other users will be locked out of querying or updating to the MyCounter table until you Commit or Rollback your transaction so you want to minimize the time the table is locked.

>Hi,
>
>Problem Scenario - I have a SQL database which can be accessed by a user as well as by a FoxPro program. I pull the SQL record down to foxpro(create SQL view command used), perform certain calculations, issue an SQLEXEC('update statement'). In the meantime the a user accesses the same SQL record and tries to update it. Problem Scenario explaination with an example -
> SQL table - MyCounter, Field - NextCounter
> Foxpro program runs - pulls down the following details : NextCounter=100
> Foxpro program performing calculations
> Before the foxpro program issues the update statement, a user access the
> same table and same record => NextCounter=100; updates NextCounter=101
> The user should not be able to update the record if the foxpro program
> has pulled down the record for processing.
>
>Question - How can I implement record record locking of the SQL record?
>
>Neetu Kumar

-----------------------------------------

Cathi Gero, CPA
Prenia Software & Consulting Services
Microsoft C# / .NET MVP
Mere Mortals for .NET MVP
cgero@prenia.com
www.prenia.com
Weblog: blogs.prenia.com/cathi