Using a variable in full text index predicate through ADO

I need to run full text indexing on a table joined with another table using a variable as the full text predicate.

First I tried to run this query directly from the web site:

SELECT styles.style_id, styles.styleName, styles.itemNumber, styles.detailName, styles.shortDesc, styles.longDesc, styles.keywords, MIN(offerPrice.price) AS lprice, MAX(offerPrice.price) AS hprice
FROM styles INNER JOIN offerPrice ON styles.style_id = offerPrice.style_id
WHERE (offerPrice.defaultFlag = '1') AND CONTAINS(styles.*, '" & strSiteSearch & "')
GROUP BY styles.style_id, styles.styleName, styles.itemNumber, styles.detailName, styles.shortDesc, styles.longDesc, styles.keywords
ORDER BY '" & sort & "'

I get this error:
ASP Error occurred 10/15/2004 10:16:49 AM in Microsoft OLE DB Provider for SQL Server
Error number: -2147217887 (0x80040E21)
File: /search.htm, line 174
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

To get around that I thought I could just put it into a stored procedure. the stored procedure looks like this:
CREATE PROCEDURE dbo.siteSearch
@strSearch AS varchar(250)
AS
SELECT styles.style_id, styles.styleName, styles.itemNumber, styles.detailName, styles.shortDesc, styles.longDesc, styles.keywords, MIN(offerPrice.price) AS lprice, MAX(offerPrice.price) AS hprice
FROM styles INNER JOIN offerPrice ON styles.style_id = offerPrice.style_id
WHERE (offerPrice.defaultFlag = '1') AND CONTAINS(styles.*, @strSearch)
GROUP BY styles.style_id, styles.styleName, styles.itemNumber, styles.detailName, styles.shortDesc, styles.longDesc, styles.keywords
ORDER BY styleName
GO

I execute this stored procedure from the web site like this:
Set rsTHUMBS = Server.CreateObject("ADODB.Recordset")
cmdTemp.CommandText = "EXECUTE siteSearch '" & strSiteSearch & "'"
rsTHUMBS.Open cmdTemp, , 1, 3
If NOT rsTHUMBS.EOF Then ...

That give me this error:
ASP Error occurred 10/15/2004 10:23:45 AM in Microsoft OLE DB Provider for SQL Server
Error number: -2147217900 (0x80040E14)
File: /search.htm, line 174
A variable cannot be used to specify a search condition in a fulltext predicate when accessed through a cursor.

Any work arounds/suggestions? Is there a way to accomplish what I am trying to do?

Thank you very much for your help.
fetiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
JaffaKREEConnect With a Mentor Commented:
I get the feeling this is one of those "not supported" things.   I'm out of my league here, and this is all I found (google cache link):

http://64.233.167.104/search?q=cache:a1OqLW0qSZIJ:65.61.175.198/t883114.html+%22sql+server%22+%22A+variable+cannot+be+used+to+specify+a+search+condition+%22&hl=en
0
 
JaffaKREECommented:
Can you use a wildcard in the first parameter of CONTAINS ?  shouldn't that be a specific column ?

0
 
fetiAuthor Commented:
I have run this query and stored procedure without error in Enterprise Manager. It's only through ADO that I get the error. The wildcard simply says to check all of the fields in the catalog.
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.