Resolved Combobox Filter

Daz66

Member
Joined
May 17, 2020
Messages
22
Programming Experience
Beginner
A novice question if I may.

I'm trying to cascade filter comboboxes to DGV in my winforms project.

I have a list derived from a class that populates cbx1 with CategoryA items, I'm trying to populate cbx2 with CategoryB items in the SelectedIndexChanged event based on the choice in cbx1 and displaying only the relevant categories in cbx2. It works as expected but without the filter.
The commented out LINQ is an attempt to do this but returns errors.

A point in the right direction would be great, thanks.

C#:
void cbxCategoryAFill()
        {
            List<Screwfix> scr = sc.GetAllScrewfixResults();

            this.comboBox1.DataSource = scr.Select(t => t.CategoryA).Distinct().ToList();
            comboBox1.Text = "--Select--";
        }
        void cbxCategoryBFill()
        {
            List<Screwfix> scr = sc.GetAllScrewfixResults();
            
            this.comboBox2.DataSource = scr.Select(t => t.CategoryB).Distinct().ToList();
            comboBox2.Text = "--Select--";
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.BeginInvoke((MethodInvoker)delegate { comboBox1.Text = "-Select-"; });//Note Resets combobox after selection

            List<Screwfix> scr = sc.GetAllScrewfixResults();
            dgvLeft.DataSource = scr.Where(x => x.CategoryA.Contains(comboBox1.Text)).ToList();

            // comboBox2.DataSource = ((IEnumerable<string>)dgvLeft.DataSource)
            //.Where(x =>
            //{
            //    return x == (string)comboBox1.SelectedValue;
            //});
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.BeginInvoke((MethodInvoker)delegate { comboBox1.Text = "-Select-"; });

            List<Screwfix> scr = sc.GetAllScrewfixResults();

            dgvLeft.DataSource = scr.Where(x => x.CategoryB.Contains(comboBox2.Text)).ToList();
        }
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
Please provide the complete error. Without the complete error, I can only assume that you are trying to cast dgvLeft.DataSource to an IEnumerable<string> shortly after you put in a List<ScrewFix> in there. Obviously that kind of casting won't work.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,554
Location
Sydney, Australia
Programming Experience
10+
Let's say that you have these types:
C#:
public class Parent
{
    public int ParentId { get; set; }
    public string ParentName { get; set; }   
}

public class Child
{
    public int ChildId { get; set; }
    public int ParentId { get; set; }
    public string ChildName { get; set; }   
}
You might have these fields:
C#:
private IList<Parent> allParents;
private IList<Child> allChildren;
and bind like this on load:
C#:
childComboBox.DisplayMember = "ChildName";
childComboBox.ValueMember = "ChildId";

parentComboBox.DisplayMember = "ParentName";
parentComboBox.ValueMember = "ParentId";
parentComboBox.DataSource = allParents;
and then this:
C#:
private void parentComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
    var parentId = (int)parentComboBox.SelectedValue;
    
    childComboBox.DataSource = allChildren.Where(c = c.ParentId == parentId).ToList();
}
You can implement that same principle as many times as you like for as many relationships as you like.
 

Daz66

Member
Joined
May 17, 2020
Messages
22
Programming Experience
Beginner
Please provide the complete error. Without the complete error, I can only assume that you are trying to cast dgvLeft.DataSource to an IEnumerable<string> shortly after you put in a List<ScrewFix> in there. Obviously that kind of casting won't work.
Apologies, and thanks for the replies, the debugger gave more info:

System.InvalidCastException: 'Unable to cast object of type 'System.Collections.Generic.List`1[Business.ClassLibrary.Screwfix]' to type 'System.Collections.Generic.IEnumerable`1[System.String]'.'
C#:
namespace Business.ClassLibrary
{
    public class Screwfix
    {
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Business"].ConnectionString;

        public int Id { get; set; }
        public string? Code { get; set; }
        public string? Description { get; set; }
        public string? CategoryA { get; set; }
        public string? CategoryB { get; set; }
        public string? Price { get; set; }

