How do I build a variable search form?-VBForums
Results 1 to 9 of 9

Thread: How do I build a variable search form?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    How do I build a variable search form?

    I am trying to build a search form that loops through my data, searching for what would be a wide variety of criteria. I want to include "AND/OR" operators but I cannot discern a logical way of approaching this. Here is what the search box would look like, but how would I go about looping through each line to search my data by the given criteria?

    Criteria to loop through:

    Firstname = John
    or
    Firstname = Paul
    and
    Lastname = Smith

    So, basically I would like to loop through my data and find anyone named John Smith or Paul Smith.

    Here is some futile attempt at doing this:

    Code:
     For Each strLine As String In TextBox2.Text.Split(vbNewLine)
                Dim SearchFor As String = strLine.ToString
    
                If SearchFor = vbLf & "AND" Or SearchFor = vbLf & "OR" Then
    
                End If
    
                For Each dr As DataRow MyDataTable.Rows
                    If MyDataTable.Rows(RowNumber)(ColumnName) = ItemToFind Then
    
                    End If
                Next
    
            Next
    Also, the "=" operator is subject to change. It could be something like this as well:

    RecordNumber > 1000
    And
    RecordNumber < 2000
    Last edited by Christhemist; Nov 17th, 2016 at 01:53 PM.

  2. #2
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    728

    Re: How do I build a variable search form?

    Assuming your are wanting to restrict what is shown in a control bound to a DataTable, you would use this

    Code:
    dtMyDataTable.DefaultView.RowFilter = mySearchString
    where mySearchString is very much like the Where clause of a SQL statement. Here is a snippet from one I use (note: I should have used a StringBuilder for search). cols is a collection of the columns it should search. byPhrase is a checkbox to determine whether it will find exact phrase or individual words. useAND is a boolean on whether to find any or all the keywords. The wordnum variable was just to get around a bug in one special case of particular keywords.

    Code:
            If searchtext <> "" Then
                Const a = "{cName} LIKE '%{0}%'"
                Const b = "{cName} LIKE %{0}%"
    
                Dim search As String = "("
    
                For Each c As searchColumn In cols
                    If search <> "(" Then search = search & " OR "
                    If c.asNumeric = True Then
                        search = search & b.Replace("{cName}", c.ColumnName)
                    Else
                        search = search & a.Replace("{cName}", c.ColumnName)
                    End If
                Next
                search = search & ")"
    
                Dim sWords() As String
                If byPhrase = True Then
                    sWords = {Trim(searchtext)}
                Else
                    sWords = Trim(searchtext).Split(New Char() {" "c})
                End If
    
                Dim conjunction As String = " AND "
                If Not useAND Then
                    conjunction = " OR "
                End If
    
                Dim wordNum As Integer = 0
                For Each word As String In sWords
                    wordNum = wordNum + 1
                    filter = filter & search.Replace("{0}", word)
                    If sWords.Length > 1 AndAlso sWords.Length <> wordNum Then
                        filter = filter & conjunction
                    End If
                Next
            End If

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,804

    Re: How do I build a variable search form?

    I'm not sure that I agree with the StringBuilder comment. I assume you were thinking it would be more efficient, and it would be, but I also remember some thread where people were looking at the efficiency achieved. Basically, if you are only doing a handfull of concatenations, which is likely in this case, then it's kind of a wash.

    So don't be too hard on yourself about it. StringBuilder probably would be an improvement, but it would only be a noticeable improvement if the search had an abundance of terms. That's fairly unlikely.
    My usual boring signature: Nothing

  4. #4
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    728

    Re: How do I build a variable search form?

    Quote Originally Posted by Shaggy Hiker View Post
    So don't be too hard on yourself about it. StringBuilder probably would be an improvement, but it would only be a noticeable improvement if the search had an abundance of terms. That's fairly unlikely.
    In my case it's not huge I'd guess. Maybe a couple hundred characters if they use all the options. My thinking was more in line with how often it is used since the filter is generated on the fly as someone types, selects various comboboxes, etc.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: How do I build a variable search form?

    your code seems so far different from what I am trying to accomplish. I am not using a rowfilter but if the criteria is "true" then i am checking a checkbox column in the dataTable. Can you simplify your code for me? I apologize I am new to programming...

    Also, what is "useAND"?

  6. #6
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    728

    Re: How do I build a variable search form?

    Quote Originally Posted by Christhemist View Post
    your code seems so far different from what I am trying to accomplish. I am not using a rowfilter but if the criteria is "true" then i am checking a checkbox column in the dataTable. Can you simplify your code for me? I apologize I am new to programming...
    I'd think you could still use the RowFilter and then check your checkbox column for all the rows that are left in the bound DGV, which BTW you said nothing about in your OP

    Simplify it how? What can't you figure out?

    Quote Originally Posted by Christhemist View Post
    Also, what is "useAND"?
    useAND is a boolean on whether to find any or all the keywords.
    In your case, you'd need to do something different since you want to use both OR and AND. I would use 2 textboxes in that case, one for first names and ones for last names though it could be done with just 1 textbox if you had a different delimiter between the first and last names.

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: How do I build a variable search form?

    Rowfilter is not the functionality I am shooting for. I would like to view the selected records and non-selected records side by side. Here is what I pieced together with your code and mine:

    Code:
    For Each strLine As String In TextBox2.Text.Split(vbNewLine)
                Dim SearchFor As String = strLine.ToString
    
                If SearchFor <> "" Then
    
                    Dim conjuction As String = "AND"
    
                    Dim useANd As Boolean
    
                    If Not useAND Then
                        conjuction = "OR"
                    End If
    
                    For Each dr As DataRow In PostalSorter.DtSample.Rows
    
                        If MyDataTable.Rows(RowNumber)(FieldName) = ItemToFind Then
                            DataGridView1(0, DataGridView1.Rows(RowNumber).Index).Value = True
                            DataGridView1.Rows(RowNumber).DefaultCellStyle.ForeColor = Color.Red
                        End If
                    Next
    
    
                End If

  8. #8
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    728

    Re: How do I build a variable search form?

    Quote Originally Posted by Christhemist View Post
    I would like to view the selected records and non-selected records side by side.
    Yet another critical piece missing from your OP. Since nobody but perhaps yourself can read your mind, perhaps you should share ALL the relevant info in one place.

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: How do I build a variable search form?

    This should be it:

    Code:
    For Each strLine As String In TextBox2.Text.Split(vbNewLine)
                Dim SearchFor As String = strLine.ToString
    
                If SearchFor <> "" Then
                    Dim ItemToFind As String = SearchFor.Substring(SearchFor.LastIndexOf("=") + 1).TrimStart
                    Dim ColumnName As String = SearchFor.Substring(SearchFor.FirstIndexOf("=") + 1).TrimStart
                    Dim conjuction As String = "AND"
    
                    Dim useANd As Boolean
    
                    If Not useAND Then
                        conjuction = "OR"
                    End If
    
                    For Each dr As DataRow In PostalSorter.DtSample.Rows
    
                        If MyDataTable.Rows(RowNumber)(ColumnName) = ItemToFind Then
                            DataGridView1(0, DataGridView1.Rows(RowNumber).Index).Value = True
                            DataGridView1.Rows(RowNumber).DefaultCellStyle.ForeColor = Color.Red
                        End If
                    Next
    
    
                End If

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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.