Solved

DataSet Relation Unique Column Values Errror

Posted on 2008-10-15
6
713 Views
Last Modified: 2008-10-15
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:
OrderNo
OrderDate
ShipDate
ShipCost
ShipMethod

ExtraCharge has the following fields:
OrderNo
ECCode
Description
Cost

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
            .BeginUpdate()
            'Clears/Deletes all nodes that currently exist
            .Nodes.Clear()
            '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
            .ExpandAll()
            'Enables the redrawing of the tree view
            .EndUpdate()
            'Ensures that the first node is visible
            .Nodes(0).EnsureVisible()
        End With
 
 
    End Sub

Open in new window

0
Comment
Question by:gwosgood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:mdougan
ID: 22721091
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.
0
 
LVL 2

Author Comment

by:gwosgood
ID: 22721253
Mdougan,

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?

0
 
LVL 18

Accepted Solution

by:
mdougan earned 500 total points
ID: 22724507
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  
1234
5678

And ECCodes of
E
F
G

Then, the KEYs of the various nodes would look like:
1234
   1234E
   1234F
   1234G

5678
   5678E
   5678F
   5678G

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

1234
   E
   F
   G

5678
   E
   F
   G

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 2

Author Comment

by:gwosgood
ID: 22724788
mdougan,

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.
0
 
LVL 2

Author Comment

by:gwosgood
ID: 22724904
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?
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22725224
Yes, it is possible, you have to create a couple of DataColumns collections.  Here is an example that builds a datarelation on three columns



VB.NET

'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
ds.Relations.Add(Tran_Detail)

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question