>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.)
>

I'm still struggling with this one. I've now done enough testing to agree that the compound PK is not the problem. I've got test code where I set all the properties of the cursor and I can update it. But in the context of the app, the update still fails. I've confirmed that, other than some differences in capitalization, the properties I listed above are set exactly the same for my test cursor and the one created in code.

I thought I had it earlier today when I discovered that, in the case that fails, the PK fields were accepting nulls, while the successful case did not. But I fixed the code to ensure No nulls permitted for the PK fields, and the update still fails. Looking for any additional clues anyone can offer.

Tamar