none
How do I populate certain rows of data from a DataGridView and populate another DataGridView

    Question

  • I already have the first DataGridView populating fine and I have a ComboBox that populates from the first DataGrdiView from one of the columns. I need to fill the second DataGrdiView with any rows that have the ComboBox selected item.
    Friday, March 17, 2017 2:54 PM

Answers

  • Here is a simple example that loads MS-Access data via OleDb. Note I could of hidden the Identifier field but left it there as it does not affect the copy.

    Class for database operations

    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    
        Public Property CustomersDataTable As DataTable
        Public Property ContactTitles As List(Of String)
        Public Sub New()
            CustomersDataTable = New DataTable
        End Sub
        Public Sub LoadCustomerData()
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                ContactTitle 
                            FROM Customers
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
    
                End Using
            End Using
        End Sub
        Public Sub LoadContactTitles()
            ContactTitles = New List(Of String)
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT DISTINCT ContactTitle
                            FROM Customers;
                        </SQL>.Value
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
                    While reader.Read
                        ContactTitles.Add(reader.GetString(0))
                    End While
                End Using
            End Using
        End Sub
    
    End Class

    Form code

    Public Class DataGridViewToDataGridView
        Private Sub DataGridViewToDataGridView_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New DatabaseOperations
            ops.LoadCustomerData()
    
            Dim dt As DataTable = ops.CustomersDataTable
            Dim dtClone As DataTable = dt.Clone
    
            DataGridView1.DataSource = dt
            DataGridView2.DataSource = dtClone
    
            ops.LoadContactTitles()
            ComboBox1.DataSource = ops.ContactTitles
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim results As IEnumerable(Of DataRow) = CType(DataGridView1.DataSource, DataTable) _
                .AsEnumerable _
                .Where(Function(row) row.Field(Of String)("ContactTitle") = ComboBox1.Text)
    
            Dim dt As DataTable = CType(DataGridView2.DataSource, DataTable)
            For Each row As DataRow In results
                dt.ImportRow(row)
            Next
    
        End Sub
    End Class

    Before operation

    After selecting Sales Agent and pressing Button1


    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 VBShaper Monday, March 20, 2017 2:09 AM
    Saturday, March 18, 2017 1:20 PM
    Moderator

