CODEWITHSUNDEEP
c#sqlparametersfirebird
Myhiad
Myhiad

Reputation:

C# constructing parameter query SQL - LIKE %

I am trying to build SQL for a parameter query in C# for a query which will contain the LIKE %% command.

Here is what I am trying to acheive (please note that the database is Firebird)

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE '%?%'", TABLE, NAME);
 cmd.Parameters.AddWithValue(NAME, "JOHN");

Now I have tried every single permutation to get the parameter to work, I have tried;

I cannot seem to get this to work, how can I use a parameter for the LIKE query to work.

Suggestions are welcome!

Upvotes: 19

Views: 39663

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

var SQL = string.Format("SELECT * FROM {0} WHERE {1} LIKE '%' + ? + '%'", TABLE, NAME);
Cmd.CommandText = SQL;
Cmd.Parameters.Add("?", SqlDbType.VarChar, 50).Value = "JOHN";

Upvotes: 5

Guffa
Guffa

Reputation: 700860

You can't have parameters inside of a string literal in the query. Make the entire value the parameter, and add the wildcards to the string:

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE ?", TABLE, NAME);
Cmd.Parameters.AddWithValue(NAME, "%" + "JOHN" + "%");

Upvotes: 28

Chris
Chris

Reputation: 40661

In the past when doing this, i've simply integrated it into the sql, making sure that i replace single quotes with question marks to deal with sql injection. Eg:

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE '%{2}%'",
  TABLE,
  NAME,
  JOHN.Replace("'","?"));

Upvotes: -4

Related Questions