CODEWITHSUNDEEP
c#t-sqlado.net
user405723
user405723

Reputation: 491

How to add char type SqlParameter in SQL LIKE % %?

Can't get this working, how to add a parameter in SQL LIKE % % when its type is char?

SQL.Append("(Code LIKE @Code)");
var sqlParameter = new SqlParameter("@Code",
                      System.Data.SqlDbType.Char, 
                      10,
                      System.Data.ParameterDirection.Input,
                      true, 
                      0, 
                      0, 
                      "Code", 
                      System.Data.DataRowVersion.Current, 
                      "'%300%'");

Upvotes: 0

Views: 2456

Answers (2)

Scott Chamberlain
Scott Chamberlain

Reputation: 127563

There are two things wrong with your code, the first Soner pointed out, you need to remove the single quotes from your string. The second is the fact that you are using System.Data.SqlDbType.Char as the datatype and passed in a size of 10 to the constructor. This will pad out 10 spaces at the end of your string, effectively turning your string in to "'%300%' "

Use SqlDbType.VarChar instead. Also you don't need all those parameters in the constructor, just fill in the 3 you need then use the .Value property of the parameter you created.

SQL.Append("(Code LIKE @Code)");
var sqlParameter = new SqlParameter("@Code", SqlDbType.VarChar, 10);
sqlParameter.Value = "%300%";

Also, another trick that may be useful to you. If you want to always put the % on both sides but don't want the user to have to provide them you can put them in your query and still use parameters.

string code = "300";

SQL.Append("(Code LIKE '%' + @Code + '%')");
var sqlParameter = new SqlParameter("@Code", SqlDbType.VarChar, 10);
sqlParameter.Value = code;

Upvotes: 2

Soner Gönül
Soner Gönül

Reputation: 98750

Try to use your parameter value without single quotes. I think they make your value part as a string literal instead of parameter.

"%300%"

Upvotes: 1

Related Questions