-
Apr 21st, 2020, 03:57 AM
#1
Thread Starter
Junior Member
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
-
Apr 21st, 2020, 04:12 AM
#2
Re: To check listbox value against data in db table
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".
-
Apr 21st, 2020, 10:39 PM
#3
Thread Starter
Junior Member
Re: To check listbox value against data in db table
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.
-
Apr 21st, 2020, 11:18 PM
#4
Re: To check listbox value against data in db table
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:
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.
-
Apr 29th, 2020, 03:44 AM
#5
Thread Starter
Junior Member
Re: To check listbox value against data in db table
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:
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).
Thanks.
-
Apr 29th, 2020, 03:45 AM
#6
Thread Starter
Junior Member
Re: To check listbox value against data in db table
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:
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).
Thanks.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|