locked
Relation for multiple columns between two tables RRS feed

  • Question

  • <vb.net 2008>
    Table : MST_COMPANY / EMP_MASTER
    Column : COMPANY_ID / LOCATION_ID

    How do I add relations for two colums between two tables? I tried below way but it does not work.
    Is there any other way?

    Dim relCompanyEmp1 As DataRelation = ds.Relations.Add("relSectionEmp", ds.Tables("MST_COMPANY").Columns("COMPANY_ID"), ds.Tables("EMP_MASTER").Columns("COMPANY_ID"))
            Dim relCompanyEmp2 As DataRelation = ds.Relations.Add("relSectionEmp", ds.Tables("MST_COMPANY").Columns("LOCATION_ID"), ds.Tables("EMP_MASTER").Columns("LOCATION_ID"))


    <Full code> --> Treeview

     Public Sub Show_tvEmplsit()
            Dim strLoginID = Microsoft.VisualBasic.Mid(frmMain.TstEmpInfo.Text, 2, 5)

            Dim strCN As String = GetConnectionString()
            Dim cn As New SqlConnection(strCN)

            Dim daLocation As New SqlDataAdapter("SELECT LOCATION_ID FROM EXC_COMPANY WHERE ACTIVE = 1 AND EXC_ID = '" & strLoginID & "'", cn)
            Dim daCompany As New SqlDataAdapter("SELECT COMPANY_ID,COMPANY_CD,LOCATION_ID FROM MST_COMPANY WHERE ACTIVE = 1", cn)
            Dim daEmplist As New SqlDataAdapter("SELECT COMPANY_ID,EMP_ID, LAST_NM, FIRST_NM FROM EMP_MASTER WHERE ACTIVE = 1", cn)

            Dim ds As New DataSet
            daLocation.Fill(ds, "EXC_COMPANY")
            daCompany.Fill(ds, "MST_COMPANY")
            daEmplist.Fill(ds, "EMP_MASTER")


            Dim relExcCompany As DataRelation = ds.Relations.Add("relSectionLocation", ds.Tables("EXC_COMPANY").Columns("LOCATION_ID"), ds.Tables("MST_COMPANY").Columns("LOCATION_ID"))
            Dim relCompanyEmp1 As DataRelation = ds.Relations.Add("relSectionEmp", ds.Tables("MST_COMPANY").Columns("COMPANY_ID"), ds.Tables("EMP_MASTER").Columns("COMPANY_ID"))
            Dim relCompanyEmp2 As DataRelation = ds.Relations.Add("relSectionEmp", ds.Tables("MST_COMPANY").Columns("LOCATION_ID"), ds.Tables("EMP_MASTER").Columns("LOCATION_ID"))


            With tvEmplist
                .BeginUpdate()
                .Nodes.Clear()
                .ImageList = ilEmplist
                For Each drLocation As DataRow In ds.Tables("EXC_COMPANY").Rows
                    Dim nodParent As TreeNode
                    nodParent = .Nodes.Add(drLocation.Item("LOCATION_ID").ToString)
                    nodParent.ImageIndex = 1

                    For Each drCompany As DataRow In drLocation.GetChildRows(relExcCompany)
                        Dim nodChild As TreeNode
                        nodChild = nodParent.Nodes.Add(drCompany.Item("COMPANY_CD").ToString)
                        nodChild.ImageIndex = 0
                    Next drCompany
                Next drLocation

                For Each drCompEmp As DataRow In ds.Tables("MST_COMPANY").Rows
                    Dim nodParent As TreeNode
                    nodParent = .Nodes.Add(drCompEmp.Item("COMPANY_ID").ToString)
                    nodParent.ImageIndex = 1

                    For Each drEmp As DataRow In drCompEmp.GetChildRows(relCompanyEmp1)
                        Dim nodChild As TreeNode
                        nodChild = nodParent.Nodes.Add(drEmp.Item("EMP_ID").ToString)
                        nodChild.ImageIndex = 0
                    Next drEmp
                Next drCompEmp

                'Expands all nodes
                '.ExpandAll()
                ''Enables the redrawing of the tree view
                .EndUpdate()
                'Ensures that the first node is visible
                .Nodes(0).EnsureVisible()
                .ExpandAll()
            End With

        End Sub

    Friday, January 8, 2010 1:13 AM

