SqlDataSource optional parameter problem

[JSmith]

Hi, I'm having some issues with the SqlDataSource. I want to use it to
populate a GridView, but using an optional parameter to filter the results.

This is what I have right now (hopefully haven't made any typos - can't
copy/paste):

<asp:SqlDataSource ID="test1" runat="server" SelectCommand="SELECT * FROM
SomeTable WHERE (@MyParam IS NULL OR MyColumn = @MyParam) ORDER BY
SomeColumn" ConnectionString="<% ConnectionStrings:MyConnString %>" >
<SelectParameters>
<asp:ControlParameter Name="MyParam" ControlID="DropDownList1"
PropertyName="SelectedValue" Type="String" ConvertEmptyStringToNull="True"
DefaultValue="" />
</SelectParameters>
</asp:SqlDataSource>

<aspropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
<asp:ListItem Selected="True" Value="">All</asp:ListItem>
<asp:ListItem>AnotherValue</asp:ListItem>
<asp:ListItem>SomeElse</asp:ListItem>
<asp:ListItem>Whatever</asp:ListItem>
</aspropDownList>

<asp:GridView ID="GV" runat="server" DataSourceID="test1"
AutoGenerateColumns="False" DataKeyNames="SomeID"
<columns>
<asp:BoundField DataField="SomeColumn" HeaderText="A Title"
SortExpression="SomeColumn" />
(more bound columns here...)
</columns>
</asp:GridView>

When I test the SQL query in the query designer it works (returns only rows
having the value passed as a parameter when one is specified, otherwise it
returns all rows), so it seems like that part is OK. The "All" (as in
"return all"/no filtering) entry in the DropDownList has a value of a zero
lenght string, and the ControlParameter has the convert empty string to null
to true (and the default value is the same), so it should get converted to a
null when "All" is selected, hence returning all rows. But it doesn't work.
It works fine for all the entries with text, but the zero lenght string
somehow doesn't work - I get no results at all instead of it returning all
rows (but the query itself worked fine when I tested it).

What am I missing? I just can't find what I'm doing wrong. Any ideas/hints?
(I also need to do the same with an ObjectDataSource, so hopefully I can get
this to work!) I can't think of an easy way to find out if the zero lenght
string gets converted to a null or not (I've even tried adding OR @MyParam =
'' to the query and it still didn't work....) Right now I'm stuck....

Thanks a lot in advance for the replies!

Carl B.

 

[JSmith]

Nevermind... I found the answer! Had to set CancelSelectOnNullParameter to
false on the SqlDataSource!