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.
Ken B. Matson
GCom2 Solutions