ComboBox Filtering in DataGridView

[Phil2:3]

Hello all,

I am new to C# and thought I would post here based upon a posting I saw here yesterday indicating it is a good forum for beginners. Have been posting on CP, but I think I am just annoying them there......:icon_sad:

Anyway, I have been struggling with a DataGridView with two comboBox columns. I trying to filter the second column based upon the selection of the first column. Please see the code below:

You'll notice I have a comment in primaryCB_SelectedIndexChanged regarding wanting to set the bindingSource for the second ComboBox. However, I have tried a multitude of things without success. This seems like a straightforward problem, but I am finding otherwise (at least for someone with my skills). Thanks.

public void Form1_Load(object sender, EventArgs e)
        {

            DataTable tblPrimary = dataSet1.Tables.Add("Primary");
            tblPrimary.Columns.Add("Type");

            tblPrimary.Rows.Add("Force");
            tblPrimary.Rows.Add("Torque");
            tblPrimary.Rows.Add("Pressure");

            DataTable tblSecondary = dataSet1.Tables.Add("Secondary");
            tblSecondary.Columns.Add("Primary_Type"); tblSecondary.Columns.Add("Unit");
            tblSecondary.Rows.Add("Force", "lb");
            tblSecondary.Rows.Add("Force", "N");
            tblSecondary.Rows.Add("Force", "oz");
            tblSecondary.Rows.Add("Torque", "in-lb");
            tblSecondary.Rows.Add("Torque", "oz-in");
            tblSecondary.Rows.Add("Torque", "N-m");

            dataGridView1.DataSource = tblSecondary;

            DataGridViewComboBoxColumn clmType = new DataGridViewComboBoxColumn();
            clmType.DataSource = tblPrimary;
            clmType.ValueMember = "Type";
            dataGridView1.Columns.Add(clmType);
          
            DataGridViewComboBoxColumn clmUnit = new DataGridViewComboBoxColumn();
            clmUnit.DataSource = tblSecondary;
            clmUnit.ValueMember = "Unit";
            dataGridView1.Columns.Add(clmUnit);
   
        }//end of Form1_Load

        private void primaryCB_SelectedIndexChanged(object sender, EventArgs e)
        {

            if (dataGridView1.CurrentCell.ColumnIndex == 0) //allow control on only one column
            {

                ComboBox cb = sender as ComboBox;

                if (cb != null)
                {
                    Debug.WriteLine(cb.SelectedValue + "TEST");

                    BindingSource bsSecondary = new BindingSource();

                    bsSecondary.DataSource = dataSet1.Tables["tblSecondary"];
                   
                    string filter = string.Format("Primary_Type = {0}", cb.SelectedValue);

                    bsSecondary.Filter = filter;

                    Debug.WriteLine(filter);

                    //I want to have something like
                    //clmUnit.DataSource = bsSecondary;
                    //here, but that doesn't work....
                    
                            
                }
            }
        }//end of primaryCB_SelectedIndexChanged

        private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
        {

            if (dataGridView1.CurrentCell.ColumnIndex == 0)  //allow control only on one column
            {
                ComboBox cb = e.Control as ComboBox;
                if (cb != null)
                {
                    //need to remove the handler first, otherwise will occur for each row in grid as new row is added
                    cb.SelectedIndexChanged -= primaryCB_SelectedIndexChanged;
                    
                    cb.SelectedIndexChanged += primaryCB_SelectedIndexChanged;
                }
            }

        }// End of EditingControlShowing

[sknake]

Here is one way to go about it. This code doesn't validate if you select a secondary value then change the primary value... you will want to handle that situation.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;

namespace daniweb
{
  public partial class frmDgvComboBox : Form
  {
    DataTable tblPrimary, tblSecondary;
    DataView viewSecondary;

    public frmDgvComboBox()
    {
      InitializeComponent();
    }

    private void frmDgvComboBox_Load(object sender, EventArgs e)
    {
      tblPrimary = new DataTable();
      tblPrimary.Columns.Add(new DataColumn("Type", typeof(string)));

      tblPrimary.BeginLoadData();
      try
      {
        tblPrimary.Rows.Add("Force");
        tblPrimary.Rows.Add("Torque");
        tblPrimary.Rows.Add("Pressure");
      }
      finally
      {
        tblPrimary.EndLoadData();
      }

      tblSecondary = new DataTable();
      tblSecondary.Columns.Add(new DataColumn("Primary_Type", typeof(string)));
      tblSecondary.Columns.Add(new DataColumn("Unit", typeof(string)));
      try
      {
        tblSecondary.Rows.Add("Force", "lb");
        tblSecondary.Rows.Add("Force", "N");
        tblSecondary.Rows.Add("Force", "oz");
        tblSecondary.Rows.Add("Torque", "in-lb");
        tblSecondary.Rows.Add("Torque", "oz-in");
        tblSecondary.Rows.Add("Torque", "N-m");
      }
      finally
      {
        tblSecondary.EndLoadData();
      }

      viewSecondary = new DataView(tblSecondary);

      dataGridView1.Columns.Clear();

      DataGridViewComboBoxColumn clmType = new DataGridViewComboBoxColumn();
      clmType.DataSource = tblPrimary;
      clmType.ValueMember = "Type";
      clmType.DisplayMember = "Type";
      clmType.DataPropertyName = "Primary_Type";
      clmType.HeaderText = "Type";
      clmType.Name = "Type";
      dataGridView1.Columns.Add(clmType);

      DataGridViewComboBoxColumn clmUnit = new DataGridViewComboBoxColumn();
      //clmUnit.DataSource = tblSecondary;
      clmUnit.DataSource = viewSecondary;
      clmUnit.ValueMember = "Unit";
      clmUnit.DisplayMember = "Unit";
      clmUnit.HeaderText = "Unit";
      clmUnit.DataPropertyName = "Unit";
      clmUnit.Name = "Unit";
      dataGridView1.Columns.Add(clmUnit);

      dataGridView1.AutoGenerateColumns = false;
      dataGridView1.DataSource = tblSecondary;
    }

