>>>>I have an RV on a SQL Server table with an IDENTITY (int) field. After an add, I am trying to get the IDENTITY fields value to stuff into the RV for subsequent possible edits. I do this fine but the next tableupdate(1) always returns an Update Conflict. Is what I am trying to do possible? If so, any suggestions on what I'm doing wrong? Example of code below:
>>>>
>>>>append blank
>>>>
>>>>(... populate all the fields, except sqlupdtuid (IDENTITY field) which is the key)
>>>>
>>>>=tableupdate(1) && This is successful
>>>>
>>>>lnHandle = CURSORGETPROP("ConnectHandle", "CDMTESTTABLE")
>>>>=SQLEXEC(lnHandle, "SELECT @@IDENTITY AS LastPK", "LastPK")
>>>>select cdmtesttable
>>>>replace sqlupdtuid with lastpk.lastpk
>>>>
>>>>=tableupdate(1) && This fails with Update Conflict
>>>
>>>Do you have row or table buffering on your RV? Your idea cannot work for table buffering.
>>>Btw, you may always Requery() your view to get most recent data, including identity field. Also, you better use @@SCOPE_IDENTITY instead of @@IDENTITY on SQL-Server side.
>>
>>Thanks for the reply Ed. I tried optimistic record buffering on the RV and it didn't work either. Same Update Conflict error. As for REQUERY(), I'd prefer to avoid bringing the entire table down each time an add is done.
>>
>>Any other thoughts?
>
>I don't think you can go without Requery(). Just think what you pick up by running Select @@identity... How do you ensure that this is correct PK? It makes sense to return @@SCOPE_IDENTITY from the same SP that creates the record, but in case of remote view, I don't it will work at all. Most likely, CursorAdapter has this mechanism.
>Another way is to get PK through querying table by 'candidate' key if your table has one.

I found similar code to what I am attempting on the FoxPro Wiki by Carl Karsten. I tried this as well and the second tableupdate(0) failed. The Wiki doesn't say if an edit was attempted after the add though. I wonder if this is a VFP6 issue as opposed to an RV issue?
http://fox.wikis.com/wc.dll?Wiki~_MsdeVfp102_2

IF llAdding
* Update the current value of pkAddr with the newly assigned value
lhX = CURSORGETPROP("ConnectHandle")
lnRet = sqlexec( lhX, "select @@IDENTITY as NewID" )
replace pkAddr WITH sqlresult.NewID IN v_addr
thisform.refresh()
endif