How can you use SQL Full Text Search CONTAINS() with an asp.net 2.0 ObjectDataSource using

How can you use SQL Full Text Search CONTAINS() with an asp.net 2.0 ObjectDataSource using @.Parameters?

MSDN says something like this, but only works directly using like the Query from SQL Manager:

USE TestingDB;
GO
DECLARE @.SearchWord NVARCHAR(30)
SET @.SearchWord = N'performance'
SELECT TestText
FROM TestingTable
WHERE CONTAINS(TestText, @.SearchWord);

I tryed to mak something like that work with the DataSet DataAdapter Query Builder for the ObjectDataSource, but you can't use DECLARE or SET.

SELECT TestText
FROM TestingTable
WHERE CONTAINS(TestText, @.SearchWord);

But again it says @.SearchWord not a valide SQL Construct

Is there anyway to make a DataSet.DataApater.ObjectDataSource work with an SQL FTS CONTAINS() with @.Parameters?

Have you tried putting it into a stored procedure?

|||

No, I habe not tried using a stored procedure, thanks for the reminder, I will try that, if it works out great I will tell you, thanks.

|||

In the query window I do:

DECLARE @.SearchWord NVARCHAR(128)

SET @.SearchWord = N'Water'

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

It works great and I get the values returned that I should, but only in the SQL Manager 2005, or a query in visual studio 2005, and not in the DataSet Builder for ObjectDataSource.

When I do this as a stored procedure as:

CREATE PROCEDURE SP_Keywords

@.Keywords NVARCHAR(128)

AS

BEGIN

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

It prompts me for the value of the @.Keywords, I get 0 rows returned, it does execute properly no errors, and it is connecting to the right DB/Tables so on.

I'm new to stored procedures, I normaly just use SQL Queries, but I see all the advantages of using Stored Procedures.

Can you help me if you can please and thank you?

|||

Your queries are bad code. You cannot mix AND with OR like you have done. It is afundamental logic error.

You must use ( ) around the various units of code that go together.

Example

WHERE ( Answers.CallBackTypeId = 1
AND CONTAINS(Answers.BusinessName, @.Keywords)
)
OR ( Answers.CallBackTypeId = 1
AND CONTAINS(BusinessType.TypeEN, @.Keywords)
)
OR ...

END

WHERE Answers.CallBackTypeId = 1
AND ( CONTAINS(Answers.BusinessName, @.Keywords)
OR CONTAINS(BusinessType.TypeEN, @.Keywords)
OR ...
)

|||

I knew that, I actualy wrote the script the way you did, but like always visual studio likes to change code auto formatting crap even do that option is off, and it converted it to what I posted.

Yes the script still works even do it's mixed like it is with AND/OR, I tested it both ways, comes out with the same results, it's just the script is longer that's all, no bad.

So let me restate my problem, lets not go off track since the script works.

In the query window I do:

DECLARE @.SearchWord NVARCHAR(128)

SET @.SearchWord = N'Water'

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

It works great and I get the values returned that I should, but only in the SQL Manager 2005, or a query in visual studio 2005, and not in the DataSet Builder for ObjectDataSource.

When I do this as a stored procedure as:

CREATE PROCEDURE SP_Keywords

@.Keywords NVARCHAR(128)

AS

BEGIN

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

It prompts me for the value of the @.Keywords, I get 0 rows returned, it does execute properly no errors, and it is connecting to the right DB/Tables so on.

I'm new to stored procedures, I normaly just use SQL Queries, but I see all the advantages of using Stored Procedures.

Can you help me if you can please and thank you?

|||

cdmlb:

In the query window I do:

DECLARE @.SearchWord NVARCHAR(128)

SET @.SearchWord = N'Water'

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

Well, the obvious thing to ask you is why you are setting @.SearchWord and searching for @.Keywords in the query studio version. The query in the query studio and in the stored procedure are not the same!