>Hi,
>
>I am using VFP6.0 front end to the above SQL. I need to generate a unique character based number for one of the files. The unique key will end up being something like abc-0000000001, so can't use the SQL self generating unique id.
>
>My problem is that in VFP tables, I lock the record in the datafile that contains the counter, save the field to a memvar then increment the counter field by 1 and unlock the record, but how do I do the same thing in an SQL data file.
>
>I want to lock the SQL record, retrieve its current value, increment the field by 1 and then unlock the record.
>
>
>Regards
>Doug Johnston

Hi Doug,

I also use vfp+anywhere... after a period of trial and errors i discovered that Sybase anywhere is an incredible and splendid tool. I have being using both for two years now. Quite an effective and complementary tandem of tools !

I use vfp only for editing, browsing and reporting data. We tap the powerful anywhere server querying data.

However updating information in remote data source is difficult.
Inserting new records is of course tricky. Your data is away from fox.

There are a couple of way to handle the process. The way i do it :

1) get a connection handle to your data source

2) launch a command to fetch the a new id number.
Example - for an 'ABC-' prefixed code

SQLEXEC(oHandle,[SELECT CAST(SUBSTR(myCode,4,99)+1 AS INTEGER) FROM myRemoteTable WHERE myCode LIKE 'ABC-%'],[MyCursor])

3) build my new string id - this is a classical vfp string manipulation.
i have no vfp at hand i understand you can transform this integer into a proper
code. myString should look like 'ABC-nnnnnnnnnnn'

4) try to insert the record in the remote data source

SQLEXEC(oHandle,[INSERT INTO myRemoteTable (myCode) VALUES (]+;
myString+[)] )

and possibly loop on the process if the last command fails (the code may have been allocated by a concurrent user)

Hope it helps. Do no hesitate to come back to me with private mail in case you wish more specific information

François