        //public override string ToString()
        //{
        //    return CategoryA + " ";
        //}
        public string scTest => $"{Code} {Description} {Price}";//Note for single result from combobox

        public List<Screwfix> GetAllScrewfixResults()
        {
            List<Screwfix> li = new List<Screwfix>();

            using (SqlConnection? con = new(connectionString))
            {
                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlDataAdapter da = new SqlDataAdapter("ScrewfixItemsGetAll", con))
                {
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    using (var reader = da.SelectCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Screwfix sc = new();
                            sc.Id = (int)reader["Id"];
                            sc.Code = reader["Code"].ToString();
                            sc.Description = reader["Description"].ToString();
                            sc.CategoryA = reader["CategoryA"].ToString();
                            sc.CategoryB = reader["CategoryB"].ToString();
                            sc.Price = reader["Price"].ToString();
                            
                            li.Add(sc);
                        }
                    }
                }
            }
            return li;
        }
    }
}
 

Daz66

Member
Joined
May 17, 2020
Messages
22
Programming Experience
Beginner
Let's say that you have these types:
C#:
public class Parent
{
    public int ParentId { get; set; }
    public string ParentName { get; set; }  
}

public class Child
{
    public int ChildId { get; set; }
    public int ParentId { get; set; }
    public string ChildName { get; set; }  
}
You might have these fields:
C#:
private IList<Parent> allParents;
private IList<Child> allChildren;
and bind like this on load:
C#:
childComboBox.DisplayMember = "ChildName";
childComboBox.ValueMember = "ChildId";

parentComboBox.DisplayMember = "ParentName";
parentComboBox.ValueMember = "ParentId";
parentComboBox.DataSource = allParents;
and then this:
C#:
private void parentComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
    var parentId = (int)parentComboBox.SelectedValue;
   
    childComboBox.DataSource = allChildren.Where(c = c.ParentId == parentId).ToList();
}
You can implement that same principle as many times as you like for as many relationships as you like.
Thank you for the reply, there's some great info here.
Would it be better practice for me to have created seperate tables for the different categories and thus the class creation, and then join them for the list?
 

Daz66

Member
Joined
May 17, 2020
Messages
22
Programming Experience
Beginner
You can implement that same principle as many times as you like for as many relationships as you like.
I got there with your help thank you, works perfectly. Great learning curve too, I now have a better understanding of table relationships because of this, thanks again.
 

Daz66

Member
Joined
May 17, 2020
Messages
22
Programming Experience
Beginner
Hello again. I was wondering if you would be able to assist me further.
I require a third combobox and have created the appropriate table, relationship and class, but I an recieving the following error when trying to filter to the third combo:

System.InvalidCastException: 'Unable to cast object of type 'Business.ClassLibrary.ScrewfixCat2' to type 'System.Int32'.'

The commented out code in the cbxCat2 event is where the exception is thrown, am I implementing this incorrectly?

C#:
private void cbxCat1_SelectedIndexChanged(object sender, EventArgs e)
        {
            IList<Screwfix> scr = sc.GetAllScrewfixResults();
            IList<ScrewfixCat2> scr2 = sc2.allCat2();
          
            var catId = (int)cbxCat1.ComboBox.SelectedValue;
            cbxCat2.ComboBox.DataSource = scr2.Where(c => c.CatId == catId).ToList();

            dgvLeft.DataSource = scr.Where(s => s.CategoryA.Contains(cbxCat1.Text)).ToList();
            cbxCat2.Enabled = true;

            this.BeginInvoke((MethodInvoker)delegate { cbxCat1.Text = "-Select-"; });//Note Resets combobox after selection
        }

        private void cbxCat2_SelectedIndexChanged(object sender, EventArgs e)
        {
            IList<Screwfix> scr = sc.GetAllScrewfixResults();
            IList<ScrewfixCat3> scr3 = sc3.allCat3();//Second child

            //var cat2Id = (int)cbxCat2.ComboBox.SelectedValue;
            //cbxCat3.ComboBox.DataSource = scr3.Where(c => c.Cat2Id == cat2Id).ToList();

            dgvLeft.DataSource = scr.Where(s => s.CategoryB.Contains(cbxCat2.Text)).ToList();
            cbxCat3.Enabled = true;

            this.BeginInvoke((MethodInvoker)delegate { cbxCat2.Text = "-Select-"; });
        }

        private void cbxCat3_SelectedIndexChanged(object sender, EventArgs e)
        {
            IList<Screwfix> scr = sc.GetAllScrewfixResults();
            dgvLeft.DataSource = scr.Where(s => s.CategoryC.Contains(cbxCat3.Text)).ToList();
            cbxCat3.Enabled = true;
            this.BeginInvoke((MethodInvoker)delegate { cbxCat3.Text = "-Select-"; });
        }

