CODEWITHSUNDEEP
c#winformsdatagridviewcombobox
The Joker
The Joker

Reputation: 402

Update DataGridView based on Combobox value

I have a form with a combo box and a datagridview control. The combo box is populated by a database table, and stores a Transaction ID.

When the combo box is updated, the Datagridview is bound to a database table, and displays the records associated with the Transaction ID from the combo box.

cboTransaction_SelectionChangeCommitted(object sender, EventArgs e)
{
    int id = Convert.ToInt32(cboTransaction.SelectedValue);
    db.Items.Where(i => i.TransactionId == id).Load();
    bs.DataSource = db.Items.Local.ToBindingList();
    dgv.DataSource = bs;
}

For the first record, this works fine. A user can select a transaction ID and add/edit Item records in the DataGridView without issue.

However, when a user selects a different Transaction from the combo box, things get a bit messier. Using the code above, the DataGridView does not clear the rows with the old ID number, but shows the old records + any records for the newly selected Transaction. I assumed that the Where() clause would limit the DataGridView to those items where the Transaction ID matched.

If I add db.SaveChanges(); and dgv.Rows.Clear(); to the beginning of that block, the rows are cleared successfully, however changes are not saved to the database.

I have also tried 

db.Items.Load();
bs.DataSource = db.Items.Local.ToBindingList().Where(i => i.TransactionId == id);

This binds the Datagridview to the correct Transaction, but does not allow the user to add any new records.

How can I ensure that the datagridview displays only the records related to the desired transaction, while still allowing the user to add/edit records?

EDIT

I created a test form with a textbox, button and datagridview for testing, and have the same results. The Datagridview has a Datasource of the database table being edited.

public partial class frmTest : Form
{
private BindingSource bs = new BindingSource();
private UniformDataContainer db = new UniformDataContainer();
public frmTest()
{
    InitializeComponent();
    dataGridView1.DataSource = bs;
}

private void button1_Click(object sender, EventArgs e)
{
    int id = Convert.ToInt32(textBox1.Text.ToString());
    db.Items.Where(i => i.TransactionId == id).Load();
    bs.DataSource = db.Items.Local.ToBindingList();

    bs.ResetBindings(false);
}

private void dataGridView1_RowValidated(object sender, DataGridViewCellEventArgs e)
{
    db.SaveChanges();
}
}

While debugging the code, I found that db.Items.Local still contained the old rows, and did not seem to clear.

Upvotes: 0

Views: 3707

Answers (3)

The Joker
The Joker

Reputation: 402

I managed to fix it / hack around it by disposing of my Database Conext, and re-connecting:

db.Dispose();
db = new UniformDataContainer();
int id = Convert.ToInt32(textBox1.Text);
db.Items.Where(i => i.TransactionId == id).Load();
bs.DataSource = db.Items.Local.ToBindingList();

This saves the existing records for the previous transaction, and clears the data grid after selecting a new transaction.

Thanks to @gzaxx and @SerkanOzvatan for your responses!

Upvotes: 0

SerkanOzvatan
SerkanOzvatan

Reputation: 241

You must set grid's DataSource to null before setting it.Like this : 

cboTransaction_SelectionChangeCommitted(object sender, EventArgs e)
{
    int id = Convert.ToInt32(cboTransaction.SelectedValue);
    db.Items.Where(i => i.TransactionId == id).Load();
    bs.DataSource = db.Items.Local.ToBindingList();
    dgv.DataSource = null;
    dgv.DataSource = bs;
}

Upvotes: 0

gzaxx
gzaxx

Reputation: 17590

Instead of rebinding DataSource to DataGridView after change, call ResetBindings method that should reset grid with new values.

cboTransaction_SelectionChangeCommitted(object sender, EventArgs e)
{
    int id = Convert.ToInt32(cboTransaction.SelectedValue);

    db.Items.Where(i => i.TransactionId == id).Load();
    bs.DataSource = db.Items.Local.ToBindingList();

    bs.ResetBindings(false);
}

try it and see if that fixed your problems, if not I have one more suggestion, but this should do the job :)

Edit

Okay after your edit I see problem. First you have to clear local cache of your DB class or bind to your where clause:

cboTransaction_SelectionChangeCommitted(object sender, EventArgs e)
{
    int id = Convert.ToInt32(cboTransaction.SelectedValue);

    var bindinglist = new BindingList(db.Items.Where(i => i.TransactionId == id).ToList());
    bs.DataSource = bindinglist;

    bs.ResetBindings(false);
}

Upvotes: 1

Related Questions