How to populate a DataGridViewCombobBoxColumn with values

Question

I am using a DataGridView in a C# WinForms application and want to let the user choose between a set of values that will be the same in each cell. I suppose there needs to be a way to populate all the cells with a list of values.

Using the GUI designer, I have added COL_BUSINESS_INDUSTRY, which is a DataGridViewComboBoxColumn to the DataGridView. In the constructor, I want to populate the ComboBoxes with a list of values, for which I am using the following code:

COL_BUSINESS_INDUSTRY.DataSource = LoadIndustries();
COL_BUSINESS_INDUSTRY.DataPropertyName = "industryName";
COL_BUSINESS_INDUSTRY.DisplayMember = COL_BUSINESS_INDUSTRY.DataPropertyName;
COL_BUSINESS_INDUSTRY.ValueMember = COL_BUSINESS_INDUSTRY.DataPropertyName;

LoadIndustries() will return a DataTable that has two fiels: id and industryName

The problem is that when I execute this, the ComboBoxes contain no values.

I have also tried having LoadIndustries() return a List<string>, without setting the DataPropertyName, but that didn't work either.

I also tried doing this, with LoadIndustries() returning a List<string>, but still no luck:

COL_BUSINESS_INDUSTRY.Items.AddRange();

Obviously I'm doing something wrong but it's been over 3 hours of looking online and I have no idea. Why are my ComboBoxes not displaying the values?

EDIT: Following TaW's suggestion, I've made the following changes, which still don't work:

Added the following class to contain the values:

class IndustryObj
{
    public string theString { get; set; }
    public string theId { get; set; }
    public IndustryObj(string id, string s) { theId = id; theString = s; }
    public override string ToString() { return theString; }
}

I am loading the industries in the following method. I can guarantee that loading the values in the objects is working fine:

private static List<IndustryObj> LoadIndustries()
{
    var industries = new List<IndustryObj>();

    const string sql = "SELECT id, name FROM Industry ORDER BY name";
    using (var sqlQuery = new CustomSqlQuery(MyDatabases.Telemarketing, sql))
    {
        foreach (var record in sqlQuery)
        {
            industries.Add(new IndustryObj(record[0].ToString(), record[1].ToString()));
        }
    }

    return industries;
}

This is the method where I am populating the DataGridView. All values are loading fine, except for the ones in COL_BUSINESS_INDUSTRY:

private void LoadBusinesses()
{
    COL_BUSINESS_INDUSTRY.DataSource = LoadIndustries();
    COL_BUSINESS_INDUSTRY.DataPropertyName = "theString";
    COL_BUSINESS_INDUSTRY.DisplayMember = COL_BUSINESS_INDUSTRY.DataPropertyName;
    COL_BUSINESS_INDUSTRY.ValueMember = "theId";

    const string sql = "SELECT id, company, contact, address, phone, email, status, industry, callbackDate, callbackTime, createdBy, lastUpdatedBy, lockedUntil FROM Business ORDER BY company";
    using (var sqlQuery = new CustomSqlQuery(MyDatabases.Telemarketing, sql))
    {
        var columns = new DataGridViewColumn[]
            {
                COL_BUSINESS_COMPANY, 
                COL_BUSINESS_CONTACT, 
                COL_BUSINESS_ADDRESS, 
                COL_BUSINESS_PHONE, 
                COL_BUSINESS_EMAIL, 
                COL_BUSINESS_STATUS, 
                COL_BUSINESS_INDUSTRY,
                COL_BUSINESS_CALLBACKDATE, 
                COL_BUSINESS_CALLBACKTIME, 
                COL_BUSINESS_CREATEDBY, 
                COL_BUSINESS_LASTUPDATEDBY, 
                COL_BUSINESS_LOCKEDUNTIL
            };

        foreach (var record in sqlQuery)
        {
            dgv_Businesses.Rows.Add(CustomDGVRow.InitializeFromDataReader(true, record, columns));
        }
    }
}

EDIT 2: Following TaW's second suggestion, I've done the following. This did not help either:

