Results 1 to 6 of 6

Thread: To check listbox value against data in db table

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2016
    Posts
    16

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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:
    1. If ListBox1.SelectedItems.Count = 0 Then
    2.     myBindingSource.Filter = Nothing
    3. Else
    4.     myBindingSource.Filter = String.Join(" OR ", ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True"))
    5. 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".

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jun 2016
    Posts
    16

    Re: To check listbox value against data in db table

    Quote Originally Posted by jmcilhinney View Post
    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:
    1. If ListBox1.SelectedItems.Count = 0 Then
    2.     myBindingSource.Filter = Nothing
    3. Else
    4.     myBindingSource.Filter = String.Join(" OR ", ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True"))
    5. 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.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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:
    1. ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True")
    could be written like this:
    vb.net Code:
    1. From s In ListBox1.SelectedItems.Cast(Of String)()
    2. 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:
    1. Dim criteria As New List(Of String)
    2.  
    3. For Each s As String In ListBox1.SelectedItems
    4.     criteria.Add($"{s} = True")
    5. 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:
    1. $"{s} = True"
    is string interpolation and is literally the String.Format method built into the language:
    vb.net Code:
    1. 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.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jun 2016
    Posts
    16

    Re: To check listbox value against data in db table

    Quote Originally Posted by jmcilhinney View Post
    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:
    1. ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True")
    could be written like this:
    vb.net Code:
    1. From s In ListBox1.SelectedItems.Cast(Of String)()
    2. 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:
    1. Dim criteria As New List(Of String)
    2.  
    3. For Each s As String In ListBox1.SelectedItems
    4.     criteria.Add($"{s} = True")
    5. 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:
    1. $"{s} = True"
    is string interpolation and is literally the String.Format method built into the language:
    vb.net Code:
    1. 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).Name:  pic.jpg
Views: 114
Size:  19.9 KB


    Thanks.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jun 2016
    Posts
    16

    Re: To check listbox value against data in db table

    Quote Originally Posted by jmcilhinney View Post
    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:
    1. ListBox1.SelectedItems.Cast(Of String)().Select(Function(s) $"{s} = True")
    could be written like this:
    vb.net Code:
    1. From s In ListBox1.SelectedItems.Cast(Of String)()
    2. 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:
    1. Dim criteria As New List(Of String)
    2.  
    3. For Each s As String In ListBox1.SelectedItems
    4.     criteria.Add($"{s} = True")
    5. 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:
    1. $"{s} = True"
    is string interpolation and is literally the String.Format method built into the language:
    vb.net Code:
    1. 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).
    Name:  pic.jpg
Views: 114
Size:  19.9 KB


    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
  •  



Click Here to Expand Forum to Full Width