CODEWITHSUNDEEP
c#sqlsql-serversql-like
sbowde4
sbowde4

Reputation: 777

'LIKE' not working in SQL statement in code, but is in SSMS

I have a search box that searches by customerID, their full name, or a part of their name. I am using 'LIKE' to search using part of their name.

I can search by customerID or full name no problem, but if I enter Sam, no records for Samuel appear.

I am doing below in my SQL statement in the code, but get no records when searching for parts of names.

string sql = "SELECT * FROM  customers WHERE firstName LIKE '%@name%' OR lastName LIKE '%@name%' OR (firstName + ' ' + lastName)=@name OR customerID=@id ORDER BY customerID desc";
SqlParameter[] spParameter = new SqlParameter[2];
spParameter[0] = new SqlParameter("@id", (int.TryParse(searchTb.Text, out int i) ? i : 0));
spParameter[1] = new SqlParameter("@name", searchTb.Text);

I put this query into SSMS

SELECT * 
FROM customers 
WHERE firstName LIKE '%Sam%' 
   OR lastName LIKE '%lap%' 
   OR (firstName + ' ' + lastName) = 'Bob Tom' 
   OR customerID = 1

When I hit execute it shows me my expected output. I get all records with first names that contain sam, last names with lap, etc.

Is there something I should be doing code wise to fix my output, am I using my parameters correctly with like?

Upvotes: 0

Views: 215

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The issue here is the parameter substitution. Try treating the value just like a string constant:

WHERE firstName LIKE '%' + @name + '%'

Note: No single quotes.

Or, alternatively, put the wildcards in in the application:

WHERE firstName LIKE @name

Upvotes: 9

Related Questions