Parametrized query: How to search with multiple ranges from a multiple choice listbox?
Posted on 2012-08-22
I have a few pages where the users can select multiple values from a listbox. Usually, the way I adapt this to a parametrized query is to concatenate all selected values separated by a "|" (with one at start and end as well), then, on my query, I'll do something like:
WHERE @MyParam LIKE '%|'+MyField+'|%'
This works fine for normal values. However, I now have a list that contains values that should be ranges. Like:
<asp:ListBox ID="lst_Range" runat="server" SelectionMode="Multiple">
<asp:ListItem Value="111" Text="Range1" />
<asp:ListItem Value="211" Text="Range2" />
<asp:ListItem Value="400" Text="Range3" />
<asp:ListItem Value="500" Text="Range4" />
Now, Range1 and Range2 start at the value and end at value+5. The other 2 start at value and end at value+99. How would I go about to set a parametrized query based on this? Keep in mind that the user may select more than one option. If, for example, the user picks Range2 and Range4, the query should return rows that have values 211 to 216 and 500 to 599.
Is there any simple way of doing this?