Full text search stored procedure error

Question

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"*"'.

Answer 1

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

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

Answer 2

I think SQl uses % instead of *

Answer 3

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.

Answer 4

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;

Answer 5

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)

Answer 6

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);