asked on

Data set relation problem

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?

ASKER CERTIFIED SOLUTION

anyoneis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Moe DeShong

ASKER

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.

anyoneis

>> 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

Moe DeShong

ASKER

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.

anyoneis

>>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.

Moe DeShong

ASKER

Ok, I'll back up and do it right. Thanks for the help.

anyoneis

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