Fill DataGridView ComboBox Column using DataTable

Question

I want to fill DataGridView ComboBox column using MySQL query and every row should have DataGridView ComboBox item collection based on cell value of each row.

Imports MySql.Data.MySqlClient
Public Class Form3
    Sub dgvcbordercall()
        Dim conn As New MySqlConnection
        conn.ConnectionString = connserver

        conn.Open()
        da = New MySqlDataAdapter("SELECT Order_Number FROM tborder", conn)
        Dim dt As New DataTable
        da.Fill(dt)

        Dim dgvcborder As DataGridViewComboBoxColumn = DataGridView1.Columns("ordernumber")
        dgvcborder.DataSource = dt
        dgvcborder.ValueMember = "Order_Number"
        dgvcborder.DisplayMember = "Order_Number"
        conn.Close()
    End Sub
    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dgvcbordercall()
    End Sub
End Class

I have tried this but it makes all row has the same DataGridView ComboBox item collection. I want the ComboBox item collection filled based on ID Customer.

Answer 1

I found my solution instead using DataGridViewComboBoxColumn, I use DataGridViewComboBoxCell and loop through all datagrid row to add comboboxcell items, I don't know if it's the efficient way or not, but it works perfectly in my case.

MySQL_Query
SELECT * FROM tbtransaction

then use 'for each' to fill the datagridrow from datatable row

For Each drow As DataRow In dt.Rows
      Dim rowId As Integer = dgvdtldelivery.Rows.Add()
      Dim row As DataGridViewRow = dgvdtldelivery.Rows(rowId)
      row.Cells("idcustomer").Value = drow.Item("ID_Customer")
      row.Cells("customer").Value = drow.Item("Customer")

      Dim comboBoxCell As New DataGridViewComboBoxCell
      dgvdtldelivery("ordernumber", rowId) = comboBoxCell

      MySQL_Query
      SELECT Order_Number FROM tborder WHERE ID_Customer = row.Cells("idcustomer").Value

      Do While dr.Read
         comboBoxCell.Items.Add(dr.Item("Order_Number"))
      Loop
End For

Answer 2

If you bind a list to the column then every cell in that column will also be bound to that list and so will the editing control when ever you edit a cell in that column. If you want the list in each cell to be filtered then you have to bind each editing control separately. My advice is to bind the full list to the column, so any value in that list will be valid in any cell. You can then filter on demand in the editing control, which is created only when you edit a cell.

Coincidentally, I just created an example of this for someone a few hours ago, so I can share it here. It won't relate directly to your data but it demonstrates the principle that you apply to any data.

