datagridview filtering....where to start?

[mprice214]

I have the following and I'm having a hard time getting the second comboBox column to hook up to the filtered datasource. As you can see, there are two comboBoxColumns in the dataGridView. Am I missing something obvious here? Thanks. (Also, I have tried linking it up in primaryCB_SelectedIndexChanged, without success, which is why there isn't any code there for that)

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 col = new DataGridViewComboBoxColumn();
        col.DataSource = tblPrimary;
        col.ValueMember = "Type";
        dataGridView1.Columns.Add(col);

        
        DataGridViewComboBoxColumn col2 = new DataGridViewComboBoxColumn();
        col2.DataSource = tblSecondary;
        col2.ValueMember = "Unit";
        dataGridView1.Columns.Add(col2);

       
    }

    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);

[Stanciu Vlad]

BindingSource secondTableBindingSource = new BindingSource();
public void Form1_Load(object sender, EventArgs e) {
// ... etc

DataGridViewComboBoxColumn col = new DataGridViewComboBoxColumn();
col.DataSource = tblPrimary;
col.ValueMember = "Type";
dataGridView1.Columns.Add(col);

**secondTableBindingSource.DataSource = tblSecondary;**

DataGridViewComboBoxColumn col2 = new DataGridViewComboBoxColumn();
**col2.DataSource = secondTableBindingSource;**
col2.ValueMember = "Unit";
dataGridView1.Columns.Add(col2);

}

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)
{
if(cb.SelectedValue != null)
secondTableBindingSource.Filter = string.Format("Primary_Type = '{0}'", cb.SelectedValue);
else
secondTableBindingSource.Filter = string.Empty;
}
}
}

I have no smart signature yet...

[mprice214]

Stanciu Vlad wrote:

secondTableBindingSource.DataSource = tblSecondary;

This results in creating a table in the dataGridView that has i Rows + 1 (the +1 is the default row for the next entry), which are all blank. In this case it adds the 6 rows that correspond to 'tblSecondary.Rows.Add', but the rows added are blank. The first comboBox does allow you to drop down the three selections, but the row will remain blank. However, the filter will eliminate all rows except for a number of rows pertaining to the number of items in the filtered 'Units' column. But again they are blank, but you can at least select one and have it write to the cell. And last, but not least, selecting on the next row, 1st column comboBox throws an "Object reference not set to an instance of an object". Unless you see something obvious here and can prod me in that direction, I have spent way too much time on trying to get this to work and it probably makes sense to throw out what I've tried and start from scratch. I thought this was going to be a bit more straightforward, but alas...... X|

[Stanciu Vlad]

