I have a checkbox list and a many to many table. i am trying to use the checkbox list to filter a sql query and the function works. the query works, but when i try to hook up the html checkbox list to the query..it falls apart.
i've been trying to use the sqldatasource wizard to plug everything together. i set the checkbox list as the control parameter, but for some reason, the query doesn't work properly when i try to debug the page.
how do i send the checkbox selected values to the split function? is it possible to do this with the asp sqldatasource wizard? if not...how do i accomplish this?
here's the select parameter:
<asp:ControlParameter ControlID="DropDownCheckBoxes1"
ConvertEmptyStringToNull="False" DefaultValue="0" Name="Ids"
PropertyName="SelectedValue" Size="8000" Type="String" />
here's the sql function:
ALTER FUNCTION SplitDelimiterString1 (@StringWithDelimiter VARCHAR(8000), @Delimiter VARCHAR(8))
RETURNS @ItemTable TABLE (Item VARCHAR(8000))
AS
BEGIN
DECLARE @StartingPosition INT;
DECLARE @ItemInString VARCHAR(8000);
SELECT @StartingPosition = 1;
IF LEN(@StringWithDelimiter) = 0 OR @StringWithDelimiter IS NULL RETURN;
WHILE @StartingPosition > 0
BEGIN
SET @StartingPosition = CHARINDEX(@Delimiter,@StringWithDelimiter);
IF @StartingPosition > 0
SET @ItemInString = SUBSTRING(@StringWithDelimiter,0,@StartingPosition)
ELSE
SET @ItemInString = @StringWithDelimiter;
IF( LEN(@ItemInString) > 0)
INSERT INTO @ItemTable(Item) VALUES (@ItemInString);
SET @StringWithDelimiter = SUBSTRING(@StringWithDelimiter,@StartingPosition +
LEN(@Delimiter),LEN(@StringWithDelimiter) - @StartingPosition)
IF LEN(@StringWithDelimiter) = 0 BREAK;
END
RETURN
END
here's the sql 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)