Datagridview Violation of Primary key when editing records

Question

Hi all,

I have a DataGridView binded to a DataAdapter to manage the details of an invoice (products). The Table fields are:
ID_Invoice int (PK)
ID_Product int (PK)
Quantity numeric(18,2)

ID_Invoice is a hidden column so can’t be modified. So I only have two columns: Product and Quantity and the Product is a DataGridViewComboBoxColumn

I am able to Insert, update and delete records without problems, but I found a strange scenario when I edit the records of an existing Invoice.

Let’s say I have Invoice 111 with these products:
Product                            Quantity
Product A                           100
Product B                           200
Product C                           150

I have a validation in place to detect duplicate products on the grid (as Product is part of the PK)
Now… I edit the Invoice and get those records on the grid. I select first row and change Product A with Product C. Then I change product C with Product D and click Save (dataadapter.update())
Product                           Quantity
Product C                           100
Product B                           200
Product D                           150

… And I get a violation of PK error. I guess is because the DataAdapter tries to make the changes in sequential order and when tries to Update first record with Product C that product exists on the DB.

Is there a simple way to avoid this? I know 100% that there will be no duplicate records at the end because I validate row by row to detect duplicates.

Thanks for your help.

Rodrigo

Answer 1

The PK of a record should not be changed. Add another field (ID) as the PK of the table, and add a unique index consisting of the two fields (ID_Invoice, ID_Product).

---

Armin

Answer 2

Thanks for your answer Armin. But adding another field as PK and creating a unique index for those fields only changed the error to "Cannot insert duplicate key row in object 'dbo.Invoice_Detail' with unique index 'IX_Invoice_Detail'

I guess the problem is that the Datagridview tries to update the records sequentially. When trying to update the first record from Product A to Product C there is already another record in the DB for that unique index (row #3) that will later change to Product D and that causes the error.

I'm thinking on deleting all the records for that key before calling the update method so all the records are inserted (instead of updated) but that will cause a Concurrency Violation error as the records changed since the DA was loaded.

Another could be deleting the index and relying on the app for proper validation of duplicated products (this is really ugly!)

Any other idea? I can't believe I'm the first one running into this issue.

Answer 3

Have you also updated the Dataset in the Dataset designer? It was created with the old table design.

But you're right in respect to the problem that I also once had to sequentially update the database by executing the insert/update/delete queries individually instead of just calling the DataAdapter's Update method. I've also read something about it, but, I'm afraid, I currently don't remember. I hope someone else will continue from here for me....

---

Armin

Answer 4

I am not using the designer. I am creating it programmaticaly so no need to refresh it. I just had to change the Query and add the new PK column.

I think is a pretty common scenario where users try to change the order of the records so they invert them.

There could be one more (complex) solution that will require to go through the DA and verify the new value of the Index columns against the old values of all the other rows. 

Answer 5

Hi RodrigoCFT,

Welcome to the MSDN forum.

In my opinion, your issue is caused by design.

In general, the primary key will not be changed. So instead of finding a way to avoid it, you may need to change the structure of your table.

Base on my experience, you situation needs two tables at least. Separate your items into 4items: ID_Invoice, ID_product, product name, product quantity.

Create two tables: invoice table (ID_Invoice[Primary key], product name), Product table(ID_product [Primary key], product name, product quantity).

Make the columns of primary key non-editable. In this way, you can edit several items at the same time.

Hope it helps.

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

Answer 6

Mark,

I agree with you, however I think that the items on the invoice are stored in an association table that we're talking about. And that one requires the fields ID_Invoice and ID_Product, and, as already suggested an (unchanging) ID (PK).

Rodrigo,

yes, it's a common scenario that users change the order, but this is not done by changing the PK. You didn't write if you have an additional consecutive number determining the order of the invoice positions. If you had one (let's call it 'Position'), there would be the option to use ID_Invoice + Position as the PK. Then you were able to keep the PK and change the content of the records, e.g. swap the content of the ID_Product field.

In any case, if you have an additional ID as the PK, you can also change the content of the Position field to rearrange invoice positions.

---

Armin

Answer 7

Hey guys, thanks for your answers.

Mark, this is an association table for the products of an invoice. I have an Invoice Table (master) with all the invoice fields.

I guess is by design and the easiest way to solve it will be to delete all the records and insert them again after update.