Re: FTS Weighted Values Implementation Problem

Ok, I understand.
Your syntax error is a VB.NET compilation error under Visual Studio. We misunderstood each other.

With VB.NET :

Dim tsql as String = String.Format("SELECT FT_TBL.FullDocNo, FT_TBL.DocType,
KEY_TBL.RANK FROM FullDocuments AS FT_TBL INNER JOIN
CONTAINSTABLE(FullDocuments, SectionText, 'ISABOUT (""" + SearchTerm1 + """
weight (.8), """ + SearchTerm2 + """ weight (.4), """ + SearchTerm3 + """ weight (.2) )' )
AS KEY_TBL ON FT_TBL.FullDocumentID = KEY_TBL.[KEY] ORDER BY
KEY_TBL.RANK DESC
'string above is on a unique line of code
Dim adp as SqlDataAdapter = new SqlDataAdapter(tsql, conn)


With T-SQL, full-text pattern is a string (nvarchar). In your last query, it consider @Term as literal value. You also have to concatenate query :

[...] 'ISABOUT ("' + @SearchTerm1 + '" weight (.8), "' + @SearchTerm2 + '" weight (.4),[...]


--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr


"Organic Man" <davidmcmillin@xxxxxxx> wrote in message news:1188831562.850977.193840@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I tried to output ot console with two variations, but the build
failed:

'ERROR = "END OF STATEMENT EXPECTED"
Dim tsql As String = "SELECT FT_TBL.FullDocNo, FT_TBL.DocType,
KEY_TBL.RANK FROM FullDocuments AS FT_TBL INNER JOIN
CONTAINSTABLE(FullDocuments, SectionText, 'ISABOUT ("SearchTerm1"
weight (.8), "SearchTerm2" weight (.4), "SearchTerm3" weight (.2) )' )
AS KEY_TBL ON FT_TBL.FullDocumentID = KEY_TBL.[KEY] ORDER BY
KEY_TBL.RANK DESC "
Console.WriteLine(tsql)
Dim adp As SqlDataAdapter = New SqlDataAdapter(tsql, conn)

'ERROR = ')' EXPECTED - SEARCHTERM1 WITH SQUIGGLY UNDERLINE.
Dim tsql As String = ("SELECT FT_TBL.FullDocNo, FT_TBL.DocType,
KEY_TBL.RANK FROM FullDocuments AS FT_TBL INNER JOIN
CONTAINSTABLE(FullDocuments, SectionText, 'ISABOUT ("SearchTerm1"
weight (.8), "SearchTerm2" weight (.4), "SearchTerm3" weight (.2) )' )
AS KEY_TBL ON FT_TBL.FullDocumentID = KEY_TBL.[KEY] ORDER BY
KEY_TBL.RANK DESC ", conn)
Console.WriteLine(tsql)
Dim adp As SqlDataAdapter = New SqlDataAdapter(tsql, conn)

I then tried a series of queries in Management Studio. They all
executed successfully, but the last one which included variables and
weight values yielded 0 results.

SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS (SectionText, 'gold') *** 1670 results ***

DECLARE @SearchTerm1 nvarchar(10)
SET @SearchTerm1 ='gold'
SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS (SectionText, @SearchTerm1) *** 1670 results ***

SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS (SectionText, 'silver') *** 501 results ***

DECLARE @SearchTerm2 nvarchar(10)
SET @SearchTerm2 ='silver'
SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS (SectionText, @SearchTerm2) *** 501 results ***

SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS (SectionText, 'copper') *** 619 results ***

DECLARE @SearchTerm3 nvarchar(10)
SET @SearchTerm3 ='copper'
SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS (SectionText, @SearchTerm3) *** 619 results ***

SELECT FT_TBL.FullDocNo, KEY_TBL.RANK
FROM FullDocuments AS FT_TBL INNER JOIN
CONTAINSTABLE(FullDocuments, SectionText,
'ISABOUT ("gold" weight (.8), "silver" weight (.4), "copper" weight (.
2) )' ) AS KEY_TBL ON FT_TBL.FullDocumentID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC *** 2067 results ***

DECLARE @SearchTerm1 nvarchar(10)
SET @SearchTerm1 ='gold'
DECLARE @SearchTerm2 nvarchar(10)
SET @SearchTerm2 ='silver'
DECLARE @SearchTerm3 nvarchar(10)
SET @SearchTerm3 ='copper'
SELECT FT_TBL.FullDocNo, KEY_TBL.RANK
FROM FullDocuments AS FT_TBL INNER JOIN
CONTAINSTABLE(FullDocuments, SectionText,
'ISABOUT (@SearchTerm1 weight (.8), @SearchTerm2 weight (.4),
@SearchTerm3 weight (.2) )' ) AS KEY_TBL ON FT_TBL.FullDocumentID =
KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC *** 0 results ***

On Sep 3, 10:09 am, "Jean-Pierre Riehl" <jean-pierre.ri...@xxxxxxxxxxxxxx> wrote:
No, I meant output T-SQL string to Console in debug mode.

Dim tsql as String = "Select ......"
Console.WriteLine(tsql)

Dim adp as SqlDataAdapter = new SqlDataAdapter(tsql, conn)

Have you tried your T-SQL command directly in Management Studio ?

--
Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr

"Organic Man" <davidmcmil...@xxxxxxx> wrote in message

news:1188824805.017298.39600@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


.

Loading