Resolved Connecting List Boxes

[ConsKa]

I am a little better perhaps at VBA than I am c# - a learning process.

In VBA, I am able to connect 3 different textboxes, so that when you select an item in ListBox1 both ListBox2 and ListBox3 populate with the corresponding items (which are then transferred to texboxes for the program to act upon them)

I can do this using ListIndex, but I have not found anything similar in C#. The data comes from a table of 3 columns and are equal in length and the data at entry 4 in Column A corresponds with the data in entry 4 of Column B.

So I have the usual SQL connection code and then

While (dr.Read())
{
    listbox1.Items.Add(dr["Item1"]);
    listbox2.Items.Add(dr["Item2"]);
    listbox3.Items.Add(dr["Item3"]);
}

I have code that allows me to filter by text on listbox1 and listbox2 (listbox3 is actually hidden as I only need the output).

I have looked at the SelectedIndex documentation from MSDN - which works if you have two identical items as it simply searches for the item in the second listbox. But I have Employee ID and Employee Name for example.

I have seen some rather convoluted methods while searching online, which would require me to re-write my entire code base and given that this is the last piece that I need...I would like to avoid re-writing the entire code. Happy to make changes, but would be relieved if those changes were limited.

If you are aware of a ListIndex for C# point me at it, I can usually read up and get something working....but I have spent the last 3 hours googling this problem and I have not found anything that would do the same job.

 

[Skydiver]

I'm not seeing how a SelectedIndex would not work if ListIndex used to work for you. They both represent the same thing: an index into a list.

If the SelectedIndex on listbox1 is 2, then just set SelectedIndex to 2 on listbox2 and listbox3.

In your 5th paragraph above, you mentioned that you are filtering listbox1 and listbox2. If you remove items in of the list boxes, then you should remove the items at the corresponding indices of the other listboxes.

In general, the major reason for your problems is because you are using you UI as both your View and your Model. This used to be the norm back in the 80's and 90's because of limited RAM and CPU forced people to try to only keep data in one place and minimize moving data around. By the 2000's CPU and RAM had increased, as well as programmers have learned that doing this mixing of Model and View together leads to complex code. If you kept your Model separate, and the UI's only job was to reflect the current state of the Model, then you would simply filter down your model to what should be displayed, and your UI would follow suit and display just those items. All the items would be at the correct matching indices.

 

[jmcilhinney]

when you select an item in ListBox1 both ListBox2 and ListBox3 populate with the corresponding items

I don't think that that actually describes what you're doing. Do you actually mean that all the ListBoxes are already populated with items and then selecting an item in one ListBox automatically selects the corresponding items in the others? If so then you don't need any code at all. Data-binding will handle it for you automatically.

You should first add a BindingSource to your form in the designer. You can then populate a DataTable from your data reader and bind that to all your controls via the BindingSource. You can even bind the same data to the TextBoxes you mention and then they will be automatically populated when you make a selection. Any modifications you make will be automatically pushed back to the DataTable and reflected in the bound ListBoxes. E.g.

var table = new DataTable();

table.Load(myDataReader);

myBindingSource.DataSource = table;

listBox1.DisplayMember = "Item1";
listBox1.DataSource = myBindingSource;
listBox2.DisplayMember = "Item2";
listBox2.DataSource = myBindingSource;
listBox3.DisplayMember = "Item3";
listBox3.DataSource = myBindingSource;

textBox1.DataBindings.Add("Text", myBindingSource, "Item1");
textBox2.DataBindings.Add("Text", myBindingSource, "Item2");
textBox3.DataBindings.Add("Text", myBindingSource, "Item3");

 

[jmcilhinney]

Note that, if you want to filter the data, you should set the Filter property of the BindingSource. That will automatically affect any and all controls bound to it.

 

[Skydiver]

+1 for posts #3 and #4. They load the data into a DataTable (a form of a Model), and bind the listboxes (Views) to the DataTable.

 

[ConsKa]

I started with data-binding but when I came to try and filter that by text input into TextBox, I kept getting an error thrown. Which is why I left that to attempt the method I outlined above.

I will go back (fortunately all that is still within VS) and see if I can set the Filter Property...

myBindingSource - is unassigned and I am not entirely sure what it is to reference...sorry it is probably really obvious but its 2am now and things not as clear as they should be - but would really like to get this done if I can.

 

[jmcilhinney]

myBindingSource - is unassigned and I am not entirely sure what it is to reference

You should first add a BindingSource to your form in the designer

myBindingSource is your BindingSource that you added as per my instructions.

 

[ConsKa]

myBindingSource is your BindingSource that you added as per my instructions.

Told you it would be obvious.....apologies...can I just say, in my defence, it reads in VS as DataSource....so I was getting a little thrown...oddly though, had I looked a little harder, I would have seen that the DataSouce is bindingSource1....

 

[jmcilhinney]

I started with data-binding but when I came to try and filter that by text input into TextBox, I kept getting an error thrown.

We'd have to see the code and the error message to know the cause of that but, in general, if you want to filter based on a TextBox then it will be something like this:

myBindingSource.Filter = $"SomeColumn LIKE '{myTextBox.Text}%'";

That will include only records where the value in the SomeColumn column starts with the text in the TextBox. You can obviously add or remove wildcards and use a more complex criteria expression if desired but that's the principle.

One thing to consider is where you set that property. You might do it on the Click of a Button but many will think to do it on the TextChanged of the TextBox. The latter keeps the filter current but the problem is that it will keep changing the filter multiple times as the user types multiple characters, which may degrade the user experience. A solution to that is to start/restart a Timer on TextChanged and then set the Filter on Tick. That way, you can set the Interval so that the user never has to wait appreciably for the filter to happen but it's not so quick that it will happen between characters when typing continuously, e.g.

private void textBox1_TextChanged(object sender, EventArgs e)
{
    // Start/restart the Timer.
    timer1.Stop();
    timer1.Start();
}

private void timer1_Tick(object sender, EventArgs e)
{
    bindingSource1.Filter = $"SomeColumn LIKE '{textBox1.Text}%'";
}

You might consider an Interval in the 200 - 500 range.

 

[ConsKa]

I am not sure I even have that code anymore, as I removed it after being unable to get it to work with data binding.

The code I was using before I moved back to data binding was:

            var dataList = listBox2.Items.Cast<string>().ToList();

            if (dataList.Count > 0)
            {
                listBox2.Items.Clear();

                listBox2.Items.AddRange(dataList.Where(i => i.Contains(hcodbox.Text)).ToArray());
            }

That seemed to work quite well, but no longer works at all.

However, now I have put your code into the Text Changed rather than the SelectedIndex change...I mentioned it was almost 3am now....and added a wildcard * at the start (these are Unique IDs that start with a letter but I wanted to just search by the numbers) it works brilliantly.

I considered the tick, I actually have a clock in the application so could have implemented it....but the update as you type is perfectly fine.

Really appreciate your help, not only did I learn something...but I am actually doing it better - and it wasn't that bad of an update - didn't have to re-write the whole thing...which is a bonus.