locked
add datatable to a bindingsource RRS feed

  • Question

  • Hi Guys, any idea on how to add a new column into a databound datagrid1 and fill it with data from another Datatable

    Databound Datagrid1

    Datatable

    • Edited by Sysaide Monday, April 15, 2013 2:36 AM
    Monday, April 15, 2013 1:38 AM

Answers

  • Hello,

    The following loads mocked data into a DataTable which becomes the DataSource of a BindingSource which then is set to the DataSource of a DataGridView. Pressing Button1 we see if a column named Account belongs to the DataTable above, if it is not there we add it. Next we populate the column based on another DataTable populated with mocked data.

    The key to this demo to work is we have a primary key in both tables so we can match up data. So with that said, out of six rows, one is not populated as there is no matching row in the second DataTable (ID = 5).

    NOTE I did not match to your data because there was no matching key to relate both tables together.

    VS2010, VB.NET, Option Strict On

    Public Class Form1
        WithEvents bsCustomers As New BindingSource
        Private Sub Form1_Load(
            ByVal sender As System.Object,
            ByVal e As System.EventArgs) _
        Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.AddRange(New DataColumn() _
                {
                    New DataColumn With
                    {
                        .ColumnName = "ID",
                        .DataType = GetType(Int32),
                        .AutoIncrement = True,
                        .AutoIncrementSeed = 1,
                        .ReadOnly = True
                    },
                    New DataColumn With
                    {
                        .ColumnName = "FirstName",
                        .DataType = GetType(String)
                    },
                    New DataColumn With
                    {
                        .ColumnName = "LastName",
                        .DataType = GetType(String),
                        .ReadOnly = True
                    }
                }
            )
            dt.Rows.Add(New Object() {Nothing, "Nkundiushuti", "Gaseitsiwe"})
            dt.Rows.Add(New Object() {Nothing, "Isaak", "Lundin"})
            dt.Rows.Add(New Object() {Nothing, "Maura", "Abaszada"})
            dt.Rows.Add(New Object() {Nothing, "Mary", "Jones"})
            dt.Rows.Add(New Object() {Nothing, "Karen", "Smith"})
            dt.Rows.Add(New Object() {Nothing, "Trevor", "Adams"})
            bsCustomers.DataSource = dt
            DataGridView1.DataSource = bsCustomers
        End Sub
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim dt As DataTable = CType(bsCustomers.DataSource, DataTable)
            If Not dt.Columns.Contains("Account") Then
                Dim Items As DataRow() = Nothing
                dt.Columns.Add(New DataColumn With {.ColumnName = "Account", .DataType = GetType(String)})
                Dim dtMore As DataTable = GetMoreData(dt)
                For Each row As DataRow In dt.Rows
                    Items = dtMore.Select("ID=" & row.Field(Of Int32)("ID"))
                    If Items.Count = 1 Then
                        row.SetField(Of String)("Account", Items(0).Field(Of String)("account"))
                    End If
                Next
            End If
        End Sub
        Private Function GetMoreData(ByVal dtFromDataGridView As DataTable) As DataTable
            Dim dt As New DataTable
            dt.Columns.AddRange(New DataColumn() _
                {
                    New DataColumn With
                    {
                        .ColumnName = "ID",
                        .DataType = GetType(Int32)
                    },
                    New DataColumn With
                    {
                        .ColumnName = "Account",
                        .DataType = GetType(String),
                        .ReadOnly = True
                    }
                }
            )
            dt.Rows.Add(New Object() {6, "A600"})
            dt.Rows.Add(New Object() {1, "A100"})
            dt.Rows.Add(New Object() {3, "A300"})
            dt.Rows.Add(New Object() {2, "A200"})
            dt.Rows.Add(New Object() {4, "A400"})
            Return dt
        End Function
    End Class


    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.

    • Marked as answer by Sysaide Tuesday, April 16, 2013 2:31 AM
    Monday, April 15, 2013 1:22 PM
    • ID1 and ID are not the same.
    • All tables should have a primary key, this is how you find records.

    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.

    • Marked as answer by Sysaide Tuesday, April 16, 2013 2:32 AM
    Tuesday, April 16, 2013 2:23 AM

