Solved

data relation based on multiple columns

Posted on 2004-10-15
4
521 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:fruffing
4 Comments
 
LVL 2

Accepted Solution

by:
ahmadfuwad earned 25 total points
ID: 12318151
'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"
        cn.Open()
        '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
0
 
LVL 25

Assisted Solution

by:RonaldBiemans
RonaldBiemans earned 25 total points
ID: 12318464
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
ds.Relations.Add(Tran_Detail)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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 …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

17 Experts available now in Live!

Get 1:1 Help Now