I have the following code:
const string Sql =
@"select distinct [name]
from tblCustomers
left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId
where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');";
using (var command = new SqlCommand(Sql, Connection))
{
command.Parameters.AddWithValue("@SEARCH", searchString);
...
}
This does not work, I tried this as well:
const string Sql =
@"select distinct [name]
from tblCustomers
left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId
where (tblCustomer.Name LIKE @SEARCH OR tblCustomerInfo.Info LIKE @SEARCH );";
using (var command = new SqlCommand(Sql, Connection))
{
command.Parameters.AddWithValue("@SEARCH", "'%" + searchString + "%'");
...
}
but this does not work as well. What is going wrong? Any suggestions?
What you want is:
tblCustomerInfo.Info LIKE '%' + @SEARCH + '%'
(or edit the parameter value to include the % in the first place).
Otherwise, you are either (first sample) searching for the literal "@SEARCH" (not the arg-value), or you are embedding some extra quotes into the query (second sample).
In some ways, it might be easier to have the TSQL just use LIKE @SEARCH
, and handle it at the caller:
command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%");
Either approach should work.