C# & SQL Server : add parameter to query

Question

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?

Why are you wrapping @searchText in *? Sorry if this is obvious, I don't claim to know everything. — interesting-name-here
– interesting-name-here, Commented Jul 12, 2017 at 17:38
You're saying CONTAINS((col1, col2, col3), '"*@searchText*"') in your string. — interesting-name-here
– interesting-name-here, Commented Jul 12, 2017 at 17:47

interesting-name-here · Accepted Answer · 2017-07-12 18:08:50Z

2

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)" +

edited Jul 12, 2017 at 18:08

answered Jul 12, 2017 at 17:45

interesting-name-here

1,8901 gold badge22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Over a year ago

Perhaps it is better to prepare correctly the textToSearch variable instead of complicating the query text.

user1523271 Over a year ago

I added additional apostrophes, which was the error. Removing them and leaving only the quotation marks and the asterisk fixed the problem. Thank you all!

kjh · Accepted Answer · 2017-07-12 17:40:54Z

2

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

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

answered Jul 12, 2017 at 17:40

kjh

151 bronze badge

1 Comment

kjh Over a year ago

It's the same as your edited answer. I didn't test, so please give the error and correct yours too.

Serg · Accepted Answer · 2017-07-12 18:02:39Z

2

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+"*\"");

answered Jul 12, 2017 at 18:02

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Collectives™ on Stack Overflow

C# & SQL Server : add parameter to query

3 Answers 3

2 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Related