Please Help Linq Problem - Update

Question

Hi,
Can you tell me why im getting this insert error and how i would resolve it please
Violation of PRIMARY KEY constraint 'PK_CustomFieldsData'. Cannot insert duplicate key in object 'dbo.CustomFieldsData'.
The statement has been terminated.
EbillzNotesDataContext var where (p.CustomerCode == cuscode) && (p.FieldID == Convert .ToInt32(27)) select p).FirstOrDefault();
t.FieldValue = "PAYMENT PLAN"  t.LinkID = " Customer" EB.SubmitChanges(); I'm only trying to update the table, not insert a new record???? Thanks for your help Anthony t = ( from p in EB.CustomFieldsDatas EB = new EbillzNotesDataContext ();

Vulpes · Accepted Answer

Oh, one other thought.
Could you have made some other changes that haven't yet been submitted to the database and the exception is then being thrown in relation to one of the other changes rather than the present one?
An easy way to test this would be to call SubmitChanges() immediately before doing the present query.

Vulpes · Answer

Yeah, I think the problem with the first class must have been previously unsubmitted changes because (on the face of it) that's the only possible explanation for the exception still popping up when you made no changes at all to the latest record.
Anyway, pleased you've solved it, whatever the explanation was :)

Anthony Clarke · Answer

Hi Vulpes,
Your not going to believe this but out of curiosity i created a new class
pasted the code into in then tried again and it worked first time????
Stupid thing ha. Ill mark your answer as correct for your time and persistence though.
Thanks again Vulpes

Vulpes · Answer

I'm just wondering whether the query is actually returning a record to update - though, if it didn't, I think you'd get a null reference exception when executing the following line:
t.FieldValue = "PAYMENT PLAN" ;
because 't' would be null.
Just as an experiment, try replacing FirstOrDefault() in the query with Single().

Anthony Clarke · Answer

Interesting,
Yeah, if i comment it out i still seem to get the error message????

Vulpes · Answer

If you mean that you've removed this line:
t.LinkID = " Customer";
then I don't understand why you're still getting the error.
As you said originally, you're updating an existing record - not inserting a new one.
Do you still get the error if you temporarily comment out the next line, so that you're making no change at all?
t.FieldValue = "PAYMENT PLAN";

Anthony Clarke · Answer

I can't, It's not my database
I've removed t.FieldID from the update query though and just left t.FieldValue which is not a primary key and i still get the same response.
The only field i want to update is the t.FieldValue is there no way around this?
Thanks in advance Vulpes
Anthony

Vulpes · Answer

Yeah, I can see from the first screenshot that LinkId, together with FieldId and CustomerCode, make up the primary key for the table.
If you remove LinkId from the primary key (assuming you don't really need it in there), then that should solve the problem.

Anthony Clarke · Answer

Hi Vulpes,
Ill send you some screenshots in this zip

Vulpes · Answer

The code has become muddled but I'd guess it should look like this:
EbillzNotesDataContext EB = new EbillzNotesDataContext();
var t = (from p in EB.CustomFieldsDatasEB
where (p.CustomerCode == cuscode) && (p.FieldID == Convert.ToInt32(27))
select p).FirstOrDefault();
t.FieldValue = "PAYMENT PLAN"
t.LinkID = " Customer"
EB.SubmitChanges();
Which field or fields are used in the Primary Key for the table?
Although they don't look like promising candidates, could FieldValue or LinkId be one of them?