private List<IndustryObj> industryObjects;
private void LoadBusinesses()
{
    industryObjects = LoadIndustries();
    COL_BUSINESS_INDUSTRY.DataSource = industryObjects;
    COL_BUSINESS_INDUSTRY.DataPropertyName = "theString";
    COL_BUSINESS_INDUSTRY.DisplayMember = COL_BUSINESS_INDUSTRY.DataPropertyName;
    COL_BUSINESS_INDUSTRY.ValueMember = "theId";
...

EDIT 3: Luminous actually got it, but that introduces a number of other problems. The loading method now looks like this:

private void LoadBusinesses()
{
    // Load the records.
    const string sql =
        "SELECT id, company, contact, address, phone, email, status, industry, callbackDate, callbackTime, createdBy, lastUpdatedBy, lockedUntil FROM Business ORDER BY company";
    using (var sqlQuery = new CustomSqlQuery(MyDatabases.Telemarketing, sql))
    {
        var columns = new DataGridViewColumn[]
            {
                COL_BUSINESS_COMPANY, 
                COL_BUSINESS_CONTACT, 
                COL_BUSINESS_ADDRESS, 
                COL_BUSINESS_PHONE, 
                COL_BUSINESS_EMAIL, 
                COL_BUSINESS_STATUS, 
                COL_BUSINESS_INDUSTRY,
                COL_BUSINESS_CALLBACKDATE, 
                COL_BUSINESS_CALLBACKTIME, 
                COL_BUSINESS_CREATEDBY, 
                COL_BUSINESS_LASTUPDATEDBY, 
                COL_BUSINESS_LOCKEDUNTIL
            };

        foreach (var record in sqlQuery)
        {
            dgv_Businesses.Rows.Add(CustomDGVRow.InitializeFromDataReader(true, record, columns));
        }
    }

    // Load the industries and bind the industry DataSource to the industry ComboBoxColumn.
    COL_BUSINESS_INDUSTRY.DataSource = LoadIndustries();
    COL_BUSINESS_INDUSTRY.DataPropertyName = "theString";
    COL_BUSINESS_INDUSTRY.DisplayMember = COL_BUSINESS_INDUSTRY.DataPropertyName;
    COL_BUSINESS_INDUSTRY.ValueMember = "theId";

    // Select the correct industries in the industry column.
    foreach (var rawRow in dgv_Businesses.Rows)
    {
        var row = rawRow as CustomDGVRow;
        if (row == null) continue;

        foreach (var item in ((CustomDGVComboBoxCell) row.Cells[COL_BUSINESS_INDUSTRY.Index]).Items)
        {
            var industry = item as IndustryObj;
            if (industry == null) continue;

            if (row.Cells[COL_BUSINESS_INDUSTRY.Index].Value != null && industry.theId == row.Cells[COL_BUSINESS_INDUSTRY.Index].Value.ToString())
            {
                row.Cells[COL_BUSINESS_INDUSTRY.Index].Value = industry;
            }
        }
    }
}

As you can see, I also need to select one value from the list of items, based on its id in the database. Because I only bind the datasource after loading the items (as per Luminous's suggestion), I need to do another pass through the rows and set the correct industry value for each row. However, since I have a huge number of rows, this is extremely expensive. So in order to award the bounty, I need an answer to two more questions:

1. How can I avoid doing another pass to set the correct values?
2. Why do I need to first load the rows and only then bind the datasource? This seems rather unintuitive and it causes my problem. What if the requirements ask for more data to be loaded at some point after the initial load? Would I then need to do the binding again?

Answer 1

The first thing you need to explain is why you have your column set to be a checkboxcolumn and not a comboboxcolumn? That may be your whole problem.

Now, by your code, you have a list already filled with your possible industries. How are you going to databind to any of your tuples(rows) if you don't have any rows to begin with? You should be databinding all of the cells in the column after you fill your dgv with your query result.

If that's not the issue I made a simple example of databinding a List<String> to a cell.

Here's an example of putting a List into a datagridviewcombobox:

    private List<String> theList = new List<String>();
    public Form1()
    {
        InitializeComponent();
        theList.Add("is");
        theList.Add("this");
        theList.Add("working");
        theList.Add("yet?");
        DataGridViewComboBoxCell dropDown = (DataGridViewComboBoxCell) dgv.Rows[0].Cells[0];
        dropDown.DataSource = theList;

    }

    private void aButton_Click(object sender, EventArgs e)
    {
        theList.Clear();
        theList.Add("I");
        theList.Add("Knew");
        theList.Add("this");
        theList.Add("would");
        theList.Add("work!!!");
    }

---

How can I avoid doing another pass to set the correct values?

You can't (maybe). The datagridview is going to load one result at a time. Unless you are returning multiple values for that one cell, you are going to have to do another pass. The dgv is expecting to fill in the row with the data its given for that particular row. What you can try is not recognizing that column exists in your query (just remove it from the query) and that may cause it to pass over that column and not assign it a value. If that succeeds what you have to do next is make that cell automatically be those values when the row is created. You will know this is working if you were to just add rows and those cells were immediately populated even though there's no other data in any of the cells.

---

Why do I need to first load the rows and only then bind the datasource? This seems rather unintuitive and it causes my problem. What if the requirements ask for more data to be loaded at some point after the initial load? Would I then need to do the binding again?

You have to have an object to databind to. Databinding to the column won't make you databind to the cells in that column. It wouldn't even databind to the column header. You have to databind to the cells themselves. Once you databind, you can change that list as much as you want and the cells which are databinded to your list will change as well. Once you understand this concept databinding will be a lot easier to implement in the future.

---

Looking at your code again I suggest you create your columns before you fill your table with data. That way your dgv doesn't skip the creation of the column when you insert your results, but skips the data allotment for that column. In your foreach loop create a row, assign the data, databind the cell to your List<String> and add the row to your datagridview. Once that's all working go grab yourself a drink and pat yourself on the back for a job well done.

Edit

Instead of doing what I previously said, try this. It doesn't even involve databinding a list. If you know what your string values are going to be for your drop down box at design time you can go to Columns>your_column>Items and enter the strings there. If you don't, that's ok too. Whenever you have your list of strings you can add them to the column's internal list like so:

    foo DataGridViewComboBoxColumn = myDGV.columns("YourColumn")
    foo.Items.Add("blarggg")

If you need to change the list at any time, all rows will be updated upon making said change.

Answer 2

You can do all this without actually writing a line of code using the windows forms designer.

From the Data menu, add a new data source.

Select Database and click next

Select Dataset and click next

Enter the connection string details

Select the tables you want to include in your dataset (both the business and the industry tables)

Finish the wizard

From the data sources pane, drag the businesses table onto your form. This will generate a data grid view with all the columns in, add a binding source and table adapter to populate it.

Click on the little arrow at the top right of the data grid view and select edit columns

Select the industry column, and change the column type to DataGridViewComboBox Column

Open the Data source drop down, and select the industries table from your newly created dataset

Set the display member to the name column, and the Value Member to the primary key column.

You can then view the forms designer generated code to see how it all fits together. Its not the prettiest code, but its code you didn't have to write.

The interesting parts are the data sources, binding sources and table adapters that put it all together.

With a little bit of extra work on the data set, you can also save back to the database.

Heres the result of my quick attempt at this:

using System;
using System.Windows.Forms;

namespace WindowsFormsBindingDemo
{
public partial class BusinessesForm : Form
{
    /// <summary>
    /// Required designer variable.
    /// </summary>
    private System.ComponentModel.IContainer components = null;

    private System.Windows.Forms.DataGridView businessesDataGridView;
    private BusinessesDataSet businessesDataSet;
    private System.Windows.Forms.BindingSource businessesBindingSource;
    private BusinessesDataSetTableAdapters.BusinessesTableAdapter businessesTableAdapter;
    private BusinessesDataSetTableAdapters.TableAdapterManager tableAdapterManager;
    private BusinessesDataSetTableAdapters.IndustriesTableAdapter industriesTableAdapter;
    private System.Windows.Forms.BindingSource industriesBindingSource;
    private System.Windows.Forms.DataGridViewTextBoxColumn iDDataGridViewTextBoxColumn;
    private System.Windows.Forms.DataGridViewComboBoxColumn industryIDDataGridViewTextBoxColumn;
    private System.Windows.Forms.DataGridViewTextBoxColumn businessNameDataGridViewTextBoxColumn;

    public BusinessesForm()
    {
        this.components = new System.ComponentModel.Container();
        this.businessesDataGridView = new System.Windows.Forms.DataGridView();
        this.businessesDataSet = new WindowsFormsBindingDemo.BusinessesDataSet();
        this.businessesBindingSource = new System.Windows.Forms.BindingSource(this.components);
        this.businessesTableAdapter = new WindowsFormsBindingDemo.BusinessesDataSetTableAdapters.BusinessesTableAdapter();
        this.tableAdapterManager = new WindowsFormsBindingDemo.BusinessesDataSetTableAdapters.TableAdapterManager();
        this.industriesBindingSource = new System.Windows.Forms.BindingSource(this.components);
        this.industriesTableAdapter = new WindowsFormsBindingDemo.BusinessesDataSetTableAdapters.IndustriesTableAdapter();
        this.iDDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
        this.industryIDDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewComboBoxColumn();
        this.businessNameDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
        ((System.ComponentModel.ISupportInitialize)(this.businessesDataGridView)).BeginInit();
        ((System.ComponentModel.ISupportInitialize)(this.businessesDataSet)).BeginInit();
        ((System.ComponentModel.ISupportInitialize)(this.businessesBindingSource)).BeginInit();
        ((System.ComponentModel.ISupportInitialize)(this.industriesBindingSource)).BeginInit();
        this.SuspendLayout();
        // 
        // businessesDataGridView
        // 
        this.businessesDataGridView.AutoGenerateColumns = false;
        this.businessesDataGridView.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
        this.businessesDataGridView.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
        this.iDDataGridViewTextBoxColumn,
        this.industryIDDataGridViewTextBoxColumn,
        this.businessNameDataGridViewTextBoxColumn});
        this.businessesDataGridView.DataSource = this.businessesBindingSource;
        this.businessesDataGridView.Dock = System.Windows.Forms.DockStyle.Fill;
        this.businessesDataGridView.Location = new System.Drawing.Point(0, 0);
        this.businessesDataGridView.Name = "businessesDataGridView";
        this.businessesDataGridView.RowTemplate.Height = 24;
        this.businessesDataGridView.Size = new System.Drawing.Size(554, 374);
        this.businessesDataGridView.TabIndex = 0;
        // 
        // businessesDataSet
        // 
        this.businessesDataSet.DataSetName = "BusinessesDataSet";
        this.businessesDataSet.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
        // 
        // businessesBindingSource
        // 
        this.businessesBindingSource.DataMember = "Businesses";
        this.businessesBindingSource.DataSource = this.businessesDataSet;
        // 
        // businessesTableAdapter
        // 
        this.businessesTableAdapter.ClearBeforeFill = true;
        // 
        // tableAdapterManager
        // 
        this.tableAdapterManager.BackupDataSetBeforeUpdate = false;
        this.tableAdapterManager.BusinessesTableAdapter = this.businessesTableAdapter;
        this.tableAdapterManager.IndustriesTableAdapter = this.industriesTableAdapter;
        this.tableAdapterManager.UpdateOrder = WindowsFormsBindingDemo.BusinessesDataSetTableAdapters.TableAdapterManager.UpdateOrderOption.InsertUpdateDelete;
        // 
        // industriesBindingSource
        // 
        this.industriesBindingSource.DataMember = "Industries";
        this.industriesBindingSource.DataSource = this.businessesDataSet;
        // 
        // industriesTableAdapter
        // 
        this.industriesTableAdapter.ClearBeforeFill = true;
        // 
        // iDDataGridViewTextBoxColumn
        // 
        this.iDDataGridViewTextBoxColumn.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.Fill;
        this.iDDataGridViewTextBoxColumn.DataPropertyName = "ID";
        this.iDDataGridViewTextBoxColumn.HeaderText = "ID";
        this.iDDataGridViewTextBoxColumn.Name = "iDDataGridViewTextBoxColumn";
        this.iDDataGridViewTextBoxColumn.ReadOnly = true;
        // 
        // industryIDDataGridViewTextBoxColumn
        // 
        this.industryIDDataGridViewTextBoxColumn.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.Fill;
        this.industryIDDataGridViewTextBoxColumn.DataPropertyName = "IndustryID";
        this.industryIDDataGridViewTextBoxColumn.DataSource = this.industriesBindingSource;
        this.industryIDDataGridViewTextBoxColumn.DisplayMember = "IndustryName";
        this.industryIDDataGridViewTextBoxColumn.FillWeight = 300F;
        this.industryIDDataGridViewTextBoxColumn.HeaderText = "IndustryID";
        this.industryIDDataGridViewTextBoxColumn.Name = "industryIDDataGridViewTextBoxColumn";
        this.industryIDDataGridViewTextBoxColumn.Resizable = System.Windows.Forms.DataGridViewTriState.True;
        this.industryIDDataGridViewTextBoxColumn.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic;
        this.industryIDDataGridViewTextBoxColumn.ValueMember = "ID";
        // 
        // businessNameDataGridViewTextBoxColumn
        // 
        this.businessNameDataGridViewTextBoxColumn.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.Fill;
        this.businessNameDataGridViewTextBoxColumn.DataPropertyName = "BusinessName";
        this.businessNameDataGridViewTextBoxColumn.FillWeight = 300F;
        this.businessNameDataGridViewTextBoxColumn.HeaderText = "BusinessName";
        this.businessNameDataGridViewTextBoxColumn.Name = "businessNameDataGridViewTextBoxColumn";
        // 
        // BusinessesForm
        // 
        this.AutoScaleDimensions = new System.Drawing.SizeF(8F, 16F);
        this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
        this.ClientSize = new System.Drawing.Size(554, 374);
        this.Controls.Add(this.businessesDataGridView);
        this.Name = "BusinessesForm";
        this.Text = "Businesses";
        this.Load += new System.EventHandler(this.BusinessesForm_Load);
        ((System.ComponentModel.ISupportInitialize)(this.businessesDataGridView)).EndInit();
        ((System.ComponentModel.ISupportInitialize)(this.businessesDataSet)).EndInit();
        ((System.ComponentModel.ISupportInitialize)(this.businessesBindingSource)).EndInit();
        ((System.ComponentModel.ISupportInitialize)(this.industriesBindingSource)).EndInit();
        this.ResumeLayout(false);
    }


    private void BusinessesForm_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'businessesDataSet.Industries' table. You can move, or remove it, as needed.
        this.industriesTableAdapter.Fill(this.businessesDataSet.Industries);
        // TODO: This line of code loads data into the 'businessesDataSet.Businesses' table. You can move, or remove it, as needed.
        this.businessesTableAdapter.Fill(this.businessesDataSet.Businesses);
    }



    /// <summary>
    /// Clean up any resources being used.
    /// </summary>
    /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
    protected override void Dispose(bool disposing)
    {
        if (disposing && (components != null))
        {
            components.Dispose();
        }
        base.Dispose(disposing);
    }

}

}

