i'm using a checkbox list as a filter for a sql query. i pass the values as a csv and so far...so almost good.
the problem is, if i select more than one value, then i will ignore/skip over records that do NOT have AT LEAST (not sure how else to put it) all of the selected values.
ex...
(dashes were for formatting purposes only)
person -------person/sport---------sport
_________________________________________
a ------------a | baseball------baseball
b ------------a | football------football
c ------------a | baseball------basketball
--------------b | baseball
--------------b | football
--------------c | baseball
--------------c | football
--------------c | basketball
if i select "baseball" then person a, b, and c will be returned.
if i select baseball and football then all 3 persons will show up in the gridview.
however, if i select any combination with baseketball, then b will NOT show up because in the many-many table, b does not have basketball.
for instance, if i pick football and basketball
or
baseball, football, and basketball
only persons a and c will show up
what i would like is for all 3 to show up because person b DOES have football if i select all sports.
very unsure what steps i need to take and changes i need to make in order for that to happen.
thanks for the help.
sql split function:
ALTER FUNCTION fnSplitter (@IDs Varchar(100) )
Returns @Tbl_IDs Table (ID Int) As
Begin
Set @IDs = @IDs + ','
Declare @Pos1 Int
Declare @pos2 Int
Set @Pos1=1
Set @Pos2=1
While @Pos1<Len(@IDs)
Begin
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
Set @Pos2=@Pos1+1
Set @Pos1 = @Pos1+1
End
Return
End
dataset/tableadapter query
SELECT DISTINCT
person.personID, sports.sportsID, person.personName, sports.sport,
FROM sports INNER JOIN
person INNER JOIN
personSport ON personSport.personID = person.personID
INNER JOIN
personSport ON personSport.sportID = sports.sportsID
WHERE (person.personName LIKE '%' + @personName + '%')
AND
(sports.sportID IN
(SELECT ID FROM dbo.fnSplitter(@IDs) AS fnSplitter_1) OR @IDs = 0)
in the html, i have a gridview, dropped in an objectdatasource and the select parameter is the checkboxlist which sends to "@IDs" from the split function and the type is "string"