How to create asp.net dataset freetext query

Question

I have a database with a fulltext catalog setup for one of my tables and the goal is to be able to conduct a freetext search against that table from an asp.net c# website. Using SQL Management Studio, I can manually run the queries such as:

SELECT ProductName
FROM   Products
WHERE FREETEXT(ProductName, 'ABC')

But when I add a SQL query in an dataset and pass a parameter to it as follow

SELECT ProductName
FROM   Products
WHERE FREETEXT(ProductName, @ProductName)

the wizard returns an error

The @ProductName SQL construct or statement is not supported

How can I create a strongly-type dataset query that support full-text search ?

@Keith I use wizard to add queries to a dataset in Visual Studio, there is no code, as it is auto generated. Normaly, I can create queries with [WHERE ABC = @ABC], but when I change the condition to [WHERE FREETEXT(ProductName, @ProductName)] - the wizard returns an error message. — bg.dev, Jun 16 '14 at 20:49

score 0 · Accepted Answer · answered Jun 23 '14 at 19:50

Tried to create a store procedure with parameter and call it from the Add query wizard. It still alerts a message but runs just well.

CREATE PROCEDURE [dbo].[sp_FreetextProductName] 
    @ProductName nvarchar(500)
AS
BEGIN
    SELECT ProductName
    FROM   Products
    WHERE FREETEXT(ProductName, @ProductName)
END

How to create asp.net dataset freetext query

1 Answers1