Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Full-Text Catalog query

I created a full-text catalog and I am trying to run a query on my database. Basically I have a list of vendors and I want the user to be able to type part of the name and get results. When I have more than one word in the search box I am getting an error. Here is my query

selectCMD.CommandText = "Select test1.vendorName, test2.address from test1, test2 where test1.ID = test2.ID and  CONTAINS(VendorName, '%" & searchobj & "%') order by test1.ID"

I typed bio rad in the textbox and I recieve this error. How can I fix this query to except multiple words and not error out.

Syntax error occurred near 'rad%'. Expected ''''' in search condition '%bio rad%'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Syntax error occurred near 'rad%'. Expected ''''' in search condition '%bio rad%'.

Source Error:


Line 36:       empDA.SelectCommand = selectCMD
Line 37:       dbconn.Open()
Line 38:       empDA.Fill(empDS, "test1")
Line 39:       dbconn.Close()
Line 40:       
0
NickMalloy
Asked:
NickMalloy
  • 6
  • 6
1 Solution
 
rafranciscoCommented:
Try this one:

selectCMD.CommandText = "Select test1.vendorName, test2.address from test1, test2 where test1.ID = test2.ID and  
VendorName LIKE '%" & searchobj & "%') order by test1.ID"

This assumes that vendorname is varchar and not text data type.
0
 
rafranciscoCommented:
Also, try this one:

selectCMD.CommandText = "Select test1.vendorName, test2.address from test1 inner join test2 on test1.ID = test2.ID and  test1.VendorName LIKE '%" & searchobj & "%') order by test1.ID"

or this one:

selectCMD.CommandText = "Select test1.vendorName, test2.address from test1 inner join test2 on test1.ID = test2.ID where  test1.VendorName LIKE '%" & searchobj & "%') order by test1.ID"
0
 
NickMalloyAuthor Commented:
I had it like that and it took forever to query my tables. I have been using

selectCMD.CommandText = "Select test1.vendorName, test2.address from test1, test2 where test1.ID = test2.ID and FREETEXT (vendorName, '%" & searchobj & "%' )  order by test1.ID"

but that doesn't narrow down as well as I would like, but it is super fast. When I used the Like it seems to take forever.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
NickMalloyAuthor Commented:
the type is a varchar
0
 
rafranciscoCommented:
How about this:

selectCMD.CommandText = "Select test1.vendorName, test2.address from test1, test2 where test1.ID = test2.ID and  CONTAINS(VendorName, '\"*" & searchobj & "*\"') order by test1.ID"

The contains part should look like this:

CONTAINS(VendorName, ' "*<searchobj>*" ')
0
 
NickMalloyAuthor Commented:
errors out when more than one word in the box
0
 
rafranciscoCommented:
How about removing the asterisks?

CONTAINS(VendorName, ' "<searchobj>" ')
0
 
NickMalloyAuthor Commented:
same
0
 
rafranciscoCommented:
You do have the double quotes aside from the single quotes around the search string right?  Can you please post the latest command you're trying to execute.
0
 
NickMalloyAuthor Commented:
selectCMD.CommandText = "Select test1.vendorName, test2.address from test1, test2 where test1.ID = test2.ID and  CONTAINS(VendorName, '\" & searchobj & "\') order by test1.ID"
0
 
rafranciscoCommented:
Try this:

selectCMD.CommandText = "Select test1.vendorName, test2.address from test1, test2 where test1.ID = test2.ID and  CONTAINS(VendorName, ' \"" & searchobj & "\" ') order by test1.ID"

I don't know how to escape the double quotation marks but the searchobj should be enclosed first with single quotes then with double quotes.  On the above, I assumed that \" produces a double quote:

CONTAINS(VendorName, ' "<searchobj>" ')

As you can see, there is a single-quote and a double-quote before the search string and a double-quote and a single quote after it.
0
 
NickMalloyAuthor Commented:
selectCMD.CommandText = "Select test1.vendorName, test2.address from test1, test2 where test1.ID = test2.ID and  CONTAINS(VendorName, '""" & searchobj & """') order by test1.ID"
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now