Strange DataTable PrimaryKey Behavior

Question

Hi everyone,

I have a DataTable that contains several DataColumns, two of which act as the primary key (think OrderDetails from the Northwind database). Here's what it looks like:

Dim tblPM As New DataTable("ProductMerchant")
Dim dc As DataColumn
With tblProducts
 dc = .Columns.Add("merchantID", GetType(Byte))
 dc = .Columns.Add("productID", GetType(Integer))
 dc = .Columns.Add("listPrice", GetType(Decimal))
 dc = .Columns.Add("ourPrice", GetType(Decimal))
 .PrimaryKey = New DataColumn() {.Columns(0), .Columns(1)}
End With

Ok. This table is filled with data from an anonymous type. What I do is check to see if the DataTable already contains the primary key. If it does, then I don't add the data. Here's how I do it:

'now add data to tblPM
'note that data has already been retrieved and placed in the items variable
For Each rd In items
 Dim keys() As Object = {rd.MerchantID, rd.ProductID}
 If tblPM.Rows.Contains(keys) Then
  'do not add
 Else
  tblPM.Rows.Add(New Object() {rd.MerchantID, rd.ProductID, rd.ListPrice, rd.OurPrice})
 End If
Next

Ok. Still with me? Good.

So, I add data to the DataTable, save it to the database, and check to see if all is well. Well, I discovered that some of the data isn't added to the table and it appears that the DataTable's primary key is the culprit. It seems that the primary key uses the specified DataColumns as two separate keys instead of one key. Here's what I mean.

If I added this to the my DataTable using the above For Each...Next statement

tblPM.Rows.Add(New Object() {"15", "223344", 30.50, 25})

all is well. But if I try to add

tblPM.Rows.Add(New Object() {"56", "223344", 30.50, 25})

after the previous addition, it isn't added to the DataTable. Same data as the first example, only the merchantID is different. It's a different key, yet it isn't added to the table. Hmm....

What's going on? I thought both DataColumns in the DataTable's primary key acted as one key. The second .Add call should've worked but it doesn't.

Can someone tell my what this is happening and how to fix it?

Thank you. Oh, and sorry for such a long post.

Answer 1

Hi again,

Well, after some Googling, I've found the answer. The culprit was this line:

Dim keys() As Object = {rd.MerchantID, rd.ProductID}

The code should look like this:

Dim keys() as Object = New Object() {rd.MerchantID, rd.ProductID}

That's all it took to get things working right. Shame on me for not catching the error earlier.