fulltext search syntax on thesarus search

vb.net 2008
fulltext searching  sql server express 2005

I have a select statement with thid WHERE clause

WHERE CONTAINS(RICHTEXT, 'FORMSOF(Thesaurus, @param4 )')

This is executing ok but no results ?   is the syntax correct ?

works fine if i execute it ok...this way in query builder.
SELECT [ITEM],[WWGDESC],[RICHTEXT]
  FROM WwgDescRich
  WHERE CONTAINS(RICHTEXT, 'FORMSOF(Thesaurus, "JOHNNY BRUSH")')

LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot use variables like that directly in there.
you will need dynamic sql to build up the entire sql, unfortunaltey.
0
FordraidersAuthor Commented:
I use this ?
WHERE contains ([RICHTEXT], @param4 )  now another sql and its works fine...

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
still, no, see here:
http://msdn.microsoft.com/en-us/library/ms187787.aspx
'<contains_search_condition>'

is a fixed sql, no variable allowed there.

you need to do like this:
declare @sql varchar(1000)

set @sql = 'SELECT [ITEM],[WWGDESC],[RICHTEXT]
  FROM WwgDescRich
  WHERE CONTAINS(RICHTEXT, 'FORMSOF(Thesaurus, "' + replace(replace(@value4, '"', '""'), '''','''''') + '")')

exec (@sql)

Open in new window

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

FordraidersAuthor Commented:
hows his then ?

strsql = strsql + " WHERE CONTAINS(RICHTEXT, 'FORMSOF(Thesaurus, '" & nf1 & "')')"

??
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, but you still need the "replace" code part to ensure to handle any quotes
0
FordraidersAuthor Commented:
ok, why is this not working..
Dim da As SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
Dim ds As DataSet = New DataSet
Dim constr As String = _
  "Data Source=.\sqlexpress;Initial Catalog=DescMatch;User ID=sa;Password=veeger"
Dim con3 As SqlConnection = New SqlConnection(constr)


Dim cmdSelect As SqlCommand = New SqlCommand("Select ITEM, WWGDESC, RICHTEXT FROM WwgDescRich WHERE CONTAINS(RICHTEXT, 'FORMSOF(Thesaurus, '" & nf1 & "' )')", con3)

da.SelectCommand = cmdSelect
da.Fill(ds, "WwgDescRich")
DataGridView1.DataSource = ds.Tables(0)
TextBox2.Text = ds.Tables(0).Rows.Count



Dim da As SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
Dim ds As DataSet = New DataSet
Dim constr As String = _
  "Data Source=.\sqlexpress;Initial Catalog=DescMatch;User ID=sa;Password=veeger"
Dim con3 As SqlConnection = New SqlConnection(constr)


Dim cmdSelect As SqlCommand = New SqlCommand("Select ITEM, WWGDESC, RICHTEXT FROM WwgDescRich WHERE CONTAINS(RICHTEXT, 'FORMSOF(Thesaurus, '" & nf1 & "' )')", con3)

da.SelectCommand = cmdSelect
da.Fill(ds, "WwgDescRich")
DataGridView1.DataSource = ds.Tables(0)
TextBox2.Text = ds.Tables(0).Rows.Count

Open in new window

0
Anthony PerkinsCommented:
There is no need to use Dynamic SQL, you can use a local variable like this:
DECLARE @SearchParam nvarchar(200)

SET @SearchParam = 'FORMSOF(Thesaurus, ' + @param4 + ')'

SELECT  ITEM,
        WWGDESC,
        RICHTEXT
FROM    WwgDescRich
WHERE   CONTAINS (RICHTEXT, @SearchParam) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
acperkins,

Does this go in sql servexpress "stored procedure"  or in vb.net code event ?
0
FordraidersAuthor Commented:
acperkins posting code hold on.
0
FordraidersAuthor Commented:
here is code:
acperkins, I have this thus far...

In sql server expres stored procedure:
USE [DescMatch]
GO
/****** Object:  StoredProcedure [dbo].[get_FullTextSearch_Trade_SAP_Data]    Script Date: 04/20/2010 14:27:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		&lt;Author,,Name&gt;
-- Create date: &lt;Create Date,,&gt;
-- Description:	&lt;Description,,&gt;
-- =============================================
ALTER PROCEDURE [dbo].[get_FullTextSearch_Trade_SAP_Data] @param4 nvarchar(100)  
AS	
DECLARE @SearchParam nvarchar(200) 
SET @SearchParam = 'FORMSOF(Thesaurus, ' + @param4 + ')' 
SELECT  ITEM, 
        WWGDESC, 
        RICHTEXT 
FROM    WwgDescRich 
WHERE   CONTAINS (RICHTEXT, @SearchParam) 
GO
:
in vb.net
Private con As New SqlConnection
    Private con2 As New SqlConnection
       Private dxt2 As DataTable
    Private sql_command2 As SqlCommand
    Private sql_param2 As SqlParameter
    Private dxa2 As SqlDataAdapter

nfl  =  SearchBoxText.text


' Xref Data Full Text Trade Names
        con2.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=DescMatch;User ID=sa;Password=veeger"
        con2.Open()
        sql_command2 = New SqlCommand("get_FullTextSearch_Trade_SAP_Data", con2)
        sql_command2.CommandType = CommandType.StoredProcedure
        sql_param2 = sql_command2.Parameters.Add("@param4", SqlDbType.NVarChar)
        dxa2 = New SqlDataAdapter(sql_command2)

sql_param2.Value = nf1
dxt2 = New DataTable
dxa2.Fill(dxt2)
DataGridView1.DataSource = dxt2
TextBox2.Text = dxt2.Rows.Count & " " & "Records Found"

error mesage: 
Syntax error near 'BRUSH' in the full-text search condition 'FORMSOF(Thesaurus, JOHNNY BRUSH)'.

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SET @SearchParam = 'FORMSOF(Thesaurus, ' + @param4 + ')'

should be:
SET @SearchParam = 'FORMSOF(Thesaurus, "' + @param4 + '")'
0
FordraidersAuthor Commented:
Thnks so much to both of ya !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.