Thank you.
 

Daz66

Member
Joined
May 17, 2020
Messages
22
Programming Experience
Beginner
I haven't looked closely at your code but it sounds like you haven't set the ValueMember.
They're toolstrip combo's so have coded programatically and on form load.
C#:
void cbxCategoryAFill()
        {
            IList<ScrewfixCat1> scr1 = sc1.allCat1();

            cbxCat1.ComboBox.DisplayMember = "Category1";
            cbxCat1.ComboBox.ValueMember = "CatId";
            cbxCat1.ComboBox.DataSource = scr1;
            cbxCat1.Text = "-Select-";
        }
        void cbxCategoryBFill()
        {
            IList<ScrewfixCat2> scr2 = sc2.allCat2();

            cbxCat2.ComboBox.DisplayMember = "Category2";
            cbxCat2.ComboBox.ValueMember = "Cat2Id";
            cbxCat2.ComboBox.DataSource = scr2;
            cbxCat2.Text = "-Select-";
        }
        void cbxCategoryCFill()
        {
            IList<ScrewfixCat3> scr3 = sc3.allCat3();

            cbxCat3.ComboBox.DisplayMember = "Category3";
            cbxCat3.ComboBox.ValueMember = "Cat3Id";
            cbxCat3.ComboBox.DataSource = scr3;
            cbxCat3.Text = "-Select-";
        }
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
Can you show us the declaration of the ScrewfixCat2 class?

(Also as an aside not probably not related to your problem, why are you changing the combobox text back to "-Select-" after a user has made a selection. This goes against Windows UI guidelines, plus it'll wreak havoc for any user who has disabilities and makes use of any kind of screen reader or assistive tools.)
 

Daz66

Member
Joined
May 17, 2020
Messages
22
Programming Experience
Beginner
Can you show us the declaration of the ScrewfixCat2 class?

(Also as an aside not probably not related to your problem, why are you changing the combobox text back to "-Select-" after a user has made a selection. This goes against Windows UI guidelines, plus it'll wreak havoc for any user who has disabilities and makes use of any kind of screen reader or assistive tools.)
Hi, thanks for the tip. In a different form I use this method to give focus back to the combo for searching via typing as well as from drop down. It doesn't work here so will remove.

C#:
namespace Business.ClassLibrary
{
    public class ScrewfixCat2
    {
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Business"].ConnectionString;

        public int Cat2Id { get; set; }
        public string? Category2 { get; set; }
        public int CatId { get; set; }

        public IList<ScrewfixCat2> allCat2()
        {
            List<ScrewfixCat2> li = new();

            using (SqlConnection? con = new(connectionString))
            {
                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlDataAdapter da = new("ScrewfixCat2", con))
                {
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    using (var reader = da.SelectCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ScrewfixCat2 sc = new();
                            sc.Cat2Id = (int)reader["Cat2Id"];
                            sc.Category2 = reader["Category2"].ToString();
                            sc.CatId = (int)reader["CatId"];

                            li.Add(sc);
                        }
                    }
                }
            }
            return li;
        }
    }
}
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
Thanks for posting. I was just trying to make sure that there was no case mismatch between the declared property and the string you are setting for ValueMember.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
I don't know why the ValueMember didn't work correctly and return just the Cat2Id which is an in. Based on the error you described in post #9, it looks like it is returning the entire ScrewfixCat2 object. As a workaround, you could use that to your advantage:
C#:
var cat2Id = ((ScrewfixCat2)cbxCat2.ComboBox.SelectedValue).Cat2Id;
 

