How do I display data in a DataGridView based on a selected value from a combo box?-CodePudding

I have a ComboBox that lists Customer Names from an MS Access DB. When I select a customer name it populates a textbox with the corresponding CustomerID. I have a DataGridView which displays data from a table, however, how do I specify that only the data relating to the specific customer ID is shown?

The code for my DataGridView is as follows;

private void btnLoadDeliveryLog_Click(object sender, EventArgs e)
    {
        try
        {
            OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Carmine_Cycle_Couriers_Database.accdb");
            cn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = cn;
            string query = "Select * from tblDeliveryLog";
            cmd.CommandText = query;

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;


            cn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error "   ex);
        }

        }
    }

CodePudding user response：

Made changes to your code, try below code

    try
    {
        OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Carmine_Cycle_Couriers_Database.accdb");
        cn.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = cn;

        //string query = "Select * from tblDeliveryLog";

        //Change query like this
        string query = "Select * from tblDeliveryLog WHERE customerid = @customerid";

        //Add parameter to query
        cmd.Parameters.AddWithValue("@customerid", TextBoxCustomerId.Text);

        cmd.CommandText = query;

        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        dataGridView1.DataSource = dt;


        cn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error "   ex);
    }

CodePudding user response：

It is not clear what fields are in the grid and how the combo box is filled with data. Assuming the combo box is filled with a “CustomerID” as a ValueMember as your previous question shows and that there is a “CustomerID” field in the DataTable dt that is used as a data source to the grid that corresponds to the combo boxes ValueMember, then it may be easier to simply “filter” the existing data in the grid as opposed to re-querying the DB.

With a DataTable we could use a DataView to filter the grids data. However, we would need to “switch” the grids DataSource to each filtered DataView. In other words, we can not “directly” filter the DataTable and have it automatically update the grid.

Fortunately, a BindingSource WILL allow us to filter it and it will automatically update the grid and we never have to “switch” the grids DataSource.

Therefore, in the current code, you could add/edit the following code to set the grids data source to a BindingSource. Something like…

BindingSource OrdersBS;   // <- make a GLOBAL variable 


OrdersBS = new BindingSource();
OrdersBS.DataSource = dt;
dataGridView1.DataSource = OrdersBS;

Then in the combo boxes SelectedIndexChanged event we could easily filter the grid based on what value is selected in the combo box. NOTE: I added an addition item to the combo box in the first position with the value of zero (0) and the customer’s name of “All.” This will allow the user to “un-filter” the grid data.

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) {
  if ((int)comboBox1.SelectedValue != 0) {
    OrdersBS.Filter = "CustomerID = '"   (int)comboBox1.SelectedValue   "'";
  }
  else {
    OrdersBS.Filter = "";
  }
}

I hope this makes sense and helps.