Help: Using Multiple Checkboxes in a Checkboxlist to filter results in a gridview in ASP.NET 2.0

[joebob8000]

This seems like a simple task, but my 6 year old roots in classic ASP
must be causing me trouble with my current problem. I am looking to
provide a search for users in which they can select multiple categores
(check boxes) and view only the results in those categories in a
gridView control. My goal is to do so for movies, so an example would
be if the user checks comedy and childrens then they would only get
childrens comedies and the other "non children" comedies would be left
out of the list.

I can use the ControlParameter and link it to the CheckBoxList, but it
only responds to the first SelectedValue and not all of the Checked
items.

I can generate the SQL statement that I need to execute based on the
check boxes on the Page_Load event after a post back. i.e. SELECT *
FROM movies WHERE Category Like '%comedy%' and Category Like
'%Children%'. I can't seem to force this SQL statement to run on the
SQLDataSource control and display in my GridView

Where am i going wrong? Here is the ASPX page code:

<form id="form1" runat="server">
<div>
<asp:CheckBoxList ID="CategoryList" runat="server"
CellPadding="8" CellSpacing="0"
RepeatColumns="5">
<asp:ListItem>Action</asp:ListItem>
<asp:ListItem>Comedy</asp:ListItem>
<asp:ListItem>Adventure</asp:ListItem>
<asp:ListItem>Thriller</asp:ListItem>
<asp:ListItem>War</asp:ListItem>
<asp:ListItem>Drama</asp:ListItem>
<asp:ListItem>Fantasy</asp:ListItem>
<asp:ListItem>Martial Arts</asp:ListItem>
<asp:ListItem>Science Fiction</asp:ListItem>
<asp:ListItem>Spy</asp:ListItem>
<asp:ListItem>Disaster</asp:ListItem>
<asp:ListItem>Crime</asp:ListItem>
<asp:ListItem>Children</asp:ListItem>
<asp:ListItem>Horror</asp:ListItem>
<asp:ListItem>Animation</asp:ListItem>
<asp:ListItem>Biography</asp:ListItem>
<asp:ListItem>Holiday</asp:ListItem>
<asp:ListItem>Musical</asp:ListItem>
<asp:ListItem>Gangster Film</asp:ListItem>
<asp:ListItem>Family </asp:ListItem>
<asp:ListItem>Sports</asp:ListItem>
<asp:ListItem>Historical </asp:ListItem>
<asp:ListItem>Mystery</asp:ListItem>
<asp:ListItem>Romance</asp:ListItem>
<asp:ListItem>Western</asp:ListItem>
</asp:CheckBoxList>&nbsp;</div>
<asp:Button ID="btnSearch" runat="server" Text="Search for
Movies" />
<br />
<br />
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" CellPadding="4"
DataSourceID="SqlDataSource1" ForeColor="#333333"
GridLines="None" AllowSorting="True" PageSize="20" Font-Names="Arial"
Font-Size="Small" Font-Underline="False">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True"
ForeColor="White" />
<Columns>
<asp:BoundField DataField="NUM" HeaderText="NUM"
SortExpression="NUM">
<ItemStyle VerticalAlign="Top" />
</asp:BoundField>
<asp:HyperLinkField DataNavigateUrlFields="NUM"
DataNavigateUrlFormatString="details.aspx?Num={0}"
DataTextField="ORIGINALTITLE" HeaderText="TITLE"
SortExpression="ORIGINALTITLE">
<ItemStyle VerticalAlign="Top" Wrap="False" />
</asp:HyperLinkField>
<asp:TemplateField HeaderText="RATING"
SortExpression="Rating">
<ItemTemplate>
<%# GetRatingImage(Eval("Rating")) %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="CATEGORY"
HeaderText="CATEGORY" SortExpression="CATEGORY">
<ItemStyle VerticalAlign="Top" />
</asp:BoundField>
<asp:BoundField DataField="YEAR" HeaderText="YEAR"
SortExpression="YEAR">
<ItemStyle VerticalAlign="Top" />
</asp:BoundField>
<asp:BoundField DataField="LENGTH" HeaderText="LENGTH"
SortExpression="LENGTH">
<ItemStyle VerticalAlign="Top" />
</asp:BoundField>
</Columns>
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True"
ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White"
HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True"
ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775"
/>
<PagerSettings PageButtonCount="30" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:GillysplaceConnectionString %>"
SelectCommand="SELECT * FROM [movies] WHERE ([CATEGORY] LIKE '%' +
@CATEGORY + '%')">
<SelectParameters>
<asp:ControlParameter ControlID="CategoryList"
Name="CATEGORY" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<br />
<br />
<asp:Label ID="theSQLStatement" runat="server"></asp:Label>
</form>