-
Nov 17th, 2016, 02:38 PM
#1
Thread Starter
Lively Member
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 02:53 PM.
-
Nov 17th, 2016, 03:02 PM
#2
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
-
Nov 17th, 2016, 03:33 PM
#3
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
-
Nov 17th, 2016, 03:58 PM
#4
Re: How do I build a variable search form?
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.
-
Nov 17th, 2016, 04:08 PM
#5
Thread Starter
Lively Member
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"?
-
Nov 17th, 2016, 04:22 PM
#6
Re: How do I build a variable search form?
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?
Originally Posted by Christhemist
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.
-
Nov 17th, 2016, 04:38 PM
#7
Thread Starter
Lively Member
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
-
Nov 17th, 2016, 04:52 PM
#8
Re: How do I build a variable search form?
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.
-
Nov 17th, 2016, 06:50 PM
#9
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|