Can you debug your program to see your SQL select statement？There must be something syntax wrong with that.
Hope you can debug and copy your sql statement into SQL Management Studio first，if that runs successfully，you can just paste it into IDE of VS and have a try with that.
trev52
Member
36 Points
151 Posts
micorosoft indexing service sql syntax
Nov 19, 2012 01:56 PM|LINK
I am trying to do a search on our web site using the microsoft indexing service and I am getting a syntax error with the following query.
cmdSearch.CommandText = "select doctitle, filename, path, rank, characterization from scope() where CONTAINS('" + searchText + "') order by rank desc ";
The only time there is an issue is when I have two words in the searchText variable. So 'test' would work fine but 'test two' gets this error
Search Error: Incorrect syntax near 'two'. Expected end-of-file, ',', '.', ';', '<', '=', '>', AS, ASC, DESC, IS, IS_NOT, LIKE, NOT_LIKE, GE, LE, NE. SQLSTATE=42000
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: micorosoft indexing service sql syntax
Nov 20, 2012 03:50 AM|LINK
Hi,
You must add a pair of single quote to the specific characters。Sample is：
Select * From xxx Where [ColumnName] In ('A','B','C',',',';')trev52
Member
36 Points
151 Posts
Re: micorosoft indexing service sql syntax
Nov 20, 2012 01:15 PM|LINK
cmdSearchRegister.CommandText = "select doctitle, filename, path, rank, characterization from scope() where CONTAINS('test', 'two')";Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: micorosoft indexing service sql syntax
Nov 21, 2012 12:08 AM|LINK
Hello again,
Can you debug your program to see your SQL select statement？There must be something syntax wrong with that.
Hope you can debug and copy your sql statement into SQL Management Studio first，if that runs successfully，you can just paste it into IDE of VS and have a try with that.
trev52
Member
36 Points
151 Posts
Re: micorosoft indexing service sql syntax
Nov 21, 2012 12:50 PM|LINK
the below code ended up fixing the issue.
cmdSearchRegister.CommandText =
"select doctitle, filename, vpath, rank, characterization from scope() where CONTAINS('\"" + searchTextRegister + "\"') order by rank desc";
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: micorosoft indexing service sql syntax
Nov 22, 2012 12:08 AM|LINK
Glad to hear that, and I'll close the issue.
If you have anything urgent, please feel to feedback by creating new thread.