SQL Full-Text Search in VB code

[Trevor Fairchild]

I'm trying to execute a full-text query from a vb.net web application.

The problem I have is that in SQL Server, the syntax for a full-text
search is

SELECT *
FROM table
WHERE CONTAINS( *, ' "searchstri ng" ')

For whatever reason, VB won't run that search string unless I eliminate
the double sets of quotes:

SELECT *
FROM table
WHERE CONTAINS( *, "searchstri ng")

This will not work for a search phrase - only single words, and it also
will not work for a wild card:

SELECT *
FROM table
WHERE CONTAINS ( *, "search*")

The above does not work - I must assume that the double quotes are
needed for both wild card searches and exact phrase searches -

The problem of course is that in vb code the SQL string is already
enclosed in search quotes - the single quotes are then used to indicate
double quotes within the search.

I have tried using two single quotes on either side of the string, but
it doesn't seem to work. ie:

"SELECT * FROM table WHERE CONTAINS ( ' '" & textbox1.text & "' ')"

Does anybody have any ideas?

Trevor Fairchild

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bruce Loving]

Re: SQL Full-Text Search in VB code

A: the SQL wildcard is % not * as in Access

B: SQL uses single quotes ' not double "



On 20 Nov 2003 18:50:36 GMT, Trevor Fairchild <barcodeboy3@ao l.com>
wrote:
[color=blue]
>I'm trying to execute a full-text query from a vb.net web application.
>
>The problem I have is that in SQL Server, the syntax for a full-text
>search is
>
>SELECT *
>FROM table
>WHERE CONTAINS( *, ' "searchstri ng" ')
>
>For whatever reason, VB won't run that search string unless I eliminate
>the double sets of quotes:
>
>SELECT *
>FROM table
>WHERE CONTAINS( *, "searchstri ng")
>
>This will not work for a search phrase - only single words, and it also
>will not work for a wild card:
>
>SELECT *
>FROM table
>WHERE CONTAINS ( *, "search*")
>
>The above does not work - I must assume that the double quotes are
>needed for both wild card searches and exact phrase searches -
>
>The problem of course is that in vb code the SQL string is already
>enclosed in search quotes - the single quotes are then used to indicate
>double quotes within the search.
>
>I have tried using two single quotes on either side of the string, but
>it doesn't seem to work. ie:
>
>"SELECT * FROM table WHERE CONTAINS ( ' '" & textbox1.text & "' ')"
>
>Does anybody have any ideas?
>
>Trevor Fairchild
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it![/color]

[Simon Hayes]

Re: SQL Full-Text Search in VB code


"Trevor Fairchild" <barcodeboy3@ao l.com> wrote in message
news:3fbd0cfc$0 $195$75868355@n ews.frii.net...[color=blue]
> I'm trying to execute a full-text query from a vb.net web application.
>
> The problem I have is that in SQL Server, the syntax for a full-text
> search is
>
> SELECT *
> FROM table
> WHERE CONTAINS( *, ' "searchstri ng" ')
>
> For whatever reason, VB won't run that search string unless I eliminate
> the double sets of quotes:
>
> SELECT *
> FROM table
> WHERE CONTAINS( *, "searchstri ng")
>
> This will not work for a search phrase - only single words, and it also
> will not work for a wild card:
>
> SELECT *
> FROM table
> WHERE CONTAINS ( *, "search*")
>
> The above does not work - I must assume that the double quotes are
> needed for both wild card searches and exact phrase searches -
>
> The problem of course is that in vb code the SQL string is already
> enclosed in search quotes - the single quotes are then used to indicate
> double quotes within the search.
>
> I have tried using two single quotes on either side of the string, but
> it doesn't seem to work. ie:
>
> "SELECT * FROM table WHERE CONTAINS ( ' '" & textbox1.text & "' ')"
>
> Does anybody have any ideas?
>
> Trevor Fairchild
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

I would guess that you have an issue with escaping the different type of
quotes in VB. If you build the complete command in VB then print it, does it
have the correct SQL Server syntax? It's not completely clear from your post
if you've tried this. You may need one of the following (untested):

/* Simple phrase */
"SELECT * FROM table WHERE CONTAINS ( *, ' """ & textbox1.text & """ ')"
/* Single word */
"SELECT * FROM table WHERE CONTAINS ( *, '" & textbox1.text & "')"

Simon

[Trevor Fairchild]

Re: SQL Full-Text Search in VB code

I found the answer elsewhere.

Thank you, though.

I had to use Chr(34), which is the ASCII code for a double quote, to
force an insert of double quotes into the SQL string on either side of
the search criteria.

It should be noted that I could only get the * working as a wildcard - %
did not work in VB.NET.

The Full-Text syntax specifically stated that the search criteria was
double-quoted, and then the search string Plus the double-quotes were
then surrounded by single quotes (I do not know why):

This is what I had to do to get it working:

command.Command Text = "SELECT * FROM table WHERE CONTAINS ( ' " &
chr(34) & "mySearchHe re" & chr(34) & " ' )"

This comes out looking like:

SELECT * FROM table WHERE CONTAINS( ' "mySearchHe re" ')

And that is how the help in SQL Server 2000 showed it...

Thanks, everyone!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!