2nd Combo box data not updating properly in windows form app..

Question

I've been trying to create this fairly simple windows form application which consists of two combo boxes and a datagridview (DGV).
The first time this app loads it should populate the 1st combo box with unique data from the 4th column of a excel workbook and then when the user chooses a item from the 1st combo box
then using the text of the 1st combo box the excel data should be filtered and shown on the DGV and the 2nd combo box should be filled with column 2 data (filtered based on the combo box 1 text) of the workbook.
After then if the user chooses a value from the 2nd combo box then the DGV should show the updated data (i.e. filtered data as per both items seleted in both combo boxes).

Now, I want the user to have both the options to type the data(with autocomplete functionality) on the combo boxes as well as select from the dropdown list. But the existing AutoComplete functionality only supports searching by prefix, so I've added a file called AutoCompleteBehavior.cs (code shown below) to do the autocomplete while typing in combo boxes.

using System;  
using System.Diagnostics;  
using System.Linq;  
using System.Windows.Forms;  
  
  
public class AutoCompleteBehavior  
{  
 private readonly ComboBox comboBox;  
    private string previousSearchterm;  
  
    private object[] originalList;  
  
    public AutoCompleteBehavior(ComboBox comboBox)  
    {  
        this.comboBox = comboBox;  
        this.comboBox.AutoCompleteMode = AutoCompleteMode.Suggest; // crucial otherwise exceptions occur when the user types in text which is not found in the autocompletion list  
        this.comboBox.TextChanged += this.OnTextChanged;  
        this.comboBox.KeyPress += this.OnKeyPress;  
        this.comboBox.SelectionChangeCommitted += this.OnSelectionChangeCommitted;  
    }  
  
    private void OnSelectionChangeCommitted(object sender, EventArgs e)  
    {  
        if (this.comboBox.SelectedItem == null)  
        {  
            return;  
        }  
  
        var sel = this.comboBox.SelectedItem;  
        this.ResetCompletionList();  
        this.comboBox.SelectedItem = sel;  
    }  
  
    private void OnTextChanged(object sender, EventArgs e)  
    {  
        if (!string.IsNullOrEmpty(this.comboBox.Text) || !this.comboBox.Visible || !this.comboBox.Enabled)  
        {  
            return;  
        }  
  
        this.ResetCompletionList();  
    }  
  
    private void OnKeyPress(object sender, KeyPressEventArgs e)  
    {  
        if (e.KeyChar == '\r' || e.KeyChar == '\n')  
        {  
            e.Handled = true;  
            if (this.comboBox.SelectedIndex == -1 && this.comboBox.Items.Count > 0  
                && this.comboBox.Items[0].ToString().ToLowerInvariant().StartsWith(this.comboBox.Text.ToLowerInvariant()))  
            {  
                this.comboBox.Text = this.comboBox.Items[0].ToString();  
            }  
  
            this.comboBox.DroppedDown = false;  
  
            // Guardclause when detecting any enter keypresses to avoid a glitch which was selecting an item by means of down arrow key followed by enter to wipe out the text within  
            return;  
        }  
  
        // Its crucial that we use begininvoke because we need the changes to sink into the textfield  Omitting begininvoke would cause the searchterm to lag behind by one character  That is the last character that got typed in  
        this.comboBox.BeginInvoke(new Action(this.ReevaluateCompletionList));  
    }  
  
    private void ResetCompletionList()  
    {  
        this.previousSearchterm = null;  
        try  
        {  
            this.comboBox.SuspendLayout();  
  
            if (this.originalList == null)  
            {  
                this.originalList = this.comboBox.Items.Cast<object>().ToArray();  
            }  
  
            if (this.comboBox.Items.Count == this.originalList.Length)  
            {  
                return;  
            }  
  
            while (this.comboBox.Items.Count > 0)  
            {  
                this.comboBox.Items.RemoveAt(0);  
            }  
  
            this.comboBox.Items.AddRange(this.originalList);  
        }  
        finally  
        {  
            this.comboBox.ResumeLayout(true);  
        }  
    }  
  