    private void dataGridView1_EditingControlShowing_1(object sender, DataGridViewEditingControlShowingEventArgs e)
    {
      DataGridView grid = (sender as DataGridView);
      Debug.Assert(grid != null);
      Debug.Assert(grid.CurrentCell != null);
      if (grid.CurrentCell.OwningColumn == grid.Columns["Unit"])
      {
        ComboBox cb = (e.Control as ComboBox);
        Debug.Assert(cb != null);
        viewSecondary.RowFilter = string.Format("Primary_Type='{0}'",
          Convert.ToString(grid.CurrentRow.Cells["Type"].Value ?? string.Empty));
      }
    }

    private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
    {
      DataGridView grid = (sender as DataGridView);
      Debug.Assert(grid != null);
      if (e.ColumnIndex == grid.Columns["Unit"].Index)
      {
        e.Cancel = true;
      }
    }

    private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
    {
      DataGridView grid = (sender as DataGridView);
      Debug.Assert(grid != null);
      if (grid.Rows[e.RowIndex].IsNewRow)
        return;
      if (e.ColumnIndex == grid.Columns["Unit"].Index)
      {
        if (e.Value == null)
        {
          e.FormattingApplied = true;
          DataRowView row = grid.Rows[e.RowIndex].DataBoundItem as DataRowView;
          Debug.Assert(row != null);
          e.Value = Convert.ToString(row["Unit"]);
        }
      }
    }
  }
}

[Phil2:3]

Hi sknake,

Thank you for the reply. The line

dataGridView1.DataSource = tblSecondary;

causes the dataGridView to populate with all rows of tblSecondary. With that, I tried

dataGridView1.DataSource = viewSecondary;

That keeps from populating the entire table. However, selecting one of the value in the first column comboBox doesn't filter the second. I am still trying to figure that out (as well as what is going on in dataGridView1_CellFormatting as I unfamiliar with that class :-/)

[sknake]

In your original code you were binding to tblSecondary. What are you trying to do? Upload a small sample project with test data.

[Phil2:3]

To make the explanation simple:

Column 1 comboBox (cb1) has a collection of 'A', 'B', 'C'

Column 2 comboBox (cb2) has a collection of 'A1', 'A2', 'B1', 'B2', 'C1', C2'

There will be multiple rows in the table and it will be populated row by row by the user. If they select 'A' in cb1, cb2 is limited to 'A1', 'A2', 'A3' and if they select 'B', cb2 is limited to 'B1', 'B2'.............

I am slightly embarrassed (well actually highly) that I have tried at least 4 ways to solve this without success.

I am actually trying to go the route in Appendix A.18 in the DataGridView FAQ
http://social.msdn.microsoft.com/forums/en-US/winformsdatacontrols/thread/5378b9af-d0b6-4041-80ab-e76ac21c1cd4/

However, when I select anything in column 1 say "Force" , and then go to select the filtered value from column 2, I get "Cannot find column [Force]"

