Ken;

If you have a need to edit the record or if it is a parent record with children (who will need the parent's key before they can be saved), your save routine will have to include a method to retrieve the key value. This is actually not very difficult. You must do tableupdates one record at a time and after each update USING THE SAME CONNECTION do a SQLExec(handle, "SELECT @@INDENTITY") and then update the current record value with this returned key value. This code can be in the AfterSave routine of a base data class and with a few properties (primarykeyIsIdentity, etc) it can be triggered automatically.

Having added the record, you have no way to requery for it unless you get the key value first.


>Ok,
>
>I'm in a quandry here - someone please help me out!
>I have an updatable cursor created from a SQL stored proc and all the appropriate SQLSetProp() stuff to make it happy. I use integer surrogate keys, and the key is set up as an identity field on the back end.
>
>On regular (existing record) updates - all works as expected. On inserts - here is the problem - and I can't find the way around it though I'm sure there must be one.
>
>When I insert into the cursor, the key "automatically" gets a value of 0 (zero) since the field is non-null. (The key field is NOT set up as updatable, of course.) When I fill in all the blanks and issue TableUpdate(), the record is correctly inserted on the back end with the correct @@identity number. Great. Now I can retrieve the identity number. Great there too. HOWEVER - here is the problem.
>
>If I now (after the initial tableupdate to do the insert) change a field and try to update again, it fails because the code generated by TableUpdate() still looks for "where keyfield = 0" I can see this in SQL Profiler. Now - even IF I REPLACE the keyfield in the cursor with the correct identity#, tableupdate STILL uses "where keyfield = 0" in its update statement!! Refresh() doesn't have any effect either.
>
>So - how the heck do I do this? Specifically - how can I "force" tableupdate to use the current value in my key field in its where statement? Please don't tell me I need to write my own "tableupdate". I thought @@identity was the answer to getting around having to have a "keys table", but I'm beginning to wonder.
>
>Any help is GREATLY appreciated.