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 + "' )"
Here is the definition of strtbSearch:
If tbKeywords.Text Is Nothing Then
strtbSearch = """"
strtbSearch = tbKeywords.Text.Replace(" ", " AND ")
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?