Re: datagridview filtering....where to start? - C# Discussion Boards

7-May-10 17:47

Hi all,

I have a question as to where to begin with what I'd like to to in a datagridview. The datagridview is bound to a dataset data table. One of the columns is a combobox. I want another column that is also a combobox too, but filtered based upon the selection of the first combobox.

Ex.

Column 1

Force

Column 2
lb
N
ton

or

Column 1

Torque

Column 2
in-lb
N-m

etc, etc.

My initial thought is to have an if loop that build the collection in the second combobox based upon the selection of the first. However, I don't know where this would go (even if that were to be the most efficient way to do it). Any help would be greatly appreciated.

Thanks.

Abhinav S7-May-10 18:13

Abhinav S

7-May-10 18:13

You might want to start from here.

Pedro Covarrubias7-May-10 19:30

Pedro Covarrubias

7-May-10 19:30

You should try ItemDatabound event to populate the second DropDownList

protected void dgEmpleados_ItemDataBound(object sender, DataGridItemEventArgs e)
{
DataTable dt = (DataTable)((DataGrid)sender).DataSource;

if (dt.Rows.Count > 0 && e.Item.ItemIndex > -1)
{
((CheckBox)e.Item.Cells[0].FindControl("chkSeleccionar")).Checked =
Convert.ToBoolean( dt.Rows[e.Item.ItemIndex][_COL_SELECCIONAR].ToString());

((TextBox)e.Item.Cells[0].FindControl("txtGridObservaciones")).Text =
dt.Rows[e.Item.ItemIndex][_COL_OBSERVACIONES].ToString();
}
}

Stanciu Vlad7-May-10 22:52

7-May-10 22:52

1)create a dataTable to store all the posibilities, something like this:

Option1 - Option2
Force lb
Force N
Force ton
Torque in-lb
... ...

2)Bind this table to your second comboBox via a bindingSource.
3)On selectedIndexChanged at your first comboBox, apply a filter to your second comboBox (something like "Option1 = " + comboBox1.Text, but adapt it to a dataGridView).

The good points to thins method is that you can store the filtering posibilities in the database (like this, or based on primary keys).

I have no smart signature yet...

mprice21420-May-10 10:12

20-May-10 10:12

Stanciu Vlad wrote:
apply a filter to your second comboBox (something like "Option1 = " + comboBox1.Text, but adapt it to a dataGridView).

I have the following filtering method:

private void GetRowsByFilter()
        {
            //DataTable table = DataSet1.Tables["Orders"];
            //// Presuming the DataTable has a column named Date.
            string expression;
            expression = "MeasureType = 'Force'";
            DataRow[] foundRows;

            // Use the Select method to find all rows matching the filter.
            foundRows = table.Select(expression);

            

            // Print column 0 of each returned row.
            for (int i = 0; i < foundRows.Length; i++)
            {
                
                Debug.WriteLine(foundRows[i][0] + "test");
            }
        }

I am calling this method in my comboBox SelectedIndexChanged.

With that I do get the number of rows returned that matches Force in Debug.Writeline, but not sure how to filter the 2nd ComboBox display member. Do you have any thoughts on this?

Stanciu Vlad20-May-10 10:22

Re: datagridview filtering....where to start? [modified]

20-May-10 10:22

You can use BindingSource to bind a dataTable to almost any control.

BindingSource bsOrders = new BindingSource();
bsOrders.DataSource = Dataset1;
bsOrders.DataMember = "Orders"

Bind the comboBoxColumn in the gridview to this binding source (if you don't know how check google, it's full of this stuff)

Then in the comboBox SelectedIndexChanged event you can directly filter the data using

//eventualy do a nothing selected check
if(filterText == string.Empty)
    bsOrders.Filter = string.Empty;
else
    bsOrders.Filter = "Option1 = " + filterText;

I have no smart signature yet...

mprice21424-May-10 8:24

24-May-10 8:24

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

                                    
                }
            }
        }

        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)
                {
                    cb.SelectedIndexChanged -= primaryCB_SelectedIndexChanged;
                    cb.SelectedIndexChanged += primaryCB_SelectedIndexChanged;
                }
            }

        }

modified on Monday, May 24, 2010 2:46 PM

Stanciu Vlad25-May-10 21:12

25-May-10 21:12

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...

mprice21426-May-10 7:48

26-May-10 7:48

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...... Dead | X|

Stanciu Vlad26-May-10 10:05

26-May-10 10:05

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.Empty; // reset the filter
}

void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
    if (e.Control is ComboBox) // if the editing control is a comboBox
    {
        ComboBox editingControl = e.Control as ComboBox;

        // check if the comboBox is the one binded to the binding source
        if (editingControl.DataSource is BindingSource)
        {
            if ((editingControl.DataSource as BindingSource).Equals(myBinding))
            {
                // this control is binded to the bindingSource, so let's filter the binding source

                if(dataGridView1.CurrentRow == null)
                    return;

                int selectedID;
                bool ok = Int32.TryParse(Convert.ToString(dataGridView1.CurrentRow.Cells["valueTypeColumn"].Value), out selectedID);

                if (ok) // do the filter
                    myBinding.Filter = "ValueTypeID = " + selectedID;
                else // no value selected
                    myBinding.Filter = "ValueTypeID = -1"; // show nothing


                return;
            }
        }
    }
}

I have no smart signature yet...

mprice21426-May-10 17:43

26-May-10 17:43

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"?? Big Grin | :-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.

Stanciu Vlad26-May-10 20:43

Re: datagridview filtering....where to start? <Solved>

26-May-10 20:43

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...

mprice21426-May-10 18:52

26-May-10 18:52

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";
            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();
                }
        
        }

Stanciu Vlad26-May-10 20:54

26-May-10 20:54

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. Smile | :)

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...

mprice21427-May-10 3:13

27-May-10 3:13

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

Stanciu Vlad27-May-10 6:52

XML Serialization of derived classes

27-May-10 6:52

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

I have no smart signature yet...

Roland Bär7-May-10 12:35

Roland Bär

7-May-10 12:35

Hi
I wanted to ask if it is possible to do something like this and how to do it:

I would like to have a xml structure like this:

<root>
 <collection>
  <element />
  <element attribute="value" />
  <element />
 </collection>
 <something />
 <somethingother />
</root>

The elements in the collection should be of the type ElementBase if the attribute is not set or of the type ElementDerived if the attribute is set to a value (the second element).

So the Deserialized collection should be something like:
List<ElementBase> collection = new List<ElementBase>() { new ElementBase(), new ElementDerived(), new ElementBase() };

ElementDerived would be derived from ElementBase:

class ElementDerived : ElementBase {}

I hope my question is described clearly.

Thanks for your help Rose | [Rose]

Roland

Hi! I'm a signature virus. Copy me into your sig file and help me spread!

Follow your Euro notes in their tracks

Re: XML Serialization of derived classes

Peace ON7-May-10 21:40

Peace ON

7-May-10 21:40

I find following link useful..

http://social.msdn.microsoft.com/Forums/en-US/netfxremoting/thread/41e462ab-f833-40a0-937b-3faaeba711e3/[^]

Jinal Desai - LIVE
Experience is mother of sage....

Re: XML Serialization of derived classes

Stanciu Vlad7-May-10 23:20