[RESOLVED] Multiple Keyword Search in SQL Database

[bennerzuk]

Hi all,
FIrstly, thanks for taking the time to read my post. I have been hunting the net for a couple of days now and have a problem I can not seem to resolve. I think in escence it is straight forward for a seasoned coder, I on the otherhand am still learning....

Put simply, I am trying to search one column (Titles) of an Access database using an SQL query. The search entry comes from a split string passed to an array and inserted into the SQL command.
This works well, however, the results I get back are for entries containing EITHER of the keywords entered, not entries containing ALL keywords entered.

The code I am using is::::

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
dgvSearch.Visible = True
Dim GODSSearch As String = tbSearch.Text
If IsNumeric(GODSSearch) Then
ds.Tables("MyTable").DefaultView.RowFilter = "PageID =" & GODSSearch
Else
Dim mysearch As String = tbSearch.Text
Dim words() As String = mysearch.Split(" "c)

For Each word As String In words
ds.Tables("MyTable").DefaultView.RowFilter = "Title LIKE '%" & word & "%'"
Next
End If
End Sub

The loop is working, but acting as a new search as opposed to searching the results of the previous search.

I would personally kiss the feet of anyone who can assist me with this issue, I have about 4 remaining hairs on my head which are not likely to last the day!!

Thanks again

Ben

[jmcilhinney]

In your loop, you are replacing the previous filter each time, so only the last condition will be used once the loop finishes. You need to combine all the conditions into one filter string. Use your loop to add the conditions to a List, then use String.Join to join them all together with AND operators between them.

[bennerzuk]

Hi there,
Thanks ever so much for swift reply!! I would just like to ask you one further question if I may. I have tried splitting the string before and combining them into the filter string. The issue I had is that the user could type an unknown number of words into the search box. Could this be covered by assuming a maximum number of words eg. 5 and passing a NULL value also to each (I have looked into your previous posts regarding methods for this), or is there a way of passing an "n" number to the query. Or even doing a wordcount on the entry string and coding a condition where this many AND operators get automatically added?? This would be way above my level of knowledge, but I can continue to look this up if I know which way I am pointing!!

Thanks again

Ben

[jmcilhinney]

Have you tried to do as I posted previously? I can only assume not, because if you had you would know that it already covers your latest question.

[bennerzuk]

Thanks again for your reply, you are dead right!! I think this is due to me not understanding the way in which I am meant to do this.
I could try to explain all of the ways I have been trying to do this, but it is probably easier for me to show you the code so you can tear me apart!!!

 Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        dgvSearch.Visible = True
        Dim GODSSearch As String = tbSearch.Text
        If IsNumeric(GODSSearch) Then
            ds.Tables("MyTable").DefaultView.RowFilter = "PageID =" & GODSSearch
        Else

            Dim mysearch As String = tbSearch.Text
            Dim words() As String = mysearch.Split(" ")
            Dim searchstring As String = " "
            If String.IsNullOrEmpty(searchstring) = True Then Exit Sub
            For Each word As String In words
                searchstring = String.Join(" ", words) AND
            Next
            ds.Tables("MyTable").DefaultView.RowFilter = "Title LIKE '%" & searchstring & "%'"
        End If
    End Sub

[jmcilhinney]

Can you explain to me what String.Join does, which would include what the two parameters are actually for?

[bennerzuk]

The string.join I got from another site - I am learning from scratch, so have to look up everything I do. I found the below giodence in another site:

VB.NET - Method - Join
Returns a string created by joining a number of substrings contained in an array.

String.Join(delimiter,list)
delimiter - String character used to separate the substrings in the returned string.
list - One-dimensional array containing substrings to be joined.

eg code:

Dim astrFruit() As String
ReDim astrFruit(2)
astrFruit(0) = "apple"
astrFruit(1) = "peach"
astrFruit(2) = "pear"
Dim strAllFruit As String
strAllFruit = String.Join("|", astrFruit)
' strAllFruit now contains "apple|peach|pear"

Supposedly this was to join strings together from an array ( in my case words()) to be passed to the SQL query. Maybe I have also misunderstood what the above help was for, but it actually seemed fairly logical at the time.....

[jmcilhinney]

So, to be concise, String.Join takes a delimiter string and a list of values and returns a single string containing all those values with the delimiter between each pair. Does that sound reasonable? So, in your case, what is the delimiter string and what is the list of values?

[bennerzuk]

Well, to me it sounds like I am getting a string of text, seperating it by the " " delimiter, then getting those words and joining them back to a string of text with a " " delimiter, thefore giving me exactly what I had in the first instance?!?!?
In my case the delimiter that I use to split the search string is a space " "
The list of values is each word the user types into the search box.
As far as joining them together again to use them in the SQL search - that is where I draw the blank. Do I use the space, comma, nothing??
I would not consider myself to be ridiculously stupid, but I do get the feeling I am coming across that way due to my inability to wrap my mind around this - so I do appreciate your patience!!

[jmcilhinney]

You appear not to have read post #2 too carefully:

use String.Join to join them all together with AND operators between them

Also, where have you done this?

Use your loop to add the conditions to a List

[bennerzuk]

Hi there,
Thanks for your help with this, just wanted to close off with what I found to be the simplest working code:

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        dgvSearch.Visible = True
        dgvSearch.Select()
        Dim GODSSearch As String = tbGODSSearch.Text
        If IsNumeric(GODSSearch) Then
            ds.Tables("MyTable").DefaultView.RowFilter = "PageID =" & GODSSearch
        Else
            Dim mysearch As String = tbSearcher.Text
            Dim array() As String = mysearch.Split(" "c)
            array = mysearch.Split(" "c)
            Dim searchstring As String = ""
            Dim searchlist As String = ""
            Dim word As String

            For Each word In array
                ds.Tables("MyTable").DefaultView.RowFilter = "Title LIKE '%" & word & "%'"
            Next
        End If

    End Sub

Thanks again for your help in getting me to this point!!

Ben

[jmcilhinney]

That code will not work because you are still not doing what I said in my previous post and several times before.

[bennerzuk]

That was dumb!! Sorry, put old code in - actual code was..

            Dim mysearch As String = tbSearch.Text
            Dim i As Integer
            Dim aryTextFile() As String
            Dim searchstring As String = ""
            Dim searchlist As String = ""
            aryTextFile = mysearch.Split(" ")

            For i = 0 To UBound(aryTextFile)
                searchstring = "'%" & aryTextFile(i) & "%'"
                searchlist = searchlist + " (Title) LIKE " + searchstring
                If i <> UBound(aryTextFile) Then
                    searchlist = searchlist + " AND"
                End If
            Next i

            Debug.Print(searchlist)
            ds.Tables("MyTable").DefaultView.RowFilter = searchlist

Thanks again!