> Hi experts!
>
> At the backend using MSSQL, I have set a field to be primary key that automatically increments its value by 1. Similary with AUTOINC integer type in native foxpro table.
>
> I have one row at the frontend to send data to the backend using remote view, minus of course a data for the primary field key since SQL shall provide the value automatically once it reaches the backend.
>
> After issuing TABLEUPDATE, How do I then retrieve the automatically generated value from the primary key field at the backend so I can display it on the FORM?
>
> I remember time ago, this was one feature to be included in VFP 9.
>
> Is there any other way?
>
> Hope you can help.
>
> TIA
>
> Rene-PCSO

You cannot do this using a remote view. You must use SQL Pass-Through to query SQL Server for the value that it just issued. The basic query is:

lnRes = SQLEXEC( lnCon, "SELECT @@IDENTITY AS lastid", 'cur_lastid' )
IF lnRes > 0
  lnLastID = cur_lastid.lastid
ELSE
  *** SQL Error
ENDIF

Note that @@IDENTITY returns the last Identity value issued on the specified connection! So you must use the same connection as that used by the Remote View in order to get the correct value.

If the table you are updating, in SQL Server, uses triggers to update other tables, then you have to use a different function (IDENT_CURRENT) in order to retrieve the correct value - otherwise the 'last' ID will be for the table updated by the trigger, not the table updated by the view.

lnRes = SQLEXEC( lnCon, "SELECT IDENT_CURRENT( 'table_name' ) AS lastid", 'cur_lastid' )
IF lnRes > 0
  lnLastID = cur_lastid.lastid
ELSE
  *** SQL Error
ENDIF

Regards
Andy Kramek
Microsoft MVP (Visual FoxPro)
Tightline Computers Inc, Akron Ohio, USA