All replies

  • Hello,

    The following loads mocked data into a DataTable which becomes the DataSource of a BindingSource which then is set to the DataSource of a DataGridView. Pressing Button1 we see if a column named Account belongs to the DataTable above, if it is not there we add it. Next we populate the column based on another DataTable populated with mocked data.

    The key to this demo to work is we have a primary key in both tables so we can match up data. So with that said, out of six rows, one is not populated as there is no matching row in the second DataTable (ID = 5).

    NOTE I did not match to your data because there was no matching key to relate both tables together.

    VS2010, VB.NET, Option Strict On

    Public Class Form1
        WithEvents bsCustomers As New BindingSource
        Private Sub Form1_Load(
            ByVal sender As System.Object,
            ByVal e As System.EventArgs) _
        Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.AddRange(New DataColumn() _
                {
                    New DataColumn With
                    {
                        .ColumnName = "ID",
                        .DataType = GetType(Int32),
                        .AutoIncrement = True,
                        .AutoIncrementSeed = 1,
                        .ReadOnly = True
                    },
                    New DataColumn With
                    {
                        .ColumnName = "FirstName",
                        .DataType = GetType(String)
                    },
                    New DataColumn With
                    {
                        .ColumnName = "LastName",
                        .DataType = GetType(String),
                        .ReadOnly = True
                    }
                }
            )
            dt.Rows.Add(New Object() {Nothing, "Nkundiushuti", "Gaseitsiwe"})
            dt.Rows.Add(New Object() {Nothing, "Isaak", "Lundin"})
            dt.Rows.Add(New Object() {Nothing, "Maura", "Abaszada"})
            dt.Rows.Add(New Object() {Nothing, "Mary", "Jones"})
            dt.Rows.Add(New Object() {Nothing, "Karen", "Smith"})
            dt.Rows.Add(New Object() {Nothing, "Trevor", "Adams"})
            bsCustomers.DataSource = dt
            DataGridView1.DataSource = bsCustomers
        End Sub
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim dt As DataTable = CType(bsCustomers.DataSource, DataTable)
            If Not dt.Columns.Contains("Account") Then
                Dim Items As DataRow() = Nothing
                dt.Columns.Add(New DataColumn With {.ColumnName = "Account", .DataType = GetType(String)})
                Dim dtMore As DataTable = GetMoreData(dt)
                For Each row As DataRow In dt.Rows
                    Items = dtMore.Select("ID=" & row.Field(Of Int32)("ID"))
                    If Items.Count = 1 Then
                        row.SetField(Of String)("Account", Items(0).Field(Of String)("account"))
                    End If
                Next
            End If
        End Sub
        Private Function GetMoreData(ByVal dtFromDataGridView As DataTable) As DataTable
            Dim dt As New DataTable
            dt.Columns.AddRange(New DataColumn() _
                {
                    New DataColumn With
                    {
                        .ColumnName = "ID",
                        .DataType = GetType(Int32)
                    },
                    New DataColumn With
                    {
                        .ColumnName = "Account",
                        .DataType = GetType(String),
                        .ReadOnly = True
                    }
                }
            )
            dt.Rows.Add(New Object() {6, "A600"})
            dt.Rows.Add(New Object() {1, "A100"})
            dt.Rows.Add(New Object() {3, "A300"})
            dt.Rows.Add(New Object() {2, "A200"})
            dt.Rows.Add(New Object() {4, "A400"})
            Return dt
        End Function
    End Class


    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.

    • Marked as answer by Sysaide Tuesday, April 16, 2013 2:31 AM
    Monday, April 15, 2013 1:22 PM
  • Hi Kevininstructor Thanks for this. Please check my code below, I got an error "ID1 doesn't belog to the table"

    bsdata

    dt5

    //Databound to a datagrid Using cn As New OleDbConnection With {.ConnectionString = String.Format(ConnectionNoHeader, FileName)} cn.Open() Dim cmd As OleDbCommand = New OleDbCommand( <Text> SELECT Product, price, qty, userID FROM [<%= SheetName %>$] </Text>.Value, cn ) Dim dt As New DataTable dt.Load(cmd.ExecuteReader) dt.AcceptChanges() bsData.DataSource = dt DataGridView1.DataSource = bsData //DataTable that contain the data I need to add to a new column in bsdata Using cn As New OleDbConnection With {.ConnectionString = String.Format(ConnectionNoHeader3, FileName3)} cn.Open() Dim cmd As OleDbCommand = New OleDbCommand( <Text> SELECT * FROM [<%= SheetName3 %>$] where date=<%= CStr(CurrentWeek) %> </Text>.Value, cn ) Dim dt5 As New DataTable dt5.Load(cmd.ExecuteReader) Dim dtts As DataTable = CType(bsData.DataSource, DataTable) If Not dtts.Columns.Contains("Objectif") Then Dim items As DataRow() = Nothing dtts.Columns.Add(New DataColumn With {.ColumnName = "Objectif", .DataType = GetType(String)}) For Each row As DataRow In dtts.Rows items = dt5.Select("ID1" & row.Field(Of Int32)("ID1"))//ERROR , ID1 doesn't belong to the table If items.Count = 1 Then row.SetField(Of String)("objectif", items(0).Field(Of String)("Objectif")) End If Next End If








    • Edited by Sysaide Monday, April 15, 2013 3:25 PM
    Monday, April 15, 2013 3:19 PM
  • Study how I did the column add in Button1_Click and if you are still unclear let me know.

    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.

    Monday, April 15, 2013 3:49 PM
  • Hi KevinInstructor, I have tried different approachs, unfortunately it didn't work. I'm still getting error "ID1 column doesn't belong to the table" under 
     items = dtmore.Select("ID=" & row.Field(Of Int32)("ID"))

    even though, I can see the column ID under dtmore with DataSet Visualizer. Another thing, in both datatable I don't have a primary key column, if add one than I'm going too far. It easy when you add you own datacolumn to datatable, in my case the datatable is extracted from different excel files. thanks.
    • Edited by Sysaide Tuesday, April 16, 2013 1:17 AM
    Tuesday, April 16, 2013 1:16 AM
    • ID1 and ID are not the same.
    • All tables should have a primary key, this is how you find records.

    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.

    • Marked as answer by Sysaide Tuesday, April 16, 2013 2:32 AM
    Tuesday, April 16, 2013 2:23 AM
  • Finally I added a primary key ID to the Datatable the same as the first column in the databound datagrid and with your code I was able to add column and populate the data. Thanks again for your help. you are a good Instructor.
    Tuesday, April 16, 2013 2:31 AM