using Like Operator with a parameter in a command text

[James]

Hello,

I am able to get the result set when I am giving a hardcoded value
( Ex: "WHERE LIKE 'B%' ") in a command text, but when I am trying to
give a parameter to that and passing a value, I am not able to get the
result set.
Ex: cmdObj.Text= "SELECT Phone FROM Person.Contact WHERE LastName
LIKE '@filter%' ";
SqlParameter param = new SqlParameter(");
cmdObj.Parameters.Add(param);
cmdObj.Parameters["@filer"].value= 'B';

can anyone please help me regarding this.

Thanks,
James

 

[Marc Gravell]

It is just "...WHERE LastName LIKE @filter..." (no single quotes)

There is also a typo in the parameter name - you had "@filer", not
"@filter"

I would tend to do the + "%" in the C#, but you can do this in the
TSQL if you like:

"...WHERE LastName LIKE @filter + '%'..."

Marc

 

[Alberto Poblacion]

I am able to get the result set when I am giving a hardcoded value
( Ex: "WHERE LIKE 'B%' ") in a command text, but when I am trying to
give a parameter to that and passing a value, I am not able to get the
result set.
Ex: cmdObj.Text= "SELECT Phone FROM Person.Contact WHERE LastName
LIKE '@filter%' ";
SqlParameter param = new SqlParameter(");
cmdObj.Parameters.Add(param);
cmdObj.Parameters["@filer"].value= 'B';

You need to add the "%" in the Value of the parameter, not in the query. And
the name of the parameter in the query string should not be surrounded by
quotes.

cmdObj.Text= "SELECT Phone FROM Person.Contact WHERE LastName
LIKE @filter";
SqlParameter param = new SqlParameter("@filter", SqlDbType.Varchar,
50);
cmdObj.Parameters.Add(param);
param.Value= '"B%";

 

[James]

It is just "...WHERE LastName LIKE @filter..." (no single quotes)

There is also a typo in the parameter name - you had "@filer", not
"@filter"

I would tend to do the + "%" in the C#, but you can do this in the
TSQL if you like:

"...WHERE LastName LIKE @filter + '%'..."

Marc

Thanks
James