I am building a website in ASP.NET 2.0, some description of the page I am working about:
ListView displaying a table (of posts) from my access db, and a ListBox with Multiple select mode used to filter rows (by forum name, value=forumId).
I am converting the ListBox selected values into a List, then running the following query.
SELECT * FROM sp_feedbacks WHERE forumId IN ([@Q])
When you have:
col in ('1,4')
This tests that col is equal to the string
'1,4'. It is not testing for the values individually.
One way to solve this is using
where ','&@Q&',' like '*,'&col&',*'
The idea is to add delimiters to each string. So, a value of "1" becomes ",1,"in the column. A value of "1,4" for @Q becomes ",1,4,". Now when you do the comparison, there is no danger that "1" will match "10".
Note (for those who do not know). The wildcard for
* rather than the SQL standard
%. However, this might differ depending on how you are connecting, so use the appropriate wildcard.