none
how to populate an unbound column in a datagridview

    Question

  • Hi everyone,

    Having 2 tables say T1 (parent table) and T2 (child table) in datarelation thru Column1 (an id) in T1 with column1 ( an id) in T2; The chid table T2 is datamember to a datagridview. To this datagridview an unbound column has been added. The problem is: how to get the datagridview to display the values in column2 of T1. Actually the datagridview shows the child table T2 and the unbound column is empty. Thanks.

    Wednesday, May 3, 2017 7:45 PM

Answers

  • Hi vblover29,

    I create two table, one is ParentTable table, another is ChildTable, you can see these below.

    Using DataGridView to display ParentTable and ChildTable's Phone column, firstly I write the correct Datasource.

    select pt.* , ct.phone from ParentTable pt left join ChildTable ct on pt.Id=ct.ParentId
    Imports System.Data.SqlClient
    
    Public Class Form5
        Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            fun()
        End Sub
        Private Sub fun()
            Dim dt As New DataTable()
            Dim strConn As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Database2.mdf;Integrated Security=True"
            Using conn As New SqlConnection(strConn)
                Dim selectSql = "select pt.* , ct.phone from ParentTable pt left join ChildTable ct on pt.Id=ct.ParentId"
                Using com As New SqlCommand(selectSql, conn)
                    Try
                        conn.Open()
                        Dim adapter As New SqlDataAdapter(com)
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        conn.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString())
                    End Try
                End Using
            End Using
        End Sub
    
    End Class

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 4, 2017 5:25 AM
    Moderator
  • It sounds like you want the unbound column in datagridview2 to be an expression column.  I believe you want to create a Parent expression to retrieve the relevant field from the parent table.

    From the MSDN documentation linked:

    Parent/Child Relation Referencing

    A parent table may be referenced in an expression by prepending the column name with Parent. For example, the Parent.Price references the parent table's column named Price.

    When a child has more than one parent row, use Parent(RelationName).ColumnName. For example, the Parent(RelationName).Price references the parent table’s column named Price via the relation.

    A column in a child table may be referenced in an expression by prepending the column name with Child. However, because child relationships may return multiple rows, you must include the reference to the child column in an aggregate function. For example, Sum(Child.Price) would return the sum of the column named Price in the child table.

    If a table has more than one child, the syntax is: Child(RelationName). For example, if a table has two child tables named Customers and Orders, and the DataRelation object is named Customers2Orders, the reference would be as follows:

    Avg(Child(Customers2Orders).Quantity)


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, May 4, 2017 12:19 PM
    Moderator
  • If you would like to see what Reed provided, see my MSDN master-detail code sample. I setup for displaying child data in the master table in CustomerOrders.vb

    Dim FreightExpression As String = "Sum(Child(CustomersOrders).Freight) " 
    

    The sample was done with MS-Access but that does not matter as we are dealing the data containers (DataSet/DataTable and relations) after loading, I show the same pattern for SQL-Server in this MSDN code sample so you can see the methods used are the same yet this example does not have the Child(Relationship) part.

    Lastly, I use the following to setup relationships

    Public Module DataRelationsExtensions
        ''' <summary>
        ''' USed to create a one to many relationship for a master-detail in a DataSet.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="MasterTableName">master table</param>
        ''' <param name="ChildTableName">child table of master table</param>
        ''' <param name="MasterKeyColumn">master table primary key</param>
        ''' <param name="ChildKeyColumn">child table of master's primary key</param>
        <DebuggerStepThrough()>
        <Runtime.CompilerServices.Extension()>
        Public Sub SetRelation(ByVal sender As DataSet, ByVal MasterTableName As String, ByVal ChildTableName As String, ByVal MasterKeyColumn As String, ByVal ChildKeyColumn As String)
    
            sender.Relations.Add(
             New DataRelation(String.Concat(MasterTableName, ChildTableName),
                sender.Tables(MasterTableName).Columns(MasterKeyColumn),
                sender.Tables(ChildTableName).Columns(ChildKeyColumn)
             )
          )
    
        End Sub
    
    End Module
    Which is a language extension method that takes the parent and child table names, creates a relationship using a combination of the two table names for the relationship name.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by vblover29 Friday, May 5, 2017 4:40 PM
    Thursday, May 4, 2017 12:38 PM
    Moderator
  • Oh this is a duplicate from the French forum, so I'm adding my answer here too :)

    Have a look at this code using a DataSet made of 2 Tables and a DataRelation and refer to the french forum for the GIF showing the app running:

    Private leDataSet As DataSet
    
        Private table1 As DataTable
        Private table2 As DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ' Création d'un dataset contenant 2 tables
    
            ' Création Table1
            table1 = New DataTable("clients")
            table1.Columns.Add("num")
            table1.Columns.Add("nom")
            table1.Columns.Add("montant")
            ' Remplir table1
            table1.Rows.Add(1, "Jean", 1050)
            table1.Rows.Add(2, "Albert", 2546)
            table1.Rows.Add(3, "Marie", 3221)
    
            ' Création Table2
            table2 = New DataTable("commandes")
            table2.Columns.Add("num")
            table2.Columns.Add("date")
            table2.Columns.Add("montant")
            ' Remplir table2
            table2.Rows.Add(3, "01/01/2017", 1050)
            table2.Rows.Add(1, "05/01/2017", 2546)
            table2.Rows.Add(3, "16/04/2017", 3221)
    
            ' Affichage dans les DGV
            DataGridView1.DataSource = table1
            DataGridView2.DataSource = table2
    
            ' Configuration du DGV3 pour avoir 2 colonnes: Nom et DateDeCommande
            Dim cl1 As New DataGridViewTextBoxColumn
            With cl1
                .HeaderText = "Nom"
                .Name = "Nom"
                .Width = DataGridView3.Width / 2
                .ReadOnly = True
            End With
            Dim cl2 As New DataGridViewTextBoxColumn
            With cl2
                .HeaderText = "Date Commande"
                .Name = "Date"
                .Width = DataGridView3.Width / 2
                .ReadOnly = True
            End With
            DataGridView3.Columns.Insert(0, cl1)
            DataGridView3.Columns.Insert(1, cl2)
            DataGridView3.RowHeadersVisible = False
    
            ' Création du DataSet
            Dim customerOrders As DataSet = New DataSet("Business")
            customerOrders.Tables.Add(table1)
            customerOrders.Tables.Add(table2)
    
            ' Création de la Relation
            Dim customerOrdersRelation As DataRelation = customerOrders.Relations.Add("LesCommandes",
                                                                                      customerOrders.Tables("clients").Columns("num"),
                                                                                      customerOrders.Tables("commandes").Columns("num"))
            ' Utilisation de la relation pour afficher dans le DGV3
            Dim custRow, orderRow As DataRow
    
            For Each custRow In customerOrders.Tables("clients").Rows
                Debug.Print("Customer ID:" & custRow("nom").ToString())
    
                For Each orderRow In custRow.GetChildRows(customerOrdersRelation)
                    Debug.Print(orderRow("date").ToString())
                    DataGridView3.Rows.Add(custRow("nom").ToString(), orderRow("date").ToString())
                Next
            Next
        End Sub

    • Marked as answer by vblover29 Friday, May 5, 2017 4:41 PM
    Thursday, May 4, 2017 12:43 PM

All replies

  • Hi vblover29,

    I create two table, one is ParentTable table, another is ChildTable, you can see these below.

    Using DataGridView to display ParentTable and ChildTable's Phone column, firstly I write the correct Datasource.

    select pt.* , ct.phone from ParentTable pt left join ChildTable ct on pt.Id=ct.ParentId
    Imports System.Data.SqlClient
    
    Public Class Form5
        Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            fun()
        End Sub
        Private Sub fun()
            Dim dt As New DataTable()
            Dim strConn As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Database2.mdf;Integrated Security=True"
            Using conn As New SqlConnection(strConn)
                Dim selectSql = "select pt.* , ct.phone from ParentTable pt left join ChildTable ct on pt.Id=ct.ParentId"
                Using com As New SqlCommand(selectSql, conn)
                    Try
                        conn.Open()
                        Dim adapter As New SqlDataAdapter(com)
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        conn.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString())
                    End Try
                End Using
            End Using
        End Sub
    
    End Class

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 4, 2017 5:25 AM
    Moderator
  • It sounds like you want the unbound column in datagridview2 to be an expression column.  I believe you want to create a Parent expression to retrieve the relevant field from the parent table.

    From the MSDN documentation linked:

    Parent/Child Relation Referencing

    A parent table may be referenced in an expression by prepending the column name with Parent. For example, the Parent.Price references the parent table's column named Price.

    When a child has more than one parent row, use Parent(RelationName).ColumnName. For example, the Parent(RelationName).Price references the parent table’s column named Price via the relation.

    A column in a child table may be referenced in an expression by prepending the column name with Child. However, because child relationships may return multiple rows, you must include the reference to the child column in an aggregate function. For example, Sum(Child.Price) would return the sum of the column named Price in the child table.

    If a table has more than one child, the syntax is: Child(RelationName). For example, if a table has two child tables named Customers and Orders, and the DataRelation object is named Customers2Orders, the reference would be as follows:

    Avg(Child(Customers2Orders).Quantity)


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, May 4, 2017 12:19 PM
    Moderator
  • If you would like to see what Reed provided, see my MSDN master-detail code sample. I setup for displaying child data in the master table in CustomerOrders.vb

    Dim FreightExpression As String = "Sum(Child(CustomersOrders).Freight) " 
    

    The sample was done with MS-Access but that does not matter as we are dealing the data containers (DataSet/DataTable and relations) after loading, I show the same pattern for SQL-Server in this MSDN code sample so you can see the methods used are the same yet this example does not have the Child(Relationship) part.

    Lastly, I use the following to setup relationships

    Public Module DataRelationsExtensions
        ''' <summary>
        ''' USed to create a one to many relationship for a master-detail in a DataSet.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="MasterTableName">master table</param>
        ''' <param name="ChildTableName">child table of master table</param>
        ''' <param name="MasterKeyColumn">master table primary key</param>
        ''' <param name="ChildKeyColumn">child table of master's primary key</param>
        <DebuggerStepThrough()>
        <Runtime.CompilerServices.Extension()>
        Public Sub SetRelation(ByVal sender As DataSet, ByVal MasterTableName As String, ByVal ChildTableName As String, ByVal MasterKeyColumn As String, ByVal ChildKeyColumn As String)
    
            sender.Relations.Add(
             New DataRelation(String.Concat(MasterTableName, ChildTableName),
                sender.Tables(MasterTableName).Columns(MasterKeyColumn),
                sender.Tables(ChildTableName).Columns(ChildKeyColumn)
             )
          )
    
        End Sub
    
    End Module
    Which is a language extension method that takes the parent and child table names, creates a relationship using a combination of the two table names for the relationship name.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by vblover29 Friday, May 5, 2017 4:40 PM
    Thursday, May 4, 2017 12:38 PM
    Moderator
  • Oh this is a duplicate from the French forum, so I'm adding my answer here too :)

    Have a look at this code using a DataSet made of 2 Tables and a DataRelation and refer to the french forum for the GIF showing the app running:

    Private leDataSet As DataSet
    
        Private table1 As DataTable
        Private table2 As DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ' Création d'un dataset contenant 2 tables
    
            ' Création Table1
            table1 = New DataTable("clients")
            table1.Columns.Add("num")
            table1.Columns.Add("nom")
            table1.Columns.Add("montant")
            ' Remplir table1
            table1.Rows.Add(1, "Jean", 1050)
            table1.Rows.Add(2, "Albert", 2546)
            table1.Rows.Add(3, "Marie", 3221)
    
            ' Création Table2
            table2 = New DataTable("commandes")
            table2.Columns.Add("num")
            table2.Columns.Add("date")
            table2.Columns.Add("montant")
            ' Remplir table2
            table2.Rows.Add(3, "01/01/2017", 1050)
            table2.Rows.Add(1, "05/01/2017", 2546)
            table2.Rows.Add(3, "16/04/2017", 3221)
    
            ' Affichage dans les DGV
            DataGridView1.DataSource = table1
            DataGridView2.DataSource = table2
    
            ' Configuration du DGV3 pour avoir 2 colonnes: Nom et DateDeCommande
            Dim cl1 As New DataGridViewTextBoxColumn
            With cl1
                .HeaderText = "Nom"
                .Name = "Nom"
                .Width = DataGridView3.Width / 2
                .ReadOnly = True
            End With
            Dim cl2 As New DataGridViewTextBoxColumn
            With cl2
                .HeaderText = "Date Commande"
                .Name = "Date"
                .Width = DataGridView3.Width / 2
                .ReadOnly = True
            End With
            DataGridView3.Columns.Insert(0, cl1)
            DataGridView3.Columns.Insert(1, cl2)
            DataGridView3.RowHeadersVisible = False
    
            ' Création du DataSet
            Dim customerOrders As DataSet = New DataSet("Business")
            customerOrders.Tables.Add(table1)
            customerOrders.Tables.Add(table2)
    
            ' Création de la Relation
            Dim customerOrdersRelation As DataRelation = customerOrders.Relations.Add("LesCommandes",
                                                                                      customerOrders.Tables("clients").Columns("num"),
                                                                                      customerOrders.Tables("commandes").Columns("num"))
            ' Utilisation de la relation pour afficher dans le DGV3
            Dim custRow, orderRow As DataRow
    
            For Each custRow In customerOrders.Tables("clients").Rows
                Debug.Print("Customer ID:" & custRow("nom").ToString())
    
                For Each orderRow In custRow.GetChildRows(customerOrdersRelation)
                    Debug.Print(orderRow("date").ToString())
                    DataGridView3.Rows.Add(custRow("nom").ToString(), orderRow("date").ToString())
                Next
            Next
        End Sub

    • Marked as answer by vblover29 Friday, May 5, 2017 4:41 PM
    Thursday, May 4, 2017 12:43 PM