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.

if so what type of column is COACHING in the db? If it is Boolean, then "Y" and "N" are not direct conversions. Rather than a combo, consider a checkbox. — Ňɏssa Pøngjǣrdenlarp, Aug 22 '14 at 18:54
The field type in the database is char and it is limited to one character. I was actually thinking of using a checkbox instead. @Plutonix any idea how I could use a checkbox to accomplish this? — slister, Aug 22 '14 at 19:28
Sounds like the DB is a pseudo bool. I'd change it if I could. — Ňɏssa Pøngjǣrdenlarp, Aug 22 '14 at 19:30
I totally agree with @Plutonix. Store the value as bit. It's always easier to format a value (for display) then parse (for storage). — Bjørn-Roger Kringsjå, Aug 22 '14 at 19:35
the other problem, depending on the DB, is the chance that other characters get introduced. you could try to convert it in SQL if you cant change the DB: something like `([Coaching]='Y') AS myCoaching` for MS Access — Ňɏssa Pøngjǣrdenlarp, Aug 22 '14 at 19:43

Bjørn-Roger Kringsjå · Accepted Answer · 2014-08-22T19:08:35.927

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

edited Aug 22 '14 at 19:08

answered Aug 22 '14 at 18:59

Bjørn-Roger Kringsjå

9,849
6
36
64

Thank you so much. That has been driving me crazy all day. That worked like a charm. On a side note I was thinking about maybe using a check box instead of a combo box. Do you know how I could have it recognize Y as a True check and N for false? – slister Aug 22 '14 at 19:11
@slister I'm pretty sure I've done that before but I don't recall how. Try google `parse` (push) and `format` (pull) + `DataGridViewCell`. If no luck, you can always ask a new question;) – Bjørn-Roger Kringsjå Aug 22 '14 at 19:32

Load data into DataGridViewComboBoxColumn VB.NET

1 Answers1

Linked