none
How to make SQL query based on CheckedListBox selection in vb.net RRS feed

  • Question

  • There is a DataGridView on the Form and once I click on a specific column of DataGridView1, a Checkedlistbox will pop up and based on the selection of items in checkedlistbox, it should write SQL query and get only that data (Strictly on selection) on DataGridView1. The code is as below:

    Please know I cannot use the button.

    Private Sub CheckedListBox1_ItemCheck(ByVal sender As Object, ByVal e As System.Windows.Forms.ItemCheckEventArgs) Handles CheckedListBox1.ItemCheck

        Dim i As Integer
        'xx is an array of string, used to store checkedlistbox selection
        Dim xx() As String
        Dim sqlstr2 As String

        For i = 0 To CheckedListBox1.Items.Count - 1 Step i + 1

                If CheckedListBox1.GetItemCheckState(i) = CheckState.Checked Then

                       xx(i) = CheckedListBox1.SelectedValue(i)

               End If
        Next
        sqlstr2 = "SELECT * From Table WHERE [TagName] in (' "%, "' '"& [xx] & "' )"'
        SQL.RunQuery(sqlstr2)

        If SQL.SQLDataset.Tables.Count > 0 Then
            DataGridView1.DataSource = SQL.SQLDataset.Tables(0)

        End If

    End Sub


    Monday, February 11, 2019 11:51 PM

All replies

  • Hi,

    fix the code:

      For i = 0 To CheckedListBox1.Items.Count - 1 Step i + 1
    
                If CheckedListBox1.GetItemCheckState(i) = CheckState.Checked Then
    
                       xx(i) = CheckedListBox1.SelectedValue(i)
    
               End If
        Next
      For i = 0 To CheckedListBox1.Items.Count - 1 Step i + 1
    
            xx(i) = CheckedListBox1.SelectedItem.ToString
        Next

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 12, 2019 2:00 AM
    Moderator
  • First off you can't do a WHERE IN with multiple LIKE conditions, instead you need OR's

    If you want multiple like conditions try this.

    Public Class Form1
        Private CheckedItems As New List(Of String)
        Private Sub CheckedListBox1_ItemCheck(sender As Object, e As ItemCheckEventArgs) _
            Handles CheckedListBox1.ItemCheck
    
            Dim value As String = CheckedListBox1.Items(e.Index).ToString()
    
            If e.NewValue = CheckState.Checked Then
    
                If Not CheckedItems.Contains(value) Then
                    CheckedItems.Add(value)
    
                End If
    
            Else
                CheckedItems.Remove(value)
            End If
    
    
            If CheckedItems.Count > 0 Then
    
                Dim results = CheckedItems.Select(Function(item) $" [TagName] LIKE '%{item}' OR").ToArray()
                Dim selectStatement = $"SELECT * From Table WHERE {String.Join(" ", results)}"
                selectStatement = selectStatement.Substring(0, selectStatement.Length - 3)
                ' we have something to work with
                Console.WriteLine(selectStatement)
            End If
    
        End Sub
    End Class
    

     



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 12, 2019 2:17 AM
    Moderator