Load data into DataGridViewComboBoxColumn VB.NET

Question

Hi I have been banging my head against the wall all day trying to figure this out.

I have a DataGridView that is displaying the results of an SQL Query

The query returns 3 fields: GROUPNUM, GROUPNAME, COACHING

The group fields just have strings in them and they are displaying fine, but the COACHING field is a single character field that will either have a Y or an N in it. For that column I want to have a combobox with Y or N as the items. Here is what I have so far.

dtGroups is a data table that was filled with an SQL data adapter.

            dvGroups = dtGroups.DefaultView

            'Set up datagrid view

            With dgvToPopulate

                .Columns.Clear()

                .AutoGenerateColumns = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

                .DataSource = dtGroups

                With .Columns

                    Dim groupNumColumn, groupNameColumn As New DataGridViewTextBoxColumn

                    With groupNumColumn

                        .DataPropertyName = "GROUPNUM"
                        .HeaderText = "Group Number"
                        .ReadOnly = True

                    End With

                    With groupNameColumn

                        .DataPropertyName = "GROUPNAME"
                        .HeaderText = "Group Name"
                        .ReadOnly = True

                    End With

                    Dim coachingColumn As New DataGridViewComboBoxColumn

                    With coachingColumn

                        .HeaderText = "RN Coaching"

                        .Items.AddRange({"Y", "N"})

                        .DataPropertyName = "COACHING"

                        .DisplayMember = .DataPropertyName
                        .ValueMember = .DisplayMember


                    End With

                    .AddRange({groupNumColumn, groupNameColumn, coachingColumn})

                End With

            End With

The grid is set up the way I want and it is displaying the all the data except all the comboBoxes have nothing selected. How do I get the comboBox to have a Y or N in them based on what was stored in that field.

Any help with this would be appreciated.

Answer 1

Both DisplayMember and ValueMember are linked to the DataSource property. Whenever you change one of these properties, the data connection is reset. In other words: the items-collection is cleared.

Take your example for instance. The String class do not have a member named COACHING. It has properties like Length and Chars etc. So you cannot create a binding. Also, the items-collection accepts all kinds of objects.

You need to create and bind a custom data source. Here's an example using a DataTable:

Dim ynTable As New DataTable()

With ynTable
    .Columns.Add("Value", GetType(String))
    .Rows.Add("Y")
    .Rows.Add("N")
    .AcceptChanges()
End With

With coachingColumn
    .HeaderText = "RN Coaching"
    .DataSource = ynTable
    .DataPropertyName = "COACHING"
    .DisplayMember = "Value"
    .ValueMember = "Value"
End With