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?
 
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
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.