Add parameter issue for fulltext search in C#

Question

I have this part of code

string query = "SELECT ID, COL1 FROM TABLE1 WHERE CONTAINS(COL1,@text)";

sqlCommand.CommandText = sql;
sqlCommand.Parameters.Add("@text", value+"*");

value is a function parameter.

For fulltext search, the sql statement must be like this:

SELECT ID, COL1 FROM TABLE1 WHERE CONTAINS(COL1,'"eng*"')

It could search strings which start with "eng" -> english, bla blah.

But executing in C# the above code then ExecuteReader() returns empty list.

@text has as value "sometext*" but I want to add ' ' characters.

I tried string query = "SELECT ID, COL1 FROM TABLE1 WHERE CONTAINS(COL1,'@text')"; but it doesn't work, returns empty list.

Why ? How do I proceed that @text parameters must include '' characters over value for search ?

Thank you

Answer 1

Have you tried specifying the data type? I don't think you should need the quotes in the sqlcommand string.

sqlCommand.Parameters.Add("@text", SqlDbType.VarChar);
sqlCommand.Parameters["@text"].Value = value+"*";

Or SqlDbType.NVarChar.

Answer 2

Can you try using sqlCommand.Parameters.AddWithValue("@text", value+"*");

If above doesn't work, you can use SQL Parameter and SqlDBType.

I think your SQL query is wrong. See is this post helps.

C# constructing parameter query SQL - LIKE %