Daz66

Member
Joined
May 17, 2020
Messages
22
Programming Experience
Beginner
I don't know why the ValueMember didn't work correctly and return just the Cat2Id which is an in. Based on the error you described in post #9, it looks like it is returning the entire ScrewfixCat2 object. As a workaround, you could use that to your advantage:
C#:
var cat2Id = ((ScrewfixCat2)cbxCat2.ComboBox.SelectedValue).Cat2Id;
The form will load now but there is system info in the cbxCat2 combo.
Yes it's the filtering on the third combo which is the issue. With the attached code all three combo's will load the correct elements, and cbx2 will filter from cbx1 and then error on the cbx3 which makes me think the display/value members are working ok?
C#:
private void cbxCat1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.BeginInvoke((MethodInvoker)delegate { cbxCat1.Text = "-Select-"; });//Note Resets combobox after selection

            IList<Screwfix> scr = sc.GetAllScrewfixResults();
            IList<ScrewfixCat2> scr2 = sc2.allCat2();


            var catId = (int)cbxCat1.ComboBox.SelectedValue;
            cbxCat2.ComboBox.DataSource = scr2.Where(c => c.CatId == catId).ToList();

            dgvLeft.DataSource = scr.Where(s => s.CategoryA.Contains(cbxCat1.Text)).ToList();
            cbxCat2.Enabled = true;
        }

        private void cbxCat2_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.BeginInvoke((MethodInvoker)delegate { cbxCat2.Text = "-Select-"; });

            IList<Screwfix> scr = sc.GetAllScrewfixResults();
            //IList<ScrewfixCat3> scr3 = sc3.allCat3();
            //var cat2Id = (int)cbxCat2.ComboBox.SelectedValue;//Old
            //var cat2Id = ((ScrewfixCat2)cbxCat2.ComboBox.SelectedValue).Cat2Id;//New
            //cbxCat3.ComboBox.DataSource = scr3.Where(c => c.Cat2Id == cat2Id).ToList();

            dgvLeft.DataSource = scr.Where(s => s.CategoryB.Contains(cbxCat2.Text)).ToList();
            cbxCat3.Enabled = true;
        }

        private void cbxCat3_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.BeginInvoke((MethodInvoker)delegate { cbxCat3.Text = "-Select-"; });
            IList<Screwfix> scr = sc.GetAllScrewfixResults();
            dgvLeft.DataSource = scr.Where(s => s.CategoryC.Contains(cbxCat3.Text)).ToList();
            
        }
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
then error on the cbx3
What error are you getting?

Also why are you changing the text of the combobox on line 32?
 

Daz66

Member
Joined
May 17, 2020
Messages
22
Programming Experience
Beginner
What error are you getting?

Also why are you changing the text of the combobox on line 32?
Same as before, I get the error on form load with the original code;
System.InvalidCastException: 'Unable to cast object of type 'System.Int32' to type 'Business.ClassLibrary.ScrewfixCat2'.'

With your code snippet the form loads but the elements don't load in cbx2, i get system info instead, if i click the system info i then get the above error.
Really weird as the 2 combo set up works flawlessly.

I like to default the combo's to 'Select' on load and after selection.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,554
Location
Sydney, Australia
Programming Experience
10+
I like to default the combo's to 'Select' on load and after selection.
Why? Are your users so simple that they don't understand how a basic GUI works? Do you also default a TextBox to "Type"? If you think the users can work out how to do one, why do you think they can't work out how to do the other?
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
With your code snippet the form loads but the elements don't load in cbx2, i get system info instead,
What do you mean "system info"? Was it something like "Business.ClassLibrary.ScrewfixCat2"? If so, it sounds like your DisplayMember seeing doesn't work either. I suspect that your cbxCategoryBFill() was not called. Set a breakpoint on that method to make sure that it is being called.
 
Top Bottom