Solved

DataSet Relation Unique Column Values Errror

Posted on 2008-10-15
6
706 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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 …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now