Click here to Skip to main content
15,850,548 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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:
SQL
ALTER FUNCTION fnSplitter (@IDs Varchar(100) )  
Returns @Tbl_IDs Table  (ID Int)  As  

Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
 
 -- Start from first character 
 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)
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return
End


dataset/tableadapter query
SQL
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)
/*the @IDs = 0 is for the initial value...eveerything pops up on pageload*/


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"
Posted

1 solution

You don't even need a function. Since you are passing comma separated values, you will need to make a dynamic query. For instance, declare a SQL variable, then set that variable to your select statement. After that execute that variable.
SQL
DECLARE @SQL VARCHAR(1000)

SET @SQL = 
	'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(@IDs))'
			
EXEC(@SQL)
 
Share this answer
 
v2
Comments
memberxxxxxxxxxxxxxxxxx 13-May-13 11:49am    
thanks for the help.

first time i tried it, i didn't have the ' near the select and at the end...so it said incorrect syntax near the select.

second time i put the little quote marks in and now it says...
"The data types varchar and varchar are incompatible in the modulo operator."

as i understand it, datasets are old school, but i've been following the data walkthroughs...i'm learning lol.

i made the tableadapter using sql statements. does this method still work for that?

for added info...when i made the adapter using the dataset wizard, it says that the DECLARE sql construct is not supported.
Balimusi 13-May-13 22:50pm    
Ok, I see the extra tick mark and removed it. I was typing it here, and didn't test the code in Management Studio.

Anyhow, you can return the data to your GridView using DataTable. And then bind it the GridView; for instance, GridView1.DataSource = dataTable1; GridView1.Bind();

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900