How to change the DataSource for DataGridVewComboBoxColumn at runtime in C#

Question

I am working on a project that reads in a hardware configuration xml file and parses and sorts its data by protocol type and channels for further modification that is displayed in a two column gridview. Column 1 holds the channel names as a DataGridViewTextBoxColumn while column 2 is setup as a DataGridViewComboBoxColumn where the user should be able to select an item from the dropdown list that was generated from a query from a SQL database (Channel-Type).

The SQL database table itself has couple columns that are as follows: Interface Channel-Type

The "Interface" column is used as a key to distinguish the "Channel-Type" if they are RS232, RS422, RS485, and so on.

As I populate the DataGridView, I sort it out to first run through all RS422 channel listing, followed by RS232 listings and so on. However, to do this I would need to be able to change the DataSource for the ComboBoxColumn to have different queries such as for the first it would be "SELECT Channel_Type FROM Table1 WHERE Interface='RS422'" while for the second one I would need to run "SELECT Channel_Type FROM Table1 WHERE Interface='RS232'"

So in the code I have the following:

private void scanner()
{
    //...code for parser that assembles a List<> ...

    ChannelTypeColumn.DataSource = Populate("SELECT Channel_Type FROM Table1 WHERE Interface='RS422'");
    ChannelTypeColumn.ValueMember = "Channel_Type";
    ChannelTypeColumn.DisplayMember = ChannelTypeColumn.ValueMember;

    for(int x = 0; x < ChannelRS422List.Count; x++)
    {
        RowDataBuffer[0] = ChannelRS422List.channel;
        dgv.Rows.Add(RowDataBuffer);
    }

    ChannelTypeColumn.DataSource = Populate("SELECT Channel_Type FROM Table1 WHERE Interface='RS232'");

    for(int x = 0; x < ChannelRS232List.Count; x++)
    {
        RowDataBuffer[0] = ChannelRS232List.channel;
        dgv.Rows.Add(RowDataBuffer);
    }
}

private DataTable Populate(string query)
{
    SqlCommand command = new SqlCommand(query, connection);
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = command;

    DataTable table = new DataTable();
    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
    adapter.Fill(table);

    return table;
}

Unfortunately I am not sure how to change datasources correctly, since doing it with this methods only would show me a list of the items in the last datasource definition.

Any help and advise would be greatly appreciated!

Thanks!

Why don't you just create two data grids, one for each query? — user8128167
Alternatively, what about using a GROUP BY in your query to distinguish between the two types? — user8128167
I can't use GROUP BY since the Channel-Type names may have the same name for either interface and would cause me problems with further editing. In addition the listing would be very large. Having multiple data grids would be a workaround if I can't get this one to work but I am not sure how clean the GUI would look since not all hardware configuration files necessarily have multiple different interfaces in them. — Pita
I was also considering if instead of doing a DataSource I would just do an item listing where I populate an array from the query and insert it that way. Assuming that will work per cell as well but ideally I was hoping to do the DataSource method if there is a way of doing it somehow. — Pita

Pita Pita · Accepted Answer · 2014-10-30T21:47:34

After much trying, I finally found a method to do the problem. Instead of doing this by the column, I need to do it by the cell for the row I need it at.

private void scanner()
{
    //...code for parser that assembles a List<> ...

    for(int x = 0; x < ChannelRS422List.Count; x++)
    {
        RowDataBuffer[0] = ChannelRS422List.channel;
        dgv.Rows.Add(RowDataBuffer);
    }

    int rowCount = 0;
    for (int row = 0; row < dgv.Rows.Count; row++)
    {
        DataGridViewComboBoxCell cell = (DataGridViewComboBoxCell)(dgv.Rows[row].Cells[1]);
        cell.DataSource = Populate("SELECT Channel_Type FROM Table1 WHERE Interface='RS422'");
        cell.ValueMember = "Channel_Type";
        cell.DisplayMember = cell.ValueMember;
        rowCount = row+1;
    }

    for(int x = 0; x < ChannelRS232List.Count; x++)
    {
        RowDataBuffer[0] = ChannelRS232List.channel;
        dgv.Rows.Add(RowDataBuffer);
    }

    for (int row = rowCount; row < dgv.Rows.Count; row++)
    {
        DataGridViewComboBoxCell cell = (DataGridViewComboBoxCell)(dgv.Rows[row].Cells[1]);
        cell.DataSource = Populate("SELECT Channel_Type FROM Table1 WHERE Interface='RS232'");
        cell.ValueMember = "Channel_Type";
        cell.DisplayMember = cell.ValueMember;
        rowCount = row+1;
    }
}

How to change the DataSource for DataGridVewComboBoxColumn at runtime in C#

1 Answers