Answer 3

I can tell you for certain that a List<string> can't work as a DataSource. Instead you need to wrap the strings in a class, even as simple as this..:

class StringClass
{
    public string theString { get; set; }
    public StringClass(string s) { theString = s; }
    public override string ToString()  {  return theString;}
}

.. because a DataSource needs to be fed from Properties. You may want to try this to create such a list from your DataTable:

List<StringClass> industries = DT.AsEnumerable().Select(r=>r[fieldIndex].ToString() )
             .Distinct().OrderBy(r=>r).Select(r => new StringClass(r)).ToList();

Obvioulsy you'll leave out Distinct and OrderBy if you don't want them and change the field index..!

Update

Here is a block of code (without the connection) that reads from a MySql table. It reads a list of suppliers (lieferanten) into DataTable DTL and then reads distinct the locations (ort) from the same DB table into a 2nd DataTable DTO.. From there I pull the source for the ComboBox into a list, as shown above.

It creates the three columns (int, string, string) and the two necessary template cells dct and dccb.

It sets the members and first sets the DataSourceof the combobox cell, then of the datagridview.

When the DataGridView fills, all combobox cells are set to the right values as it should be..

MySqlDataAdapter DA = new MySqlDataAdapter();
string sqlSelectAll = "SELECT * from lieferanten";
DA.SelectCommand = new MySqlCommand(sqlSelectAll, CO);

