Solved

DataSet Relation Unique Column Values Errror

Posted on 2008-10-15
6
705 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

10 Experts available now in Live!

Get 1:1 Help Now