asked on

Full-Text search multiple keywords

Hello Experts:

I've setup a simple search app for my website, but I'm having trouble getting multiple keyword search to work. I added a full-text index to the table I'm searching. I search keywords on four different columns in my table, so I'd like to search multiple keywords on each of those columns. Here is the select statement I use to populate my gridview:

jobpost.SelectCommand = "select [jobid], [jobtitle], [companyname], [salaryrange1], [salaryrange2], [commissionamount], " & _
            "[jobcity], [jobstate], [jobindustry], [jobstatus], [jobpostdate], [specialties] FROM [jobpost] " & _
                "where CONTAINS((jobtitle, companyname, jobcity, jobstate), '" & strtbSearch & "') and (jobindustry like '" + ddlJobIndustry.SelectedValue + "'" & _
            " and specialties like '" + ddlSpecialties.SelectedValue + "' )"

Open in new window

Here is the definition of strtbSearch:

If tbKeywords.Text Is Nothing Then
            strtbSearch = """"
        Else
            strtbSearch = tbKeywords.Text.Replace(" ", " AND ")
        End If

Open in new window

I'm not sure how to get it to return all rows if the user leaves the textbox blank. I'm also not sure how to get multiple keywords working. I tried replacing spaces with " AND ", but this syntax doesn't work, it returns nothing.

How do I use CONTAINS() to search multiple columns on multiple keywords?

jay-are

ASKER

Solved the empty textbox search by changing the If/Then to:

If tbKeywords.Text = Nothing Then
            strtbSearch = "''"
        Else
            strtbSearch = tbKeywords.Text.Replace(" ", " OR ")
        End If

Open in new window

Still unable to figure out how to search multiple keywords across multiple columns.

SOLUTION