CODEWITHSUNDEEP
c#mysqldata-bindingdatagridview
Lefteris E
Lefteris E

Reputation: 2814

DataGridView with adapter and combobox

If i bind a DataGridView(dgv) to an adapter, the dgv is populated automatically with the contents of the table. However one of the columns that is now presented as text, I want to change it to a ComboBox.

This code prepares a data adapter:

MySqlDataAdapter awardsAdapter, spendingsAdapter;
DataSet cachedData = new DataSet(); 
MySqlDataAdapter PrepareSpendingsAdapter() {
try {
        MySqlDataAdapter adapter;
        adapter = new MySqlDataAdapter("select * from spendings", connection);
        adapter.TableMappings.Add("Table", "spendings");

        adapter.UpdateCommand = new MySqlCommand(
            "UPDATE spendings SET category=@category, points=@points WHERE id=@id;",
            connection);
        adapter.UpdateCommand.Parameters.Add("@id", MySqlDbType.Int32, 10, "ID");                
        adapter.UpdateCommand.Parameters.Add("@category", MySqlDbType.Int32, 10, "Category");
        adapter.UpdateCommand.Parameters.Add("@points", MySqlDbType.Int32, 10, "Points");                
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        adapter.InsertCommand = new MySqlCommand(
            "INSERT INTO spendings VALUES (@id,@category,@points);",
            connection);
        adapter.InsertCommand.Parameters.Add("@id", MySqlDbType.Int32, 10, "ID");
        adapter.InsertCommand.Parameters.Add("@category", MySqlDbType.Int32, 10, "Category");
        adapter.InsertCommand.Parameters.Add("@points", MySqlDbType.Int32, 10, "Points");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        adapter.DeleteCommand = new MySqlCommand(
            "DELETE FROM spendings WHERE id=@id;", connection);
        adapter.DeleteCommand.Parameters.Add("@id", MySqlDbType.Int32, 10, "ID");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

        return adapter;
    } catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
    return null;
}

This code adds an extra column with the information i need

DataGridViewComboBoxColumn cmb = new DataGridViewComboBoxColumn();
MySqlDataAdapter adapterSpendCategory = new MySqlDataAdapter("select category,details from spendcategory", connection);
adapterSpendCategory.TableMappings.Add("Table", "spendcategory");
adapterSpendCategory.Fill(cachedData);
cmb.DataSource = cachedData.Tables["spendcategory"];
cmb.DisplayMember = "details";
cmb.ValueMember = "category";
//Put the missing piece of the puzzle here

This code populates the DataGridView:

spendingsAdapter = PrepareSpendingsAdapter();
spendingsAdapter.Fill(cachedData);
dgvPointsSpent.DataSource = cachedData.Tables["spendings"];

Instead of adding this extra column i want the category column to change and look like the extra column

enter image description here

Upvotes: 0

Views: 972

Answers (2)

Lefteris E
Lefteris E

Reputation: 2814

It seems the answer was adding the line colCategory.DataPropertyName = "Category". Adding this line before calling spendingsAdapter.Fill(..) did the trick as it recognized the column already existed and didn't create a new one.

Upvotes: 0

Harald Coppoolse
Harald Coppoolse

Reputation: 30512

If you use the designer to add the data grid view, and select it, you'll see a little arrow to some properties. You usually use this to bind the datagridview to a bindingsource. You can also use the arrow to "edit columns". For each coiumn you can select whether it must be displayed or not, in which order and how the data must be displayed (as text? as a combo box? as a number?).

So if you already managed to add a combo box column that suits your needs, all you have to do is go to "edit columns", select the columns you dont't want to see and either remove them from the list of columnhs in the datagridview, or use the properties of that column to make it invisible.

Normally you wouldn't add an extra column, but change the properties so it displays a combobox instead of text.

Upvotes: 1

Related Questions