>Hi,
>
>I've a sqlserver (isnt that great ?) with a table. In this table there is one field that is an identity field and is set to auto-numbering. This table is viewed via a grid in a visual foxpro form.
>
>The user can create/delete/modify the data via this grid. Buffering is set to optimistic row buffering.
>
>The keyfield that is set to auto-numbering in the sqltable is set to readonly in the grid.
>
>The problem is that when a user has created a record that the keyfield stays empty. So we have to do a requery to update this field in the view.
>
>That is not so good because afther te requery the recordpointer isnt at the right record (the one last created).
>
>So here is my question : is it possible to get the right value for that keyfield in the grid without doing a requery and losing the focus on the good record ?
>
>Thnx
Kurt,
Since that's a surrogate key users never need to see that value. With an autonumbering system it's assigned by the server. If you're using a row buffering and requery the view each time your view is tableupdated (either implicitly due to row buffering or explicitly) you might get the last created identity with querying @@IDENTITY :
Requery('v_myTable')
Local m.lnHandle, m.myIdentity
m.lnHandle = CursorGetProp("ConnectHandle",'V_myTable') && Get conn. handle
SQLExec(m.lnHandle,'select @@IDENTITY as myIdentity','LastIdent') && Query last identity
Select v_myTable
myIdentity = LastIdent.myIdentity
However IMHO if you use keys that you create as unique you could simply send them back to server (mark as updatable). That way you'd know the key as soon as you insert a record.
Such a unique key is typically a GUID. A GUID is guaranteed to be unique worlwide provided a box is networked. In SQL server a GUID is known as a uniqueidentifier datatype and 16 bytes in length (128 bits value). You could get that value from SQL server with NewID() function or using winAPI :
Local pGUID,rGUID
Declare Integer UuidCreate In 'RPCRT4.dll' String @pguid
Declare Integer CoCreateGuid In 'Ole32.dll' ;
string @pguid
Declare Integer StringFromGUID2 In 'Ole32.dll' ;
string rguid, String @lpsz, Integer cchMax
Declare RtlMoveMemory In WIN32API ;
INTEGER @DestNumeric, ;
STRING @pVoidSource, ;
INTEGER nLength
pGUID=Replicate(Chr(0),16)
rGUID=Replicate(Chr(0),80)
Return Iif(Inlist(UuidCreate(@pGUID),0,1824),;
Iif(StringFromGUID2(pGUID,@rGUID,40) # 0, ;
Strconv(Left(rGUID,76),6), ""),"")
Cetin
