>Hi, again.
>
>Going on with my CursorAdapter samples over the Northwind database, I got stuck with another gotcha that I think it should be fairly common, but I couldn't find a decent solution yet.
>
>Nortwinth has the tables Orders and OrderDetails that are parent/child related by Orders.OrderID.
>
>OrderID is an AutoInc Integer (or Identity). Now, in my data-tier, I need to insert a new order, so I do it over CursorAdapter tables, but when the time comes to update, I need to:
>
>1. Update Orders (this would insert the new parent record)
>
>2. Replace all OrderDetails.OrderID with Orders.OrderID
>
>3. Update OrderDetails
>
>The problem is that I don't know Orders.OrderID for the record I just inserted. It is not refreshed after the TableUpdate on the local cursor, and in this case, I have not any possible candidate key to try to retrieve it.
>
>Any ideas are appreciated,

Hi Martin,

CursorAdapter.AfterInsert event is the place to refresh AUTOINC values during TABLEUPDATE.

For ODBC or ADODB.Command based Insert, execute SELECT @@IDENTITY using the same connection and simply replace value for the local field. This method might not work as expected if Insert command inserts into more than one IDENTITY field.

For ADODB.Recordset based Insert, move to the last record in the Recordset, get value from its field and replace the local field. This method (moving to the last record) works for adOpenStatic recordset, I am not sure how it'll work for different recordset type.

Thanks,
Aleksey.