DA.Fill(DTL);

string sqlSelectOrte = "SELECT DISTINCT ort from lieferanten";
DA.SelectCommand = new MySqlCommand(sqlSelectOrte, CO);

DA.Fill(DTO);

ortsListe = DTO.AsEnumerable().Select(r => r["ort"].ToString())
            .OrderBy(r => r).Select(r => new StringClass(r)).ToList();


BindingSource bSource = new BindingSource();
bSource.DataSource = DTL;

dgv_Lief.AutoGenerateColumns = false;


DataGridViewCell  dct = new DataGridViewTextBoxCell();

DataGridViewColumn dc0 = new DataGridViewColumn();
dc0.ValueType = typeof(int);
dc0.Name = "lieferanten_key";
dc0.DataPropertyName = "lieferanten_key";
dc0.CellTemplate = dct;
dgv_Lief.Columns.Add(dc0);

DataGridViewColumn dc1 = new DataGridViewColumn();
dc1.ValueType = typeof(string);
dc1.Name = "name";
dc1.CellTemplate = dct;
dc1.DataPropertyName = "firma";
dgv_Lief.Columns.Add(dc1);

DataGridViewComboBoxCell dccb = new DataGridViewComboBoxCell();
dccb.DataSource = ortsListe;
dccb.DisplayMember = "theString";  // using the same field..
dccb.ValueMember   = "theString";  // .. as I have only one

DataGridViewColumn dc2 = new DataGridViewColumn();
dc2.ValueType = typeof(string);
dc2.DataPropertyName = "ort";
dc2.CellTemplate = dccb;
dgv_Lief.Columns.Add(dc2);

dgv_Lief.DataSource = bSource;

Note the order of things. First set up the template cell dccb including the DataSource, then add it to the dgv, then set the dgv's DataSource.

I make use of the StringClass from above.

Even if I leave out setting the DGV's DataSource I can add rows and use the values from the ComboBoxColumn, as expected..