I've been trying to help out a colleague, Bruce (I use the word colleague but Bruce is several rungs up the greasy pole from me) with his admirable desire to build an ASP.NET site to share his knowledge and experience about the Maldives with the world. I remember helping him some time ago (must be over a year ago). When he came to my desk this week I was particularly endeared when he said something along the lines of "Thanks for the help you gave me way back, it's come on quite a bit since then. This guy called Dave Sussman has been helping me. He showed me how to do it *right*."
Trying not to look too hurt we had a dabble about in the code again. He's using an AccessDataSource and has a pretty simple set of filters (as DropDownLists) and a GridView to display the results. On the filters he wants to add an "Any" option - ie don't filter on this. For the numerical stuff he's simply associated a very high value with the item in the DropDownList. So, for example, if you're filtering on # of rooms, the Value associated with the Any item might be 1000. Where this breaks down is with the Boolean options.
My initial reaction was to add some code to dynamically build the SQL statement but it all starts to get a little messy because there are multiple filters and you need to cater for whether the previous filter has added a where clause to the query or not (actually, you could probably get around this quite easily). But so far Bruce has managed to keep almost everything declarative and I was convinced it ought to be possible.
I have to admit I spent a lot longer on this than intended mainly down to one property on the AccessDataSource control - CancelSelectOnNullParameter. My one observation: it can be extremely difficult to debug when you're databinding declaratively and things don't go to plan. There's a lot to be said for writing code in this scenario (and I know, in reality, that's what most people do).
Here's a simplified version of where I got to:
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"> <asp:ListItem Value="">Any</asp:ListItem> <asp:ListItem Value="True">Yes</asp:ListItem> <asp:ListItem Value="False">No</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/Database1.mdb" CancelSelectOnNullParameter="False" SelectCommand="select * from [Table1] where ((@Pool IS NULL) or (Pool = @Pool))"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="Pool" PropertyName="SelectedValue" Type="Boolean" /> </SelectParameters> </asp:AccessDataSource>
ConvertEmptyStringToNull is set to true by default on ControlParameter so by setting the "Any" value on my DropDownList to an empty string I can pass null to the select query. The select query is constructed such that the Pool filter is applied unless @Pool is null. I spent a long time staring at this wondering why it wasn't working until I discovered the CancelSelectOnNullParameter which basically stops the select query executing if any of the parameters are null. Set this to false and all works nicely.
This is probably fairly obvious but it caught me out and I didn't find much useful info when I searched around.