To check listbox value against data in db table

Hi Experts,

I have a table which keep data on Vendor list according to the services they provide,as below;
Vendor Table:
VendorID
TypeSupplier (checkbox)
TypeContractor (checkbox)
ServicePrinting (checkbox)
ServiceOutfitting(checkbox)
ServiceMultimedia(checkbox)
ServiceCivil(checkbox)
ServiceConsultancy(checkbox)

These collection of data is use by Procurement staffs, on order to filter which Vendor is providing certain service. For example;
Vendor A. Supplier. ServiceOutfitting. ServiceCivil.
Vendor B. Contractor. ServiceConsultancy. ServiceMechanical.
Vendor C. Supplier. Contractor. ServiceOutfitting.ServiceCivil. ServiceConsultancy.

I had created a form with 2 listbox. One list box(lsBox1) contain all the services. the other listbox(lsBox2) will contain the service selected from LSBox1.

For example I wanted to know which vendor provide Civil or Mechanical, I will choose ServiceCivil and ServiceMechanical from lsBox1(lsBox2.Items.Add(lsBox1.SelectedItem)),now lsBox2 will have the value member of ServiceCivil and ServiceMechanical. My intention is, upon button click, it would be able to return the value as per the listbox value in lsBox2. In this example, Vendor A, Vendor B, Vendor C.

Any way to accomplish this? Or do you have any better ways on how to do this search(do listbox is the correct way?)?

Thanks all.

Jude

Populate a DataTable in the usual way and bind it to a BindingSource, then bind that to the second ListBox. It will display all vendors by default. Assuming that your first ListBox contains items that exactly match the columns of the table, you can handle the SelectedIndexChanged event of that first ListBox and do this:

vb.net Code:

If ListBox1.SelectedItems.Count = 0 Then
    myBindingSource.Filter = Nothing
Else
    myBindingSource.Filter = String.Join(" OR ", ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True"))
End If

That will create a filter that excludes any vendor that doesn't offer at least one of the specified services. In your case, the filter would be "ServiceCivil = True OR ServiceMechanical = True".

Quote:

---

Originally Posted by jmcilhinney

Populate a DataTable in the usual way and bind it to a BindingSource, then bind that to the second ListBox. It will display all vendors by default. Assuming that your first ListBox contains items that exactly match the columns of the table, you can handle the SelectedIndexChanged event of that first ListBox and do this:

vb.net Code:

If ListBox1.SelectedItems.Count = 0 Then
    myBindingSource.Filter = Nothing
Else
    myBindingSource.Filter = String.Join(" OR ", ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True"))
End If

That will create a filter that excludes any vendor that doesn't offer at least one of the specified services. In your case, the filter would be "ServiceCivil = True OR ServiceMechanical = True".

---

Hi jmcilhinney. Thanks for your prompt reply. Honestly, I am disoriented at the moment. :(

LINQ can be a bit confusing at first and I think that function syntax, which I generally prefer, may also be a bit less beginner-friendly that query syntax. This:

vb.net Code:

ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True")

could be written like this:

vb.net Code:

From s In ListBox1.SelectedItems.Cast(Of String)()
Select $"{s} = True"

which might make things a little clearer, especially if you're familiar with SQL. To make things clearer still, that is almost functionally equivalent to this:

vb.net Code:

Dim criteria As New List(Of String)
 
For Each s As String In ListBox1.SelectedItems
    criteria.Add($"{s} = True")
Next

although it's a bit more efficient because it doesn't create the list first and then enumerate that. This part:

vb.net Code:

$"{s} = True"

is string interpolation and is literally the String.Format method built into the language:

vb.net Code:

String.Format("{0} = True", s)

The String.Join method then just joins each item in the list with the specified separator (" OR ") between each pair. The result is a Boolean expression used to filter the items in the BindingSource.

Quote:

---

Originally Posted by jmcilhinney

LINQ can be a bit confusing at first and I think that function syntax, which I generally prefer, may also be a bit less beginner-friendly that query syntax. This:

vb.net Code:

ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True")

could be written like this:

vb.net Code:

From s In ListBox1.SelectedItems.Cast(Of String)()
Select $"{s} = True"

which might make things a little clearer, especially if you're familiar with SQL. To make things clearer still, that is almost functionally equivalent to this:

vb.net Code:

Dim criteria As New List(Of String)
 
For Each s As String In ListBox1.SelectedItems
    criteria.Add($"{s} = True")
Next

although it's a bit more efficient because it doesn't create the list first and then enumerate that. This part:

vb.net Code:

$"{s} = True"

is string interpolation and is literally the String.Format method built into the language:

vb.net Code:

String.Format("{0} = True", s)

The String.Join method then just joins each item in the list with the specified separator (" OR ") between each pair. The result is a Boolean expression used to filter the items in the BindingSource.

---

Hi jmcilhinney,
So, with those codes, I still need the 2 listBoxs and this codes will be written under ListBox2? Actually, these past few days, I was thinking of other way to achieve my goal by using a combobox as filter..and the result will be displayed in datagrid but..I realized that my database field datatype is "CheckBox", therefore I am stuck on how to match my ComboBox values 0f type string to the match with checkBox..(refer attached pic).Attachment 176599


Thanks.

Quote:

---

Originally Posted by jmcilhinney

LINQ can be a bit confusing at first and I think that function syntax, which I generally prefer, may also be a bit less beginner-friendly that query syntax. This:

vb.net Code:

ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True")

could be written like this:

vb.net Code:

From s In ListBox1.SelectedItems.Cast(Of String)()
Select $"{s} = True"

which might make things a little clearer, especially if you're familiar with SQL. To make things clearer still, that is almost functionally equivalent to this:

vb.net Code:

Dim criteria As New List(Of String)
 
For Each s As String In ListBox1.SelectedItems
    criteria.Add($"{s} = True")
Next

although it's a bit more efficient because it doesn't create the list first and then enumerate that. This part:

vb.net Code:

$"{s} = True"

is string interpolation and is literally the String.Format method built into the language:

vb.net Code:

String.Format("{0} = True", s)

The String.Join method then just joins each item in the list with the specified separator (" OR ") between each pair. The result is a Boolean expression used to filter the items in the BindingSource.

---

Hi jmcilhinney,
So, with those codes, I still need the 2 listBoxs and this codes will be written under ListBox2? Actually, these past few days, I was thinking of other way to achieve my goal by using a combobox as filter..and the result will be displayed in datagrid but..I realized that my database field datatype is "CheckBox", therefore I am stuck on how to match my ComboBox values 0f type string to the match with checkBox..(refer attached pic).
Attachment 176599


Thanks.