Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data set relation problem

Posted on 2006-06-10
7
Medium Priority
?
260 Views
Last Modified: 2010-04-23
I use the following code to retrieve two datasets and create a relationship

      Dim Command As New OleDbCommand
      adItems = New OleDbDataAdapter
      dtItems = New DataTable
      adItemGridPackage = New OleDbDataAdapter
      dtItemGridPackage = New DataTable
      fSql = " SELECT ContractItems.* " _
         & "FROM(ContractItems) " _
         & "WHERE (((ContractItems.txtContractNumber)= " & oContract.ContractNumber & ") AND ContractItems.blnPackageSubItem = 0) " _
         & "ORDER BY ContractItems.blnLaborOnly DESC; "
      Command.Connection = oContract.OpenConnection
      Command.CommandText = fSql
      adItems.SelectCommand = Command
      adItems.Fill(dtItems)

      Debug.Print(dtItems.Rows.Count)
      'oContractItem.CloseConnection()
      ' get the package sub items
      fSql = " SELECT ContractItems.* " _
         & "FROM(ContractItems) " _
         & "WHERE (((ContractItems.txtContractNumber)= " & oContract.ContractNumber & ") AND ContractItems.blnPackageSubItem <> 0) "

      Command.CommandText = fSql
      adItemGridPackage.SelectCommand = Command
      adItemGridPackage.Fill(dtItemGridPackage)
      Command.Dispose()
      Command = Nothing
      oContractItem.CloseConnection()
      Debug.Print(dtItemGridPackage.Rows.Count)
      'build the data set
      dsGridItems = New DataSet
      dsGridItems.Tables.Add(dtItems)
      dsGridItems.Tables.Add(dtItemGridPackage)
      ParentColumn = New DataColumn
      ParentColumn = dtItems.Columns("txtContractItemGuid")
      ChildColumn = New DataColumn
      ChildColumn = dtItemGridPackage.Columns("txtContractItemGuid")
      relPackage = New DataRelation("Package", ParentColumn, ChildColumn)
      dsGridItems.Relations.Add(relPackage)                 **************************This line*********************

The marked line throws an error "The columns don't current have unique values".   Even with this error the relationship is created and the grid that has the dataset as it source displays correctly.  The only thing I see is that both datatables have the same table as the source. Perhaps this causes a problem.  What am I doing wrong?  
0
Comment
Question by:Moed
[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
  • 4
  • 3
7 Comments
 
LVL 11

Accepted Solution

by:
anyoneis earned 1000 total points
ID: 16878527
There error message means that dtItems has more than one row with the same txtContractItemGuid. Otherwise, what you are doing should work.  

David
0
 
LVL 2

Author Comment

by:Moed
ID: 16881042
If you look at the sql you'll see that I use the  ContractItems.blnPackageSubItem <> 0 or ContractItems.blnPackageSubItem = 0 to get the correct records.  Is it considered grossly poor design to just ignore the error?  For this particular application I don't really see a reason to create and maintain another whole table unless I am asking for problems down the road.
0
 
LVL 11

Expert Comment

by:anyoneis
ID: 16881365
>>  Is it considered grossly poor design to just ignore the error

WOULD YOU BE ABLE TO SLEEP AT NIGHT??? :-)

I can't say. I know I would not be able to ignore the error.

I should have asked some more questions about txtContractItemGuid. Is this column nullable?
Do all rows have a unique value here, or are there some rows that share the same value?

David
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:Moed
ID: 16885809
Sleeping could be a problem.   Here's the way it works.  Some of the product that we order, lets say a toilet, is sold in three parts.  1. tank 2. bowl 3. seat.  It's difficult to teach an entry person all the different parts for all the different products so when I input an item in the products table I input all the parts as seperate items then input a master description item.  This is all in the same table.  Then I assign the parts to the master item via a reference in a 2nd table.  When the user takes off the master item it pulls all the parts into the order assigns a guid to the parts and the master description and sets the blnPackageSubItem to true on all the items.  The master description is just shown to group all the parts.  Thats why I get the relation error.  The guid is unique to the group but its the blnPackageSubItem field that actually seperates the description item from the items that need ordered.  Hope that helps a little in your evaluation of the problem.
0
 
LVL 11

Expert Comment

by:anyoneis
ID: 16887105
>>The guid is unique to the group but its the blnPackageSubItem ....

Unique to the group is not enough. A relation requires that the parent column be unique to the row, so that when you ask it to navigate from child to parent, it can yield a single result.

0
 
LVL 2

Author Comment

by:Moed
ID: 16887863
Ok,  I'll back up and do it right.  Thanks for the help.
0
 
LVL 11

Expert Comment

by:anyoneis
ID: 16888487
Maybe you can add a column or two to the relation, thereby making it unique.

One route would be to take blnPackageSubItem and change it from bool to int, give the main item a value of 0 and incrementally assign numbers to the subitems. Then, you would add this column to the datarelation.

Ciao!
David
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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