vb.netdatagridviewdatagridviewcombobox

Fill DataGridView ComboBox Column using DataTable


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.


Solution

  • 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