CONTAINSTABLE - select statement arrrrggg

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CONTAINSTABLE - select statement arrrrggg

Author

Topic

AndyJay
Starting Member

1 Post

Posted - 2011-12-07 : 05:44:42

Hi all.
my first post here and hope to solve this problem before i lose it :-)
I'm really struggling with CONTAINSTABLE.
I have it working and Ranking as it should in MS SQL Query, but i want to be able to search on words, partial words and phrases. Help!

This is the sql which works in ms sql query

SELECT FT_TBL.ProductID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, KEY_TBL.RANK, dbo.ClientProducts.ClientID
FROM dbo.Products AS FT_TBL INNER JOIN
CONTAINSTABLE(dbo.Products, Product, '"seaw*"') AS KEY_TBL ON FT_TBL.ProductID = KEY_TBL.[KEY] INNER JOIN
dbo.ClientProducts ON FT_TBL.ProductID = dbo.ClientProducts.ProductID
WHERE (dbo.ClientProducts.ClientID = 0) AND (KEY_TBL.RANK > 10)
ORDER BY KEY_TBL.RANK DESC

This is the code in my classic ASP application.

<%
Dim RSResults__param5
RSResults__param5 = "xxx"
If (Request("searchme")  <> "") Then 
  RSResults__param5 = Request("searchme")
 
End If
%>
<%
Dim RSResults__param6
RSResults__param6 = "0"
If (Application("ClientID")  <> "") Then 
  RSResults__param6 = Application("ClientID") 
End If
%>

<%
Set RSResults_cmd = Server.CreateObject ("ADODB.Command")
RSResults_cmd.ActiveConnection = MM_shoppingcart_STRING
RSResults_cmd.CommandText = "SELECT FT_TBL.ProductID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, KEY_TBL.RANK, dbo.ClientProducts.ClientID FROM dbo.Products AS FT_TBL INNER JOIN CONTAINSTABLE(dbo.Products, Product, ?) AS KEY_TBL ON FT_TBL.ProductID = KEY_TBL.[KEY] INNER JOIN  dbo.ClientProducts ON FT_TBL.ProductID = dbo.ClientProducts.ProductID WHERE (dbo.ClientProducts.ClientID = ?) AND (KEY_TBL.RANK > 10) ORDER BY KEY_TBL.RANK DESC" 
RSResults_cmd.Prepared = true
RSResults_cmd.Parameters.Append RSResults_cmd.CreateParameter("param1", 200, 1, 255, RSResults__param5) ' adVarChar
RSResults_cmd.Parameters.Append RSResults_cmd.CreateParameter("param2", 5, 1, -1, RSResults__param6) ' adDouble

Set RSResults = RSResults_cmd.Execute
RSResults

_numRows = 0
%>

How do i add the wildcard * to my search variable?
I've tried everything i can think of.

Hope someone can advise please

Andy

PS Just to add to that, if i enter two words to search on, the apllication crashes with the error below

Microsoft OLE DB Provider for SQL Server error '80040e14'

Syntax error near 'book' in the full-text search condition 'recipe book'.

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-08 : 11:35:26

Try using your working SQL and convert it into a stored procedure with the search term as a parameter and then add double-quotes around it if it has an asterisk. And build dynamic SQL statement
e.g.
CREATE PROC myProc (@myTerm varchar (50))
AS
declare @SQL varchar(max)

If @myTerm like '%*%'
begin
set @myTerm ='"' + @myTerm + '"'
end
PRINT @myTerm

set @SQL =
'SELECT FT_TBL.ProductID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, KEY_TBL.RANK, dbo.ClientProducts.ClientID
FROM dbo.Products AS FT_TBL INNER JOIN
CONTAINSTABLE(dbo.Products, Product, ''' + @myTerm + ''') AS KEY_TBL ON FT_TBL.ProductID = KEY_TBL.[KEY] INNER JOIN
dbo.ClientProducts ON FT_TBL.ProductID = dbo.ClientProducts.ProductID
WHERE (dbo.ClientProducts.ClientID = 0) AND (KEY_TBL.RANK > 10)
ORDER BY KEY_TBL.RANK DESC'

Print @SQL
--If print output is same uncomment execute section below
--exec(@SQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources