Selective Filtering in ASP.NET


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.

Technorati Tags: ,
Comments (3)
  1. Bruce Lynn says:

    Mike – Sorry it came across like Dave gave me *right* info because really both of you have been a big help.  You got me going on the ASP.net route with my pretty rusty coding skills and nearly non-existent knowledge of current day approaches.  Dave gave me some great guidance, but your and my ‘dabble’ sorted out a real stumbling blocks and your solution above works a treat.  

    You also must do a posting on all the cool Virtual Earth stuff you showed me how to incorporate so easily into the site.  I’ll post a further comment when the site goes live.  Thanks for the great guidance throughout.

  2. MikeO [MSFT] says:

    No offence taken 🙂 I just thought it was a funny and it made Dave laugh too…

Comments are closed.

Skip to main content