    private void ReevaluateCompletionList()  
    {  
        var currentSearchterm = this.comboBox.Text.ToLowerInvariant();  
        if (currentSearchterm == this.previousSearchterm)  
        {  
            return;  
        }  
  
        this.previousSearchterm = currentSearchterm;  
        try  
        {  
            this.comboBox.SuspendLayout();  
  
            if (this.originalList == null)  
            {  
                this.originalList = this.comboBox.Items.Cast<object>().ToArray(); // backup original list  
            }  
  
            object[] newList;  
            if (string.IsNullOrEmpty(currentSearchterm))  
            {  
                if (this.comboBox.Items.Count == this.originalList.Length)  
                {  
                    return;  
                }  
  
                newList = this.originalList;  
            }  
            else  
            {  
                newList = this.originalList.Where(x => x.ToString().ToLowerInvariant().Contains(currentSearchterm)).ToArray();  
            }  
  
            try  
            {  
                // clear list by loop through it otherwise the cursor would move to the beginning of the textbox  
                while (this.comboBox.Items.Count > 0)  
                {  
                    this.comboBox.Items.RemoveAt(0);  
                }  
            }  
            catch  
            {  
                try  
                {  
                    this.comboBox.Items.Clear();  
                }  
                catch (Exception ex)  
                {  
                    Debug.WriteLine(ex.Message);  
                }  
            }  
  
            this.comboBox.Items.AddRange(newList.ToArray()); // reset list  
        }  
        finally  
        {  
            if (currentSearchterm.Length >= 1 && !this.comboBox.DroppedDown)  
            {  
                this.comboBox.DroppedDown = true; // if the current searchterm is empty we leave the dropdown list to whatever state it already had  
                Cursor.Current = Cursors.Default; // workaround for the fact the cursor disappears due to droppeddown=true  This is a known bu.g plaguing combobox which microsoft denies to fix for years now  
                this.comboBox.Text = currentSearchterm; // Another workaround for a glitch which causes all text to be selected when there is a matching entry which starts with the exact text being typed in  
                this.comboBox.Select(currentSearchterm.Length, 0);  
            }  
  
            this.comboBox.ResumeLayout(true);  
        }  
    }  
}  

And my actual windows form app code is below:

using System;  
using System.Collections.Generic;  
using System.Data;  
using System.Drawing;  
using System.Linq;  
using System.Windows.Forms;  
using OfficeOpenXml;  
  
namespace ComboBoxAutocomplete  
{  
  
 public partial class MyForm : Form  
 {  
 string fname= @"C:\Users\USER4848\Desktop\testbook.xlsx";  
  
 public MyForm()  
 {  
  
 InitializeComponent();  
  
 ExcelPackage.LicenseContext =LicenseContext.NonCommercial;  
  
 }  
 void MyFormLoad(object sender, EventArgs e)  
 {  
  
 int lastRow = 0;  
  
 using (ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(fname), false))  
 {  
 ExcelWorksheet mainSheet = package.Workbook.Worksheets[0];  
  
 for (int i = 2; i <= mainSheet.Dimension.End.Row; i++)  
 {  
 if (!string.IsNullOrEmpty(mainSheet.Cells["D"+i].Text))  
 {  
 lastRow =i;  
 }  
 }  
 List<string> party = new List<string>();  
  
 for (int row = 2; row <= lastRow; row++)  
 {  
 if (!string.IsNullOrEmpty(mainSheet.Cells[row, 4].Text))  
 {  
 party.Add(mainSheet.Cells[row, 4].Text);  
 }  
 }  
  
  
 foreach (var element in party.Distinct())  
 {  
 comboBox1.Items.Add(element);  
 }  
  
 }  
  
