comma separated string filter parameter

Question

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 13-May-13 4:58am

memberxxxxxxxxxxxxxxxxx

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Balimusi · Answer 1 · 2013-05-13T05:21:00

Solution 1

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)

Posted 13-May-13 5:21am

Balimusi

Updated 13-May-13 16:46pm

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();