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
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
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.
Re: How do I build a variable search form?
Quote:
Originally Posted by
Shaggy Hiker
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.
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"?
Re: How do I build a variable search form?
Quote:
Originally Posted by
Christhemist
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
Also, what is "useAND"?
Quote:
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.
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
Re: How do I build a variable search form?
Quote:
Originally Posted by
Christhemist
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.
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