 new AutoCompleteBehavior(this.comboBox1);  
 new AutoCompleteBehavior(this.comboBox2);  
 }  
 void ComboBox1SelectedIndexChanged(object sender, EventArgs e)  
 {  
 dataGridView1.DataSource=null;  
 comboBox2.Items.Clear();  
 comboBox2.Text="";  
  
 int rowno = 0;  
  
 string kword = comboBox1.Text;  
  
  
 using (ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(fname), false))  
 {  
 ExcelWorksheet workSheet = package.Workbook.Worksheets[0];  
  
 for (int i = 2; i <= workSheet.Dimension.End.Row; i++)  
 {  
 if (!string.IsNullOrEmpty(workSheet.Cells["D"+i].Text))  
 {  
 rowno =i;  
 }  
 }  
  
 DataTable dtTemp = new DataTable();  
  
 dtTemp.Columns.Add("Company");  
 dtTemp.Columns.Add("Name");  
 dtTemp.Columns.Add("Surname");  
 dtTemp.Columns.Add("Dept.");  
 dtTemp.Columns.Add("Salary Date");  
 dtTemp.Columns.Add("Remarks");  
 dtTemp.Columns.Add("Payment Released on");  
  
 DataRow drAddItem;  
 for (int row = 2; row <= rowno; row++)  
 {  
  
 if (workSheet.Cells[row, 4].Text == kword)  
 {  
  
 object col1Value = workSheet.Cells[row, 4].Text;  
 object col2Value = workSheet.Cells[row, 2].Text;  
 object col3Value = workSheet.Cells[row, 3].Text;  
 object col4Value = workSheet.Cells[row, 1].Text;  
 object col5Value = workSheet.Cells[row, 12].Text;  
 object col6Value = workSheet.Cells[row, 11].Text;  
 object col7Value = workSheet.Cells[row, 13].Text;  
  
 drAddItem = dtTemp.NewRow();  
  
 drAddItem["Company"] = col1Value.ToString();  
 drAddItem["Name"] = col2Value.ToString();  
 drAddItem["Surname"] = col3Value.ToString();  
 drAddItem["Dept."] = col4Value.ToString();  
 drAddItem["Salary Date"] = col5Value.ToString();  
 drAddItem["Remarks"] = col6Value.ToString();  
 drAddItem["Payment Released on"] = col7Value.ToString();  
  
 dtTemp.Rows.Add(drAddItem);  
  
 comboBox2.Items.Add(col2Value);  
  
 }  
 }  
 dataGridView1.DataSource=dtTemp;  
 }  
 }  
 void ComboBox2SelectedIndexChanged(object sender, EventArgs e)  
 {  
 dataGridView1.DataSource=null;  
  
 int rowno = 0;  
  
 string kword = comboBox1.Text;  
 string kword2 = comboBox2.Text;  
  
  
 using (ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(fname), false))  
 {  
 ExcelWorksheet workSheet = package.Workbook.Worksheets[0];  
  
 for (int i = 2; i <= workSheet.Dimension.End.Row; i++)  
 {  
 if (!string.IsNullOrEmpty(workSheet.Cells["D"+i].Text))  
 {  
 rowno =i;  
 }  
 }  
  
 DataTable dtTemp = new DataTable();  
  
 dtTemp.Columns.Add("Company");  
 dtTemp.Columns.Add("Name");  
 dtTemp.Columns.Add("Surname");  
 dtTemp.Columns.Add("Dept.");  
 dtTemp.Columns.Add("Salary Date");  
 dtTemp.Columns.Add("Remarks");  
 dtTemp.Columns.Add("Payment Released on");  
  
 DataRow drAddItem;  
 for (int row = 2; row <= rowno; row++)  
 {  
  
 if (workSheet.Cells[row, 4].Text == kword && workSheet.Cells[row, 2].Text == kword2)  
 {  
  
 object col1Value = workSheet.Cells[row, 4].Text;  
 object col2Value = workSheet.Cells[row, 2].Text;  
 object col3Value = workSheet.Cells[row, 3].Text;  
 object col4Value = workSheet.Cells[row, 1].Text;  
 object col5Value = workSheet.Cells[row, 12].Text;  
 object col6Value = workSheet.Cells[row, 11].Text;  
 object col7Value = workSheet.Cells[row, 13].Text;  
  
 drAddItem = dtTemp.NewRow();  
  
 drAddItem["Company"] = col1Value.ToString();  
 drAddItem["Name"] = col2Value.ToString();  
 drAddItem["Surname"] = col3Value.ToString();  
 drAddItem["Dept."] = col4Value.ToString();  
 drAddItem["Salary Date"] = col5Value.ToString();  
 drAddItem["Remarks"] = col6Value.ToString();  
 drAddItem["Payment Released on"] = col7Value.ToString();  
  
 dtTemp.Rows.Add(drAddItem);  
  
 }  
 }  
 dataGridView1.DataSource=dtTemp;  
 }  
  
 }  
 void ComboBox1Leave(object sender, EventArgs e)  
 {  
 var indx = comboBox1.FindStringExact(comboBox1.Text);  
 if(indx <= -1)  
 {  
 MessageBox.Show(this,"Please type/select a valid Company Name","Invalid Company Name",MessageBoxButtons.OK,MessageBoxIcon.Error);  
 comboBox1.Text="";  
 return;  
 }  
 }  
 void ComboBox2Leave(object sender, EventArgs e)  
 {  
 var indx = comboBox2.FindStringExact(comboBox2.Text);  
 if(indx <= -1)  
 {  
 MessageBox.Show(this,"Please type/select a valid employee Name","Invalid Name",MessageBoxButtons.OK,MessageBoxIcon.Error);  
 comboBox2.Text="";  
 return;  
 }  
 }  
  
 }  
}  

The first time I run the application and select/type & select using autocomplete item from combo box 1 and then from combo box 2, everything seems to work fine but after that when I change the combo box 1 item the combo box 2 items are not updated properly.

Please help !!!

BTW, the excel file looks something like this:

Answer 1

@Boom Ba , based on my test, I reproduced your problem and find that the class AutoCompleteBehavior may have some problems.

If I remove the following code, the code will work properly.

    //new AutoCompleteBehavior(this.comboBox1);  
    //new AutoCompleteBehavior(this.comboBox2);  

Result:

Note: It seems that you do the same thing in AutoCompleteBehavior class and your current event.

---

If the response is helpful, please click "Accept Answer" and upvote it.

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.