CODEWITHSUNDEEP
c#sql-server
user1523271
user1523271

Reputation: 1063

C# & SQL Server : add parameter to query

I have this query (not important per se):

SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Table1 WHERE " +
"CONTAINS((col1, col2, col3), " +  "'\"*m*\"' )" +   
"ORDER BY(SELECT null) " + 
"OFFSET(1) ROWS  FETCH NEXT(100) ROWS ONLY", conn);

It returns all rows that have an 'm' in the specified columns, as it should. Notice that the 'm' is inside a pair of asterisks (wild characters), then quotation marks, then apostrophes.

I wanted to parametrize it, i.e. put any string in the query.

I wrote this:

SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Table1 WHERE "+ "CONTAINS((col1, col2, col3), " +    "'\"*@searchText*\"' )" +  
"ORDER BY(SELECT null) "+ 
"OFFSET(1) ROWS  FETCH NEXT(100) ROWS ONLY", conn);
sqlCmd.Parameters.AddWithValue("@searchText", textToSearch);

But instead of putting the textToSearch string's contents in the SqlCommand, this code puts @searchText itself.

I looked similar posts here and tried to follow but it did not work, probably because there is an apostrophe and a quotation mark and an asterisk in the format.

What am I doing wrong?

How should I specify this command?

Upvotes: 1

Views: 3113

Answers (3)

interesting-name-here
interesting-name-here

Reputation: 1890

You are enclosing your parameter inside single quotes which are reserved for character strings.

Remove the single quotes around the parameter and you should be fine like so:

Yours:

+ "'\"@searchText\"' )" +

Correct:

+ " @searchText)" +

EDIT:

If you want to include the double quotes and asterisk in what you are searching for, you'll want to concatenate the string in SQL like so:

+ "'\"*' + @searchText + '*\"')" +

EDIT2:

Per @steve's suggestion:

textToSearch = "'\"*" + textToSearch + "*\"'"

Then, you can leave your SQL as this which is much more readable.

+ " @searchText)" +

Upvotes: 2

Serg
Serg

Reputation: 22811

Try to build a value of a parameter and use the parameter. Kind of

SqlCommand sqlCmd = new SqlCommand(@"SELECT * FROM Table1 
    WHERE CONTAINS((col1, col2, col3), @searchText )
    ORDER BY(SELECT null) 
    OFFSET(1) ROWS FETCH NEXT(100) ROWS ONLY"
    , conn);
 sqlCmd.Parameters.AddWithValue("@searchText", "\"*"+textToSearch+"*\"");

Upvotes: 2

kjh
kjh

Reputation: 15

You need to concatenate the parameter into your search string in the query... something like this:

"'\"*' + @searchText + '*\"' )"

Upvotes: 2

Related Questions