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.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only