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.Paramete rs.Add(param);
cmdObj.Paramete rs["@filer"].value= 'B';

can anyone please help me regarding this.

Thanks,
James

[Marc Gravell]

Re: using Like Operator with a parameter in a command text

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]

Re: using Like Operator with a parameter in a command text

"James" <bondrulesit200 8@gmail.comwrot e in message
news:86bdbcab-8fd3-4f80-8dec-6bf75a36e0cb@f6 3g2000hsf.googl egroups.com...

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.Paramete rs.Add(param);
cmdObj.Paramete rs["@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.Varch ar,
50);
cmdObj.Paramete rs.Add(param);
param.Value= '"B%";

[James]

Re: using Like Operator with a parameter in a command text

On Oct 15, 12:09 pm, Marc Gravell <marc.grav...@g mail.comwrote:

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