All replies

  • Where does the data for the rows come from? A database query or programmatic values?

    Essentially you will just take the selected value from the ComboBox and run a new query with that value as part of the "WHERE" clause in the query, or you'll execute code to create rows for the second datagridview based on the combobox selection.


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

    Friday, March 17, 2017 3:06 PM
    Moderator
  • wise words reed. 

    VBShaper he is right we need a bit more specifics such as are these comboboxes connected to a database on your laptop? 

    If so then give us more information then we can get an idea of how to solve your problem. 


    WRA

    Friday, March 17, 2017 3:09 PM
  • Your idea sounds awfully similar to what I have done in one of my projects where someone created a multicolumncombobox (somebody help made it for me) so It can display the name of something from my datastorage (access database) and how much does it cost.

    WRA

    Friday, March 17, 2017 3:10 PM
  • Where does the data for the rows come from? A database query or programmatic values?

    Essentially you will just take the selected value from the ComboBox and run a new query with that value as part of the "WHERE" clause in the query, or you'll execute code to create rows for the second datagridview based on the combobox selection.


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

    The data comes from an Access database. I m using a query statement to extract the data.

    Access.ExecQuery("Select A.NIIN, B.AMC, B.AMSC, C.Company, D.NHA_NIIN, D.NHA_Item_Name, D.NHA_FSC, E.Date, E.Price, E.Qty, E.CAGE, E.Vendor_Name " &
                                        "FROM (((SegmentA As A INNER JOIN (Select DISTINCT MOERuleSegB.NIIN, MOERuleSegB.AMC, MOERuleSegB.AMSC FROM MOERuleSegB " &
                                        "WHERE MOERuleSegB.AMC <> '' AND MOERuleSegB.AMSC <> '')  AS B ON A.NIIN = B.NIIN)" &
                                        "INNER JOIN (SELECT MCRLMasterCrossReferenceList.NIIN, MCRLMasterCrossReferenceList.Company " &
                                        "FROM MCRLMasterCrossReferenceList WHERE MCRLMasterCrossReferenceList.Obsolescence='Design/Source Control Reference Item.')  AS C ON A.NIIN = C.NIIN) " &
                                        "LEFT JOIN (SELECT DISTINCT NextHigherAssembly.NIIN, NextHigherAssembly.NHA_NIIN, NextHigherAssembly.NHA_Item_Name, NextHigherAssembly.NHA_FSC FROM NextHigherAssembly)  AS D ON A.NIIN = D.NIIN) " &
                                        "LEFT JOIN (SELECT DISTINCT ProcurementHistory.NIIN, ProcurementHistory.Date, ProcurementHistory.Price, ProcurementHistory.Qty, ProcurementHistory.CAGE, ProcurementHistory.Vendor_Name FROM ProcurementHistory " &
                                        "WHERE (((CDate(([ProcurementHistory].[Date])))>#12/31/2013#)))  AS E ON A.NIIN = E.NIIN ")

    Saturday, March 18, 2017 11:58 AM
  • Here is a simple example that loads MS-Access data via OleDb. Note I could of hidden the Identifier field but left it there as it does not affect the copy.

    Class for database operations

    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    
        Public Property CustomersDataTable As DataTable
        Public Property ContactTitles As List(Of String)
        Public Sub New()
            CustomersDataTable = New DataTable
        End Sub
        Public Sub LoadCustomerData()
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                ContactTitle 
                            FROM Customers
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
    
                End Using
            End Using
        End Sub
        Public Sub LoadContactTitles()
            ContactTitles = New List(Of String)
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT DISTINCT ContactTitle
                            FROM Customers;
                        </SQL>.Value
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
                    While reader.Read
                        ContactTitles.Add(reader.GetString(0))
                    End While
                End Using
            End Using
        End Sub
    
    End Class

    Form code

    Public Class DataGridViewToDataGridView
        Private Sub DataGridViewToDataGridView_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New DatabaseOperations
            ops.LoadCustomerData()
    
            Dim dt As DataTable = ops.CustomersDataTable
            Dim dtClone As DataTable = dt.Clone
    
            DataGridView1.DataSource = dt
            DataGridView2.DataSource = dtClone
    
            ops.LoadContactTitles()
            ComboBox1.DataSource = ops.ContactTitles
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim results As IEnumerable(Of DataRow) = CType(DataGridView1.DataSource, DataTable) _
                .AsEnumerable _
                .Where(Function(row) row.Field(Of String)("ContactTitle") = ComboBox1.Text)
    
            Dim dt As DataTable = CType(DataGridView2.DataSource, DataTable)
            For Each row As DataRow In results
                dt.ImportRow(row)
            Next
    
        End Sub
    End Class

    Before operation

    After selecting Sales Agent and pressing Button1


    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 VBShaper Monday, March 20, 2017 2:09 AM
    Saturday, March 18, 2017 1:20 PM
    Moderator
  • Here is a simple example that loads MS-Access data via OleDb. Note I could of hidden the Identifier field but left it there as it does not affect the copy.

    Class for database operations

    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    
        Public Property CustomersDataTable As DataTable
        Public Property ContactTitles As List(Of String)
        Public Sub New()
            CustomersDataTable = New DataTable
        End Sub
        Public Sub LoadCustomerData()
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                ContactTitle 
                            FROM Customers
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
    
                End Using
            End Using
        End Sub
        Public Sub LoadContactTitles()
            ContactTitles = New List(Of String)
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT DISTINCT ContactTitle
                            FROM Customers;
                        </SQL>.Value
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
                    While reader.Read
                        ContactTitles.Add(reader.GetString(0))
                    End While
                End Using
            End Using
        End Sub
    
    End Class

    Form code

    Public Class DataGridViewToDataGridView
        Private Sub DataGridViewToDataGridView_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New DatabaseOperations
            ops.LoadCustomerData()
    
            Dim dt As DataTable = ops.CustomersDataTable
            Dim dtClone As DataTable = dt.Clone
    
            DataGridView1.DataSource = dt
            DataGridView2.DataSource = dtClone
    
            ops.LoadContactTitles()
            ComboBox1.DataSource = ops.ContactTitles
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim results As IEnumerable(Of DataRow) = CType(DataGridView1.DataSource, DataTable) _
                .AsEnumerable _
                .Where(Function(row) row.Field(Of String)("ContactTitle") = ComboBox1.Text)
    
            Dim dt As DataTable = CType(DataGridView2.DataSource, DataTable)
            For Each row As DataRow In results
                dt.ImportRow(row)
            Next
    
        End Sub
    End Class

    Before operation

    After selecting Sales Agent and pressing Button1


    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

    This is what I have I am not certain how I would implement your example.

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Public Class Form1
    
    
    
        Private Access As New DBControl
    
    
    
        Private Function NotEmpty(text As String) As Boolean
            Return Not String.IsNullOrEmpty(text)
        End Function
    
        Private Sub Form1_Shown(sender As Object, e As System.EventArgs) Handles Me.Shown
    
            ' RUN QUERY
    
                   Access.ExecQuery("Select A.NIIN, B.AMC, B.AMSC, C.Company, D.NHA_NIIN, D.NHA_Item_Name, D.NHA_FSC, E.Date, E.Price, E.Qty, E.CAGE, E.Vendor_Name " &
                                        "FROM (((SegmentA As A INNER JOIN (Select DISTINCT MOERuleSegB.NIIN, MOERuleSegB.AMC, MOERuleSegB.AMSC FROM MOERuleSegB " &
                                        "WHERE MOERuleSegB.AMC <> '' AND MOERuleSegB.AMSC <> '')  AS B ON A.NIIN = B.NIIN)" &
                                        "INNER JOIN (SELECT MCRLMasterCrossReferenceList.NIIN, MCRLMasterCrossReferenceList.Company " &
                                        "FROM MCRLMasterCrossReferenceList WHERE MCRLMasterCrossReferenceList.Obsolescence='Design/Source Control Reference Item.')  AS C ON A.NIIN = C.NIIN) " &
                                        "LEFT JOIN (SELECT DISTINCT NextHigherAssembly.NIIN, NextHigherAssembly.NHA_NIIN, NextHigherAssembly.NHA_Item_Name, NextHigherAssembly.NHA_FSC FROM NextHigherAssembly)  AS D ON A.NIIN = D.NIIN) " &
                                        "LEFT JOIN (SELECT DISTINCT ProcurementHistory.NIIN, ProcurementHistory.Date, ProcurementHistory.Price, ProcurementHistory.Qty, ProcurementHistory.CAGE, ProcurementHistory.Vendor_Name FROM ProcurementHistory " &
                                        "WHERE (((CDate(([ProcurementHistory].[Date])))>#12/31/2013#)))  AS E ON A.NIIN = E.NIIN ")
            If NotEmpty(Access.Exception) Then MsgBox(Access.Exception)
    
            ' FILL DATAGRID
            dgvData.DataSource = Access.DBDT
    
            ' FILL COMBOBOX
            For Each R As DataRow In Access.DBDT.Rows
                cbxUsers.Items.Add(R("NIIN"))
            Next
    
            ' DISPLAY FIRSt NAME FOUND
            If Access.RecordCount > 0 Then cbxUsers.SelectedIndex = 0
            'Call Fdata()
            Update()
    
            Dim offset As Integer = dgvData.HorizontalScrollingOffset
            dgvData.HorizontalScrollingOffset = 2000
            Me.Width = Me.Width + offset
            dgvData.Width += offset
        End Sub
    
    End Class


    • Edited by VBShaper Saturday, March 18, 2017 3:27 PM
    Saturday, March 18, 2017 3:11 PM
  • The code I am showing loads the first datagridview with all the data I need from the tables. The Combobox uses the NIIN column from SegmentA table. I have what I need. I just need to have the second Datagridview load just like your example. Display only the rows of data that have the selected item from the combobox.
    • Edited by VBShaper Saturday, March 18, 2017 3:17 PM
    Saturday, March 18, 2017 3:14 PM
  • How would you go about using my code?

    You would not use Office automation, instead use the framework I provided. Using Office Automation is complete overkill. What I have provided permits you to do what you want to do.

    On a side note, this is the first time in nearly ten years of me helping others in this forum someone using Office automation to populate a DataGridView.


    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

    Saturday, March 18, 2017 3:27 PM
    Moderator
  • The code I am showing loads the first datagridview with all the data I need from the tables. The Combobox uses the NIIN column from SegmentA table. I have what I need. I just need to have the second Datagridview load just like your example. Display only the rows of data that have the selected item from the combobox.

    All you need to do is execute a second query and fill the DataTable which the second Datagridview is bound to.  Create a query which takes the NIIN parameter.  Assign the parameter value based on the combobox selection and then execute the query (FillBy method on the appropriate TableAdapter).

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

    Saturday, March 18, 2017 3:28 PM
    Moderator
  • How would you go about using my code?

    You would not use Office automation, instead use the framework I provided. Using Office Automation is complete overkill. What I have provided permits you to do what you want to do.

    On a side note, this is the first time in nearly ten years of me helping others in this forum someone using Office automation to populate a DataGridView.


    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

    Office interop is for something else that I was working on with excel, so please ignore.

    Saturday, March 18, 2017 3:38 PM
  • The code I am showing loads the first datagridview with all the data I need from the tables. The Combobox uses the NIIN column from SegmentA table. I have what I need. I just need to have the second Datagridview load just like your example. Display only the rows of data that have the selected item from the combobox.


    All you need to do is execute a second query and fill the DataTable which the second Datagridview is bound to.  Create a query which takes the NIIN parameter.  Assign the parameter value based on the combobox selection and then execute the query (FillBy method on the appropriate TableAdapter).

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


    I am not certain how to do that. Can you please give me an example.
    Saturday, March 18, 2017 3:40 PM
  • Here is a simple example that loads MS-Access data via OleDb. Note I could of hidden the Identifier field but left it there as it does not affect the copy.

    Class for database operations

    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    
        Public Property CustomersDataTable As DataTable
        Public Property ContactTitles As List(Of String)
        Public Sub New()
            CustomersDataTable = New DataTable
        End Sub
        Public Sub LoadCustomerData()
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                ContactTitle 
                            FROM Customers
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
    
                End Using
            End Using
        End Sub
        Public Sub LoadContactTitles()
            ContactTitles = New List(Of String)
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT DISTINCT ContactTitle
                            FROM Customers;
                        </SQL>.Value
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
                    While reader.Read
                        ContactTitles.Add(reader.GetString(0))
                    End While
                End Using
            End Using
        End Sub
    
    End Class

    Form code

    Public Class DataGridViewToDataGridView
        Private Sub DataGridViewToDataGridView_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New DatabaseOperations
            ops.LoadCustomerData()
    
            Dim dt As DataTable = ops.CustomersDataTable
            Dim dtClone As DataTable = dt.Clone
    
            DataGridView1.DataSource = dt
            DataGridView2.DataSource = dtClone
    
            ops.LoadContactTitles()
            ComboBox1.DataSource = ops.ContactTitles
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim results As IEnumerable(Of DataRow) = CType(DataGridView1.DataSource, DataTable) _
                .AsEnumerable _
                .Where(Function(row) row.Field(Of String)("ContactTitle") = ComboBox1.Text)
    
            Dim dt As DataTable = CType(DataGridView2.DataSource, DataTable)
            For Each row As DataRow In results
                dt.ImportRow(row)
            Next
    
        End Sub
    End Class

    Before operation

    After selecting Sales Agent and pressing Button1


    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

    Karen, after a little manipulation your code worked perfectly. Thank you.
    Monday, March 20, 2017 12:17 PM