Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

data relation based on multiple columns

I need to create a data relation between two data tables supporting two data grids based on multiple columns. Is this possible? Any sample code? I can only find documentation on doing this with a single column.

Environment is vb.net
2 Solutions
'try the following code
  Public cn As New SqlConnection
    Public cmd1 As New SqlCommand
    Public cmd2 As New SqlCommand
    Public cmd3 As New SqlCommand
    Public oda1 As New SqlDataAdapter
    Public oda2 As New SqlDataAdapter
    Public oda3 As New SqlDataAdapter
    Public ods As New DataSet

cn.ConnectionString = "Server=eteam08;Database=dbTestGrid;user id=sa;password=imran"
        'MsgBox("Connection Established")

        cmd1.Connection = cn
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select * from tblCustomer"
        oda1.SelectCommand = cmd1
        oda1.Fill(ods, "Customer")

        cmd2.Connection = cn
        cmd2.CommandType = CommandType.Text
        cmd2.CommandText = "select * from tblOrder"
        oda2.SelectCommand = cmd2
        oda2.Fill(ods, "order")

        cmd3.Connection = cn
        cmd3.CommandType = CommandType.Text
        cmd3.CommandText = "select * from tblPackage"
        oda3.SelectCommand = cmd3
        oda3.Fill(ods, "Package")

        ods.Relations.Add("Cstomers", ods.Tables("Customer").Columns(2), ods.Tables("order").Columns(1))
        ods.Relations.Add("Customers", ods.Tables("order").Columns(0), ods.Tables("Package").Columns(1))

        UltraGrid1.DataSource = ods
Something like this, This is an example using 3 keys to relate the 2 tables

'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

Featured Post

Independent Software Vendors: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now