I suggest that you create a new WinForms project. You can then paste the following code into the designer code file of the default Form1, to save you having to build the form yourself:

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container()
        Me.DataGridView1 = New System.Windows.Forms.DataGridView()
        Me.gridBindingSource = New System.Windows.Forms.BindingSource(Me.components)
        Me.parentBindingSource = New System.Windows.Forms.BindingSource(Me.components)
        Me.childBindingSource = New System.Windows.Forms.BindingSource(Me.components)
        Me.filteredChildBindingSource = New System.Windows.Forms.BindingSource(Me.components)
        Me.idColumn = New System.Windows.Forms.DataGridViewTextBoxColumn()
        Me.parentColumn = New System.Windows.Forms.DataGridViewComboBoxColumn()
        Me.childColumn = New System.Windows.Forms.DataGridViewComboBoxColumn()
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.gridBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.parentBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.childBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.filteredChildBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGridView1
        '
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.idColumn, Me.parentColumn, Me.childColumn})
        Me.DataGridView1.Location = New System.Drawing.Point(12, 12)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.Size = New System.Drawing.Size(776, 426)
        Me.DataGridView1.TabIndex = 0
        '
        'idColumn
        '
        Me.idColumn.DataPropertyName = "GrandparentId"
        Me.idColumn.HeaderText = "ID"
        Me.idColumn.Name = "idColumn"
        '
        'parentColumn
        '
        Me.parentColumn.DataPropertyName = "ParentId"
        Me.parentColumn.HeaderText = "Parent"
        Me.parentColumn.Name = "parentColumn"
        '
        'childColumn
        '
        Me.childColumn.DataPropertyName = "ChildId"
        Me.childColumn.HeaderText = "Child"
        Me.childColumn.Name = "childColumn"
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(800, 450)
        Me.Controls.Add(Me.DataGridView1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.gridBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.parentBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.childBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.filteredChildBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

    Friend WithEvents DataGridView1 As DataGridView
    Friend WithEvents gridBindingSource As BindingSource
    Friend WithEvents parentBindingSource As BindingSource
    Friend WithEvents childBindingSource As BindingSource
    Friend WithEvents filteredChildBindingSource As BindingSource
    Friend WithEvents idColumn As DataGridViewTextBoxColumn
    Friend WithEvents parentColumn As DataGridViewComboBoxColumn
    Friend WithEvents childColumn As DataGridViewComboBoxColumn
End Class

You can then paste the following code into the user code file for that same form:

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim data = GetData()

        BindData(data)
    End Sub

    Private Function GetData() As DataSet
        Dim data As New DataSet
        Dim grandparentTable = data.Tables.Add("Grandparent")

        With grandparentTable.Columns
            .Add("GrandparentId", GetType(Integer)).AutoIncrement = True
            .Add("ParentId", GetType(Integer))
            .Add("ChildId", GetType(Integer))
        End With

        Dim parentTable = data.Tables.Add("Parent")

        With parentTable.Columns
            .Add("ParentId", GetType(Integer))
            .Add("ParentName", GetType(String))
        End With

        With parentTable.Rows
            .Add(1, "Parent 1")
            .Add(2, "Parent 2")
            .Add(3, "Parent 3")
        End With

        Dim childTable = data.Tables.Add("Child")

        With childTable.Columns
            .Add("ChildId", GetType(Integer))
            .Add("ParentId", GetType(Integer))
            .Add("ChildName", GetType(String))
        End With

        With childTable.Rows
            .Add(1, 1, "Child 1.1")
            .Add(2, 1, "Child 1.2")
            .Add(3, 1, "Child 1.3")
            .Add(4, 2, "Child 2.1")
            .Add(5, 2, "Child 2.2")
            .Add(6, 2, "Child 2.3")
            .Add(7, 3, "Child 3.1")
            .Add(8, 3, "Child 3.2")
            .Add(9, 3, "Child 3.3")
        End With

        Return data
    End Function

    Private Sub BindData(data As DataSet)
        filteredChildBindingSource.DataSource = New DataView(data.Tables("Child"))
        childBindingSource.DataSource = data.Tables("Child")

        With childColumn
            .DisplayMember = "ChildName"
            .ValueMember = "ChildId"
            .DataSource = childBindingSource
        End With

        parentBindingSource.DataSource = data.Tables("Parent")

        With parentColumn
            .DisplayMember = "ParentName"
            .ValueMember = "ParentId"
            .DataSource = parentBindingSource
        End With

        gridBindingSource.DataSource = data.Tables("Grandparent")
        DataGridView1.DataSource = gridBindingSource
    End Sub

    Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
        If e.ColumnIndex = 1 AndAlso e.RowIndex >= 0 Then
            DataGridView1.Rows(e.RowIndex).Cells(2).Value = DBNull.Value
        End If
    End Sub

    Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        Dim cellAddress = DataGridView1.CurrentCellAddress
        Dim columnIndex = cellAddress.X

        If columnIndex = 2 Then
            Dim parentIdValue = DataGridView1.CurrentRow.Cells(1).Value

            If parentIdValue IsNot DBNull.Value Then
                filteredChildBindingSource.Filter = "ParentId = " & CInt(parentIdValue)
                With DirectCast(e.Control, ComboBox)
                    .DisplayMember = "ChildName"
                    .ValueMember = "ChildId"
                    .DataSource = filteredChildBindingSource
                End With
            End If
        End If
    End Sub

End Class

If you run the project, you can then select Parent values in each row and you'll see a filtered list of Child values. As I said, the full Child table is bound to the Child column but there is an extra BindingSource bound to a DataView created for that table. That is filtered and bound on demand to the editing control for the Child column.