asked on

Parametrized query: How to search with multiple ranges from a multiple choice listbox?

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 CHARINDEX('|'+MyField+'|',@MyParam)>0
or
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" />
</asp:ListBox>

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?

itmarsolutions

why couldnt you loop through the selected item in that listview and then insert into database?

Cluskitt

ASKER

You missed the point. This is a page where the user is loading a list according to some parameters. One of the parameters is a range. Normally, I would do something like:
sqlDA.SelectCommand.CommandText = "SELECT * FROM MyTable WHERE CHARINDEX('|'+MyField+'|',@MyParam)>0"
For z = 1 To lst_Range.Items.Count - 1
If lst_Range.Items(z).Selected Then
sqlDA.SelectCommand.Parameters("@MyParam").Value &= lst_Range.Items(z).Value & "|"
End If
Next

But this doesn't work when I want it to return ranges. If I were using query concatenation, this would be easy. But parameters are indispensable.

Alan Warren

Hi itmarsolutions,

Could you post a sample of the SQL you would use if you were executing the SQL script in SQL query editor or SQL server management studio?

Show us what works, where the rubber hits the road.

eg.
SELECT * FROM MyTable WHERE somefield=111 or somefield=211

Is your listbox limited to 4 items, or do you have more?

A stored procedure may be more suited to your needs, easy enough to do, just need some sample SQL that returns desired results.

Alan

itmarsolutions

here is a sample

change your listbox to the below

<asp:ListBox ID="lst_Range" runat="server" SelectionMode="Multiple">
<asp:ListItem Value="111|2" Text="Range1" />
<asp:ListItem Value="211|5" Text="Range2" />
<asp:ListItem Value="400|50" Text="Range3" />
<asp:ListItem Value="500|100" Text="Range4" />
</asp:ListBox>

and in code behind build the where query as below

string Query = "SELECT * FROM MyTable ", Where = "";
foreach (ListItem li in lst_Range.Items)
{
if (!li.Selected) continue;
string[] vals = li.Value.Split('|');
int startVal = Convert.ToInt16(vals[0]);
int endVal = startVal + Convert.ToInt16(vals[1]);

Where += " or ( Field1 between " + startVal + " and " + endVal + ")";

}
Query = Query + " Where " + Where.Substring(3);

i hope this will help

Alan Warren

Hi itmarsolutions,

I meant can we see what the final SQL string looks like.
Put a breakpoint (F9) on this line:
Query = Query + " Where " + Where.Substring(3);
then invoke the Immediate window (CTRL+ALT+I) and type in:
?Query

Then post the results here.

itmarsolutions

SELECT * FROM MyTable Where ( Field1 between 211 and 216) or ( Field1 between 500 and 600)

Alan Warren

Apologies!
My previous posts were directed to the author of the question @Cluskitt.

@itmarsolutions,
your SQL looks fine; and is possibly exactly what the author is looking for.

I'm looking for some real fieldnames and table names from the author, along with a sample of SQL that would get the job done if executed in a SQL development environment.

Alan ";0)

Cluskitt

ASKER

@itmarsolutions: You solution uses string concatenation for building the query and is thus open to SQL injection. I did specify that I had to use a parametrized query, otherwise I could have done that myself.

@alanwarren: The query itself isn't important. There are actually much more items, but those 4 are enough for the example. You can use the following query:
SELECT * FROM Table1 WHERE CHARINDEX('|'+MyCode+'|',@sCode)>0

Using this with this:
For z = 1 To lst_Range.Items.Count - 1
If lst_Range.Items(z).Selected Then
sqlDA.SelectCommand.Parameters("@MyParam").Value &= lst_Range.Items(z).Value & "|"
End If
Next

Will work when the codes are the same as the values in the listbox. I just need to adapt it to work with ranges. I am aware I could do it with a stored procedure. I was just trying to keep it similar to other pages, if at all possible.

Alan Warren

Hi Clusskit,

you might need to pass a couple more params and do something like this, (pseudocode).

sqlDA.SelectCommand.Parameters("@MyLowerRange").Value &= lst_Range.Items(z).Value & "|"
sqlDA.SelectCommand.Parameters("@MyUpperRange").Value &= lst_Range.Items(z).Value + getUpperRange(lst_Range.Items(z).SelectedItem, lst_Range.Items(z).Value ) & "|"

Function getUpperRange(strRange as string,intLowerRange as int32) as Int32
  dim intUpperRange as int32 =0
  select case strRange 
  case is  = "Range1", "Range2"
     intUpperRange = intLower + 5
  case is  = "Range3","Range4"
    intUpperRange = intLower + 99
  case else
  end select
  getUpperRange = intUpperRange 
End Function

' Then you select something like:
  SELECT * FROM Table1 WHERE (CHARINDEX('|'+MyCode+'|',@sCode)>0 ) AND (@sCode between @MyLowerRange AND @MyUpperRange)

Open in new window

Personally reckon you should bite the bullet and do it as a sproc.
Alan ';0)

Cluskitt

ASKER

That would work for a single choice provided I remove the charindex (which was changed into a range). However, the user could chose multiple items and each would have to have a range.

I think I'll have to do a proc with a dynamic query. I have done similar things, but I was trying to avoid this. If I make a change to the DB, I'll have to change the queries in 2 places (VB and proc) rather than changing in a single place.

itmarsolutions

then you have to use xml parameters. have a look at this.
http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

Cluskitt

ASKER

XML parameters don't seem to work for multiple ranges. They're pretty much like CHARINDEX. I don't need a list of values, I need multiple start and end values.

itmarsolutions

yes it is possible through xml parameters.. did u checked the link i have given..

EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'

it is passing as multiple range.

ASKER CERTIFIED SOLUTION

Cluskitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cluskitt

ASKER

Had to change to OR (obviously), but it works as intended.