Add row to WinForms combobox after populating it from a datatable

Question

I'm populating a combobox from a datatable with the following code:

public void PopulateCategoryCbo()
{
    string connString = string.Format(<connectionString>);
    NpgsqlConnection conn = new NpgsqlConnection(connString);
    conn.Open();

    string query = "SELECT id, category FROM categories ORDER BY category";
    NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

    NpgsqlDataReader reader;
    reader = cmd.ExecuteReader();

    DataTable dt = new DataTable();
    //DataRow row = dt.NewRow();
    //row["id"] = 0;
    //row["category"] = "All Categories";
    //dt.Rows.InsertAt(row, 0);

    dt.Columns.Add("id", typeof(string));
    dt.Columns.Add("category", typeof(string));
    dt.Load(reader);

    cboSelectCategory.ValueMember = "id";
    cboSelectCategory.DisplayMember = "category";
    cboSelectCategory.DataSource = dt;

    conn.Close();
}

The four commented lines are my attempt at adding to the top of the datatable an "All Categories" row with an index of 0. I did that code per this SO question and answer.

However, when I run the solution, it breaks at the row["id"] = 0; line with the message, 'Column "id" does not belong to table.'

I tried using 0 and 1, thinking they would refer to the index value of the columns, but I got the message 'Column 0 does not belong to table.'

I'm not understanding why this isn't working. I see a lot of similar solutions when I Google this issue.

Run your commented code *after* your `dt.Load(reader);` line. — LarsTech, Mar 18 '20 at 20:58
@LarsTech - Moving the code to after `dt.Load(reader);` did the trick. thanks again! — marky, Mar 18 '20 at 21:06

score 1 · Answer 1 · answered Mar 18 '20 at 21:05

Not an answer to the original question, but with approach below you will eliminate main issue.

For data source of combobox you don't need heavy DataTable, instead use plain c# objects

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Then load data from database into the list of Category objects

private List<Category> LoadCategories()
{
    var query = "SELECT id, category FROM categories ORDER BY category";
    using (var connection = new NpgsqlConnection(connString))
    using (var command = new NpgsqlCommand(query, connection))
    {
        conn.Open();
        var categories = new List<Category>();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var category = new Category()
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1)
                }
                categories.Add(category);
            }
        }

        return categories;
    }
}

Then usage become very simple

var categories = LoadCategories();

var all = new Category { Id = 0, Name = "All Categories" };
categories.Insert(0, all);

cboSelectCategory.ValueMember = "Id";
cboSelectCategory.DisplayMember = "Name";
cboSelectCategory.DataSource = categories;

This is an aspect of OOP that I'm sorely lacking knowledge and experience in. Thanks, Fabio! — marky, Mar 18 '20 at 21:18

score 0 · Answer 2 · answered Mar 18 '20 at 20:59

0

You're adding a row to dt which is a new DataTable that has no columns. If you want to fill a DataTable look into DbDataAdapter.Fill instead of DbDataReader. Fill the DataTable and then add your row.

answered Mar 18 '20 at 20:59

stillcollating

26
2

Add row to WinForms combobox after populating it from a datatable

2 Answers2