vb.netdatagridviewdatagridviewcombobox

Populate combobox in datagridview in VbNet From other table


I have a DataGridView with 3 TextBox columns and 1 ComboBox column. I have tried without result to populate the ComboBox column with data from other table, a suggestion will be apreciated.

This is the code

Dim conn As OleDb.OleDbConnection = DBConnect.getDbConnection()
dgv.Rows.Clear()

Try

    Dim selectSql = "select idf,cb,rgso from libroacquisti " 

    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(selectSql, conn)
    Dim ds As DataSet = New DataSet
    da.Fill(ds, "Libroacquisti")
    Dim dt As DataTable = ds.Tables("libroacquisti")
    Dim row As DataRow

    If dt.Rows.Count > 0 Then
        For Each row In dt.Rows
            Me.dgv.Rows.Add(row("idf"),row("cb"), row("rgso"),THIS IS COMBOBOX COLUMN AND I SHOULD WANT TO POPULATE IT FROM ANOTHER TABLE BY SQL QUERY )
        Next row
    End If

However, it must be considered that in each row of the DataGridView, in the related ComboBox, the data are always the same and are extracted from the same table.


Solution

  • In order to populate a combo box column from table data, you will need to set up a separate data set and binding source for the combo box to pull data from.

    populate your dataset with your 'sub' table that will be referenced by the data from your data grid view. For a combo box, you will only need two columns. One for the id that will be referenced by the data in your DataGridView's ComboBoxColumn and one for the value that will be displayed.

    Drop a new BindingSource control into your form designer

    Set the DataSource property to the DataSet and set the DataMember property to the Table Name within the DataSet

    Then set the ComboBoxColumn's DataSource property to your BindingSource and set its DisplayMember and ValueMember properties.

    Now your DataGridView should have the appropriate value selected for each row and the combo box list populated showing the values populated in the data set that you set as the DisplayMember:

    Dim selectSql = "select ValueColumn,DisplayColumn " 
    
    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(selectSql, conn)
    Dim ds As DataSet = New DataSet
    da.Fill(ds, "ComboValues")
    
    BSComboValues.DataSource = ds
    BSComboValues.DataMember = "ComboValues"
    
    Dim ComboColumn As DataGridViewComboBoxColumn = dgv.Columns("ComboColumnName")
    ComboColumn.DataSource = BSComboValues
    ComboColumn.ValueMember = "ValueColumn"
    ComboColumn.DisplayMember = "DisplayColumn"