DataSet Relation Unique Column Values Errror

Hello experts;

I am attempting to create a simple application that will create a DataSet relation from two DataTables and output the results into a TreeView display.  I get an ArguementException error when I try to build this relation, it complains that I do not have unique values in my columns.

problematic code:

 Dim relUE As DataRelation = ds.Relations.Add("relUsageHistoryExtraCharge", ds.Tables("UsageHistory").Columns("OrderNo"), ds.Tables("ExtraCharge").Columns("OrderNo"))

The two tables I am tring to build a relation between are UsageHistory and ExtraCharges.

UsageHistory has the following fields:

ExtraCharge has the following fields:

Any help resolving this issue would be greatly appreciated.  Thank you ahead of time.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button1.Click
        'Creates a connection to the database
        Dim PSWconn As String = "Persist Security Info=False;Integrated Security=SSPI;database=FPSalesUsage;server=FPSQL01\SQL2005;Connect Timeout=60"
        Dim cn As New SqlConnection(PSWconn)
        'Creates 2 DataAdapters
        Dim daUsageHistory As New SqlDataAdapter("SELECT * FROM UsageHistory", cn)
        Dim daExtraCharge As New SqlDataAdapter("SELECT * FROM ExtraCharge", cn)
        'Creates and fills the dataset with the 2 DataAdapters
        Dim ds As New DataSet
        daUsageHistory.Fill(ds, "UsageHistory")
        daExtraCharge.Fill(ds, "ExtraCharge")
        'Creates a relation between the 2 DataTables       
        Dim relUE As DataRelation = ds.Relations.Add("relUsageHistoryExtraCharge", ds.Tables("UsageHistory").Columns("OrderNo"), ds.Tables("ExtraCharge").Columns("OrderNo"))
        With TreeView1
            'prevents the control from painting until the EndUpdate method is called
            'Clears/Deletes all nodes that currently exist
            'Loop through all rows of the UsageHistory table
            For Each drOrderNo As DataRow In ds.Tables("UsageHistory").Rows
                'Adds the OrderNo as a root node
                Dim nodParent As TreeNode
                nodParent = TreeView1.Nodes.Add(drOrderNo.Item("OrderNo").ToString)
                'Loops through the ECCodes related to the OrderNo
                For Each drECCode As DataRow In drOrderNo.GetChildRows(relUE)
                    'Adds the ECCode as a child of the OrderNo
                    Dim nodChild As TreeNode
                    nodChild = nodParent.Nodes.Add(drECCode.Item("ECCode").ToString)
                    If nodChild.Text.ToUpper.IndexOf("Y") >= 0 Then
                        nodChild.BackColor = Color.Gray
                    End If
                Next drECCode
            Next drOrderNo
            'Expands all nodes
            'Enables the redrawing of the tree view
            'Ensures that the first node is visible
        End With
    End Sub

Open in new window

gwosgoodIT MgrAsked:
Who is Participating?
mdouganConnect With a Mentor Commented:
I've seen samples where there is a one to many relationship in a DataRelation, however, I believe that the key has to be unique in the parent table, so, if there are dupilcate OrderNo in the UsageHistory table, that will probably be the problem.  You can figure that out easily enough by running this query:

Select OrderNo, Count(OrderNo) as Count
from UsageHistory
Group by OrderNo
order by 2 DESC

You can supply more than one parameter to the TreeNode Add method.  So far, this is how you are adding a child node...

Dim nodChild As TreeNode
nodChild = nodParent.Nodes.Add(drECCode.Item("ECCode").ToString)

You are only passing the first parameter, which is the KEY of the node.  By default, the TEXT of the node will take the same value as the key.  But, the KEY has to be unique across the whole tree.  So, if the ECCode is going to appear under different OrderNo's then you need to format the key so that it contains both the OrderNo and the ECCode.  If you pass two parameters to the Add method, the first one is the KEY and the second one is the TEXT.  So, your Add statement might look like this:

Dim nodChild As TreeNode
nodChild = nodParent.Nodes.Add((drECCode.Item("OrderNo").ToString + drECCode.Item("ECCode").ToString), drECCode.Item("ECCode").ToString)

The first parameter is simply concatenating the OrderNo and the ECCode so that the KEY is unique.

Assume that we have OrderNo's  

And ECCodes of

Then, the KEYs of the various nodes would look like:


The TEXT would just have the ECCode, so the display would look like



Is it failing on the statement line 16 where you are creating the datarelation?   Is OrderNo unique in the UsageHistory table?  If not, that is probably your problem.

I could see another possible place for an error when building your tree.  You are setting the parent node's key to the OrderNo, and then you are setting the child's key to the ECCode.  If the ECCode is not unique, then you might get a similar error.  (in other words if the same ECCode is found under other OrderNo's then you might get an error if you just use the ECCode as the node's key).

If the ECCode is only unique within an OrderNo, then you might have to set the node's key to OrderNo+ECCode, you can make the display text just display the ECCode, but the key needs to be unique across the entire tree.
gwosgoodIT MgrAuthor Commented:

Yes, the error occurs at line 16.  The OrderNo's *should* all be unique in the parent table, UsageHistory.  In the ExtraCharge table, the same OrderNo can appear more than once, if for example that order has more than one coded extra charge code (ECCode).  

In response to your Tree Node question, ECCodes are not unique in the table.  More than one OrderNo can have the same ECCode.  For example, OrderNo 12550 can have ECCodes A, F, and G, and OrderNo 12555 can have ECCodes F, G and S.

Can you show me what you are referring to when you say OrderNo+ECCode as the Node Key?

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

gwosgoodIT MgrAuthor Commented:

Thank you for that SQL query!  You are correct, I do have a duplicate OrderNo value in my UsageHistory table.  I will see what I can do about fixing that, it appears to be just one duplicate entry.

I see what you are saying about the child nodes, and that is the ultimate result I am looking for.  Thank you very much for your extended help.
gwosgoodIT MgrAuthor Commented:
When building my relation, can you have more than one table column?

For example, I would like to be able to have a relation between UsageHistory("OrderNo","ShipDate") and ExtraCharge("OrderNo","ShipDate").

If it is possible, what is the syntax?
Yes, it is possible, you have to create a couple of DataColumns collections.  Here is an example that builds a datarelation on three columns


'Declare the Columns - even though we have multiple fields, we only have two tables, hence TransactionColumns and DetailColumns
Dim TransactionColumns() as DataColumn
Dim DetailColumns() as DataColumn

TransactionColumns = New DataColum(){ds.Tables(0).Columns("TransID"), ds.Tables(0).Columns("CustomerID"), ds.Tables(0).Columns("SalePersonID")}
DetailColumns = New DataColumns(){ds.Tables(1).Columns("TransID"), ds.Tables(1).Columns("CustomerID"), ds.Tables(1).Columns("SalesPersonID")}
'We could also use all norminals, ordinals or any mixture of them

'Add the name and DataColumn arrays to the Relation
Dim Tran_Detail as New DataRelation("myRelationName", TransActionColumns, DetailColumns)
'Add the Relation to the DataSet

All Courses

From novice to tech pro — start learning today.