All replies


  • in your query string, use sample of this:


    SELECT table1.*, table2.* FROM table2 INNER JOIN table1 ON table1.PrimaryKey = table2.ForeignKey
    
    


    Hope this help you


    -Harrie-
    Friday, January 8, 2010 2:02 AM
  • Harrie.

    Can you be more specific? I am trying to implement a treeview with tables that has a relation with two foreign Keys between two tables.

    Table : MST_COMPANY / EMP_MASTER
    Column : COMPANY_ID / LOCATION_ID (Both table has those two colums and they need to be related)

    You mentioned to use "SELECT table1.*, table2.* FROM table2 INNER JOIN table1 ON table1.PrimaryKey = table2.ForeignKey".

    1. Should I use it for below?
    Dim daEmplist As New SqlDataAdapter("SELECT COMPANY_ID,EMP_ID, LAST_NM, FIRST_NM FROM EMP_MASTER WHERE ACTIVE = 1", cn)

    -->Dim daEmplist As New SqlDataAdapter("SELECT table1.*, table2.* FROM table2 INNER JOIN table1 ON table1.PrimaryKey = table2.ForeignKey", cn)

    2. The things is I have to mention the relation for the treview like below.

    Dim relCompanyEmp As DataRelation = ds.Relations.Add("relSectionEmp", ds.Tables("TB_MST_COMPANY").Columns("LOCATION_ID"), ds.Tables("EMP_MASTER").Columns("LOCATION_ID"))

                    For Each drCompany As DataRow In drLocation.GetChildRows(relExcCompany)
                        Dim nodChild As TreeNode
                        nodChild = nodParent.Nodes.Add(drCompany.Item("LOCATION_ID").ToString)
                        nodChild.ImageIndex = 0
                    Next drCompany

    Friday, January 8, 2010 5:25 PM

  • Hi Manripo,

    Have you learned about INNER JOIN in SQL Server Books Online?? Or you can check here

    in your case:
    Table : MST_COMPANY / EMP_MASTER
    Column : COMPANY_ID / LOCATION_ID

    assumption:
    table1: MST_COMPANY
    table2: EMP_MASTER
    table1.PK: COMPANY_ID
    table2.FK: LOCATION_ID

    you can use query in your DataAdapter like this:


    Dim daEmplist As New SqlDataAdapter("SELECT MST_COMPANY.*, EMP_MASTER.* FROM EMP_MASTER INNER JOIN MST_COMPANY ON MST_COMPANY.COMPANY_ID = EMP_MASTER.LOCATION_ID", cn)
    

    Hope this help you
    • Marked as answer by Jeff Shan Wednesday, January 13, 2010 2:00 AM
    • Unmarked as answer by Hoorayo Thursday, January 14, 2010 9:06 PM
    Monday, January 11, 2010 1:57 AM

  • This is the answer for my question which I found out to make create relationship thru code.


            Dim parentColumns(1) As DataColumn
            Dim childColumns(1) As DataColumn

            parentColumns(0) = ds.Tables("TB_MST_COMPANY").Columns("LOCATION_ID")
            parentColumns(1) = ds.Tables("TB_MST_COMPANY").Columns("COMPANY_ID")

            childColumns(0) = ds.Tables("TB_EMP_MASTER").Columns("LOCATION_ID")
            childColumns(1) = ds.Tables("TB_EMP_MASTER").Columns("COMPANY_ID")

            Dim bConstraints As Boolean = True
            ' Create DataRelation.
            Dim relCompanyEmp As DataRelation = New DataRelation("relSectionEmp", parentColumns, childColumns, bConstraints)

     

    Thursday, January 14, 2010 9:11 PM