public partial class Form1 : Form
    {
        DataGridViewComboBoxColumn clmUnit = new DataGridViewComboBoxColumn();

        DataGridViewComboBoxColumn clmType = new DataGridViewComboBoxColumn();

        BindingSource bsSecondary = new BindingSource();

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable tblPrimary = dataSet1.Tables.Add("Primary");
            tblPrimary.Columns.Add("Type");

            tblPrimary.Rows.Add("Force");
            tblPrimary.Rows.Add("Torque");
            tblPrimary.Rows.Add("Pressure");

            DataTable tblSecondary = dataSet1.Tables.Add("Secondary");
            tblSecondary.Columns.Add("Primary_Type"); tblSecondary.Columns.Add("Unit");
            tblSecondary.Rows.Add("Force", "lb");
            tblSecondary.Rows.Add("Force", "N");
            tblSecondary.Rows.Add("Force", "oz");
            tblSecondary.Rows.Add("Torque", "in-lb");
            tblSecondary.Rows.Add("Torque", "oz-in");
            tblSecondary.Rows.Add("Torque", "N-m");
            tblSecondary.Rows.Add("Pressure", "PSI");


            //dataGridView1.DataSource = bsSecondary;

            
            clmType.DataSource = tblPrimary;
            clmType.ValueMember = "Type";
            dataGridView1.Columns.Add(clmType);

            

            DataView dv = new DataView(tblSecondary);
            //viewSecondary = new DataView(tblSecondary);

            bsSecondary.DataSource = dv;
            
            clmUnit.DataSource = bsSecondary;
            clmUnit.ValueMember = "Unit";
            dataGridView1.Columns.Add(clmUnit);
        }

        private void dataGridView1_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            if (e.ColumnIndex == clmUnit.Index)
            {
                DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)dataGridView1
                    [e.ColumnIndex, e.RowIndex];
                dgcb.DataSource = bsSecondary;

                this.bsSecondary.Filter = "Primary_Type = " + this.dataGridView1
                    [e.ColumnIndex - 1, e.RowIndex].Value.ToString();
            }
        }

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == clmUnit.Index)
            {
                DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)dataGridView1
                    [e.ColumnIndex, e.RowIndex];
                dgcb.DataSource = bsSecondary;

                this.bsSecondary.RemoveFilter();
            }
        }
    }

[Phil2:3]

modified from:

http://social.msdn.microsoft.com/forums/en-US/winformsdatacontrols/thread/6f6c2632-afd7-4fe9-8bf3-c2c8c08d1a31/

DataTable tblPrimary, tblSecondary;
        BindingSource primaryBS, filteredSecondaryBS, unfilteredSecondaryBS;
        
        public Form1()
        {

            tblPrimary = new DataTable("Primary");
            tblPrimary.Columns.Add("ID", typeof(int));
            tblPrimary.Columns.Add("Name", typeof(string));

            tblSecondary = new DataTable("Secondary");
            tblSecondary.Columns.Add("ID", typeof(int));
            tblSecondary.Columns.Add("subID", typeof(int));
            tblSecondary.Columns.Add("Name", typeof(string));

            tblPrimary.Rows.Add(new object[] { 0, "Force" });
            tblPrimary.Rows.Add(new object[] { 1, "Torque" });
            tblPrimary.Rows.Add(new object[] { 2, "Pressure" });

            tblSecondary.Rows.Add(new object[] { 0, 0, "lb" });
            tblSecondary.Rows.Add(new object[] { 1, 0, "N" });
            tblSecondary.Rows.Add(new object[] { 2, 0, "oz" });
            tblSecondary.Rows.Add(new object[] { 3, 1, "in-lb" });
            tblSecondary.Rows.Add(new object[] { 4, 1, "ft-lb" });
            tblSecondary.Rows.Add(new object[] { 5, 1, "N-m" });
            tblSecondary.Rows.Add(new object[] { 6, 2, "PSI" });
            tblSecondary.Rows.Add(new object[] { 7, 2, "Pa" });
            tblSecondary.Rows.Add(new object[] { 8, 2, "bar" });
            
            InitializeComponent();

            primaryBS = new BindingSource();
            primaryBS.DataSource = tblPrimary;
            primaryComboBoxColumn.DataSource = primaryBS;
            primaryComboBoxColumn.DisplayMember = "Name";
            primaryComboBoxColumn.ValueMember = "ID";

            // the ComboBox column is bound to the unfiltered DataView
            unfilteredSecondaryBS = new BindingSource();
            DataView undv = new DataView(tblSecondary);
            unfilteredSecondaryBS.DataSource = undv;
            secondaryComboBoxColumn.DataSource = unfilteredSecondaryBS;
            secondaryComboBoxColumn.DisplayMember = "Name";
            secondaryComboBoxColumn.ValueMember = "ID";

            // this binding source is where I perform my filtered view
            filteredSecondaryBS = new BindingSource();
            DataView dv = new DataView(tblSecondary);
            filteredSecondaryBS.DataSource = dv;
        }
                

       
        private void dataGridView1_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
                if (e.ColumnIndex == secondaryComboBoxColumn.Index)
                {
                    DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)dataGridView1[e.ColumnIndex, e.RowIndex];
                    dgcb.DataSource = filteredSecondaryBS;

                    this.filteredSecondaryBS.Filter = "subid = " +
                        this.dataGridView1[e.ColumnIndex - 1, e.RowIndex].Value.ToString();
                }
            
         }

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
           
                if (e.ColumnIndex == this.secondaryComboBoxColumn.Index)
                {
                    DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)dataGridView1[e.ColumnIndex, e.RowIndex];
                    dgcb.DataSource = unfilteredSecondaryBS;

                    this.filteredSecondaryBS.RemoveFilter();
                }
        
        }

[sknake]

Ah, that is probably a better way of going about it. Thanks for posting back with your solution.

Please mark this thread as solved (as you have answered your own question :P) and good luck!