Check this out...

    private void Form1\_Load(object sender, EventArgs e)
    {
        DataTable mainData = new DataTable("mainData");
        mainData.Columns.Add("ID", typeof(int));
        mainData.Columns.Add("ValueType", typeof(int));
        mainData.Columns.Add("ValueUnitOfMeasurement", typeof(int));
        mainData.Columns.Add("TheValue", typeof(decimal));

        DataTable valueType = new DataTable("valueType");
        valueType.Columns.Add("ID", typeof(int)); // should be like a primary key
        valueType.Columns.Add("Description", typeof(string));

        DataTable valueUnitOfMeasurement = new DataTable("valueUnitOfMeasurement");
        valueUnitOfMeasurement.Columns.Add("ID", typeof(int)); // should be like a primary key
        valueUnitOfMeasurement.Columns.Add("ValueTypeID", typeof(int));
        valueUnitOfMeasurement.Columns.Add("Description", typeof(string));

        valueType.Rows.Add(new object\[\] { 1, "Force" });
        valueType.Rows.Add(new object\[\] { 2, "Torque" });
        valueType.Rows.Add(new object\[\] { 3, "Pressure" });

        valueUnitOfMeasurement.Rows.Add(new object\[\] { 1, 1, "lb"});
        valueUnitOfMeasurement.Rows.Add(new object\[\] { 2, 1, "N" });
        valueUnitOfMeasurement.Rows.Add(new object\[\] { 3, 2, "N-m" });
        valueUnitOfMeasurement.Rows.Add(new object\[\] { 4, 2, "in-lb" });
        valueUnitOfMeasurement.Rows.Add(new object\[\] { 5, 3, "Pa" });
        valueUnitOfMeasurement.Rows.Add(new object\[\] { 6, 3, "bar" });


        dataGridView1.AutoGenerateColumns = false;
        dataGridView1.DataSource = mainData;

        valueTypeColumn.DataSource = valueType;
        valueTypeColumn.ValueMember = "ID";
        valueTypeColumn.DisplayMember = "Description";

        myBinding.DataSource = valueUnitOfMeasurement;
        valueUnitOfMeasurementColumn.DataSource = myBinding;
        valueUnitOfMeasurementColumn.ValueMember = "ID";
        valueUnitOfMeasurementColumn.DisplayMember = "Description";

        dataGridView1.EditingControlShowing += new DataGridViewEditingControlShowingEventHandler(dataGridView1\_EditingControlShowing);
        dataGridView1.RowValidated += new DataGridViewCellEventHandler(dataGridView1\_RowValidated);
    }

    void dataGridView1\_RowValidated(object sender, DataGridViewCellEventArgs e)
    {
        myBinding.Filter = string.Emp

[mprice214]

Apparently you took a look at the code I had in the previous post and said "enough is enough" or "I'll never be able to help that"?? :-D A couple of questions:

Stanciu Vlad wrote:

DataTable mainData = new DataTable("mainData"); mainData.Columns.Add("ID", typeof(int)); mainData.Columns.Add("ValueType", typeof(int)); mainData.Columns.Add("ValueUnitOfMeasurement", typeof(int)); mainData.Columns.Add("TheValue", typeof(decimal));

I can't see that this is doing anything accept providing a DataSource place marker for dataGridView1. In fact, all of the mainData.Columns.Add lines can be commented out without impacting the functionality. In either case, it doesn't take long to add different types of measurements (Force, Torque, or Pressure) to have it begin to throw exceptions. Would you concur with this? Thanks.

[mprice214]

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

public partial class Form1 : Form
{
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";
        secondaryComboBoxColum

[Stanciu Vlad]

mainData is the place where the data is stored. I said that you could persist this in a database. So if you did that then your table would look like that: with a reference to the ValueType table and a referece to the ValueUnitOfMeasurement on each row. If you comment out all the mainData columns you would not have a place where to store the data entered in the grid and the grid would be empty. So, if you add different types of measurements and do not bind them correctly the application will crash. In all the tables the ID column must be unique (numbers from 1 to n). In the valueUnitOfMeasurement table there is an ID column (that must be unique) and a reference to a valueType (stated in the valueType table). The reference is the ID of the valueType. Let's assume you want to add a new measurement type named "Distance" with the units of measurement "km", "m", "cm", "mm". The first step is to add the record in the ValueType table:

valueType.Rows.Add(new object[] {100, "Distance"});

Note that the row added is [100, "Distance"] - 100 is the Distance's ID (must be unique in the table). Next you must add records in the ValueUnitOfMeasurement table:

valueType.Rows.Add(new object[] {500, 100, "km"});
valueUnitOfMeasurement.Rows.Add(new object[] {501, 100, "m"});
valueUnitOfMeasurement.Rows.Add(new object[] {502, 100, "cm"});
valueUnitOfMeasurement.Rows.Add(new object[] {503, 100, "mm"});

Note that the first row added is [500, 100, "km"] - 500 is the UnitOfMeasurement's ID and 100 is the valueType's ID correspondind to this unit of measurement (A couple of lines above we stated that for distance 100 is the ID). The logic in my previous example filters the unit of measurements based on the selected value type ID in the valueType combo box. This supports an logically unlimited number of measurement types and units of measure. Also this supports persistance (database or xml).

I have no smart signature yet...

[Stanciu Vlad]

It's ok if it works. I's kind of based on the principle stated by me in a previous post. The filter logic is a little diferent, it's a different approach. Just as a note: 1. a bindingSource does aproximatively the same thing as a dataView (if not the same), so there is no reason to bind to the bindingSource a dataView binded to a dataTable. You could bind the table directly to the bindingSource. 2. having a filtered and an unfiltered bindingSource is the same as having a bindingSource that can be filtered or unfiltered. :) 3. I think that if you change the datasource of a control the current selected value is lost (not sure, but this would seam logic to me)

I have no smart signature yet...

[mprice214]

Were you not getting exceptions after adding a couple of Force rows and then select a Torque row?

[Stanciu Vlad]

I don't recall, but if in certain conditions you get an exception then you've found a bug :)

I have no smart signature yet...