General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
TableUpdate() uses Insert, not Update, with compound key?
I’ve run into a weird SQL problem and I’m hoping somebody knows the answer. In cleaning up a client’s application, I’ve written some generic code to make cursors returned from SQL Server updateable. Specifically, I’m setting the following properties of the cursor: Buffering, KeyFieldList, Tables, UpdatableFieldList, UpdateNameList, SendUpdates, WhereType.
My code seems to work perfectly when the SQL table has a single PK field. But on tables that have a compound primary key, when I issue TableUpdate() on the cursor, I get a “Violation of PRIMARY KEY constraint” error from SQL. The Profiler tells me that it’s attempting to send an Insert rather than an Update, which seems to me to be the actual problem. For a table with a single PK field, TableUpdate() generates an Update command.
I’ve checked and KeyFieldList includes both PK fields and there is, in fact, exactly one record on the back-end that matches the one in my cursor.
So has anybody run into this before? Have I found an undocumented VFP bug (after all these years)? The only work-around I can think of is to detect the situation and generate the UPDATE command myself. The problem is knowing whether I need UPDATE or INSERT, which is the reason I wanted to use an updateable cursor. (I know how to write the work-around; I just don't want to.)
Tamar
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