CODEWITHSUNDEEP
c#asp.netsql-server-2005stored-proceduresfull-text-search
markiz
markiz

Reputation: 2184

Full text search stored procedure error

This is the error I am getting: 

Syntax error near 'online' in the full-text search condition '""online"*" and "and*" and ""text"*"'.

This is my stored procedure:

ALTER PROCEDURE dbo.StoredProcedure1
(
    @text varchar(1000)=null    
)
AS
SET NOCOUNT ON

declare @whereclause varchar(1000)


SET @whereclause = @text

SELECT articles.ArticleID AS linkid, 
       articles.abstract as descriptiontext, 
       articles.title as title,
       'article' as source, 
       articles.releasedate as lasteditdate     
 FROM articles
        WHERE  CONTAINS(title, @whereclause)

ORDER BY lasteditdate DESC, source ASC

This what i pass to SP: 

string content = "\"online\" and \"text\"";

part of C# code:

 using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {              
                SqlCommand cmd = new SqlCommand("StoredProcedure1", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@text", SqlDbType.VarChar).Value = searchExpression;
                cn.Open();

UPDATE:
Strings that i try and errors that i get:

content = "online text";
Syntax error near 'text' in the full-text search condition 'online text'. 

content = "\"online\" and \"text\"";
Syntax error near 'online' in the full-text search condition '""online"*" and "and*" and ""text"*"'. 


content = "\"online and text\"";
Syntax error near 'online*' in the full-text search condition '""online*" and "and*" and "text"*"'.

Upvotes: 0

Views: 2776

Answers (6)

Drazen Patekar
Drazen Patekar

Reputation: 86

I use this method to remove slashes and then pass the resulting char array to sp.

public static char[] RemoveBackslash(string value)
{
    char[] c = value.ToCharArray();
    return Array.FindAll(c, val => val != 39).ToArray();
}

string content = "'\"online\" and \"text\"'";

Sqlparam = new SqlParameter("@search", SqlDbType.NVarChar);
Sqlparam.Value = RemoveBackslash(content);
Sqlcomm.Parameters.Add(Sqlparam);

Upvotes: 0

markiz
markiz

Reputation: 2184

From msdn:

Specifies the text to search for in column_name and the conditions for a match.

is nvarchar. An implicit conversion occurs when another character data type is used as input.

Because "parameter sniffing" does not work across conversion, use nvarchar for better performance.

So i've changed everything to nvarchar:

cmd.Parameters.Add("@text", SqlDbType.NVarChar).Value = searchExpression;  

declare @whereclause nvarchar(1000)

Upvotes: 1

MartW
MartW

Reputation: 12538

Not sure if it's significant, but your procedure is expecting varchar and your calling code is saying the parameter is SqlDbType.Char. I'm quite fond of DeriveParameters :

SqlCommand cmd = new SqlCommand("StoredProcedure1", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open()
SqlCommandBuilder.DeriveParameters cmd;
cmd.Parameters("@text").Value = searchExpression;

Upvotes: 0

DmitryK
DmitryK

Reputation: 5582

The problem is with the extra quotation marks. Instead of this:

string content = "\"online\" and \"text\"";

try this:

string content = "online and text";

It will generate a correct condition:

 '"online*" and "and*" and "text*"'

Also if accept user input and pass it directly into a query like this - you are really opening your application to SQL injection.

Upvotes: 0

Sergey Olontsev
Sergey Olontsev

Reputation: 1104

Try this one in your c# code when adding parameter:

cmd.Parameters.Add("@text", searchExpression);

Upvotes: 0

richardwiden
richardwiden

Reputation: 1602

I think SQl uses % instead of *

Upvotes: 0

Related Questions