Resolved Combobox Filter

[Daz66]

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.

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]

The commented out LINQ is an attempt to do this but returns errors.

What errors are you getting?

 

[Daz66]

What errors are you getting?

Unable to cast object of type 'System.Collections.Generic.List

 

[Skydiver]

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]

Let's say that you have these types:

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:

private IList<Parent> allParents;
private IList<Child> allChildren;

and bind like this on load:

childComboBox.DisplayMember = "ChildName";
childComboBox.ValueMember = "ChildId";

parentComboBox.DisplayMember = "ParentName";
parentComboBox.ValueMember = "ParentId";
parentComboBox.DataSource = allParents;

and then this:

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]

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

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]

Let's say that you have these types:

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:

private IList<Parent> allParents;
private IList<Child> allChildren;

and bind like this on load:

childComboBox.DisplayMember = "ChildName";
childComboBox.ValueMember = "ChildId";

parentComboBox.DisplayMember = "ParentName";
parentComboBox.ValueMember = "ParentId";
parentComboBox.DataSource = allParents;

and then this:

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]

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]

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?

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.

 

[jmcilhinney]

I haven't looked closely at your code but it sounds like you haven't set the ValueMember.

 

[Daz66]

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.

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]

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]

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.

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]

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]

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:

var cat2Id = ((ScrewfixCat2)cbxCat2.ComboBox.SelectedValue).Cat2Id;