• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

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?
0
Cluskitt
Asked:
Cluskitt
  • 6
  • 5
  • 4
1 Solution
 
itmarsolutionsCommented:
why couldnt you loop through the selected item in that listview and then insert into database?
0
 
CluskittAuthor Commented:
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.
0
 
Alan WarrenApplications DeveloperCommented:
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
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
itmarsolutionsCommented:
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
0
 
Alan WarrenApplications DeveloperCommented:
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.
0
 
itmarsolutionsCommented:
SELECT * FROM MyTable Where ( Field1 between 211 and 216) or ( Field1 between 500 and 600)
0
 
Alan WarrenApplications DeveloperCommented:
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)
0
 
CluskittAuthor Commented:
@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.
0
 
Alan WarrenApplications DeveloperCommented:
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)
0
 
CluskittAuthor Commented:
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.
0
 
itmarsolutionsCommented:
0
 
CluskittAuthor Commented:
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.
0
 
itmarsolutionsCommented:
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.
0
 
CluskittAuthor Commented:
As far as I can see, I can't use that to search ranges. It uses those parameters to insert into a table and then search as a simple list. Having that or |3|6|15| and doing a charindex is the same thing. Using those values, I would want something like:
MyCode BETWEEN 3 AND 4 OR MyCode BETWEEN 6 AND 10 OR MyCode BETWEEN 15 AND 25

I could pass even values, so I do something like:
MyCode BETWEEN 3 AND 6 OR MyCode BETWEEN 15 AND 25

But I wouldn't need xml parameters for that, as I would have to fill a table with them and that would make it harder to check for ranges.

In fact, I believe I have come up with a solution. I have to use concatenation for the query, but with parameters:
        For z = 1 To lst_Range.Items.Count - 1
          If lst_Range.Items(z).Selected Then
            sqlDA.SelectCommand.CommandText &= "AND MyCode BETWEEN @sCodeI" & z & " AND @sCodeF" & z & " "
            sqlDA.SelectCommand.Parameters.Add("@sCodeI" & z, SqlDbType.VarChar, 3).Value = Left(lst_Range.Items(z).Value, InStr(lst_Range.Items(z).Value, "|") - 1)
            sqlDA.SelectCommand.Parameters.Add("@sCodeF" & z, SqlDbType.VarChar, 3).Value = Mid(lst_Range.Items(z).Value, InStr(lst_Range.Items(z).Value, "|") + 1)
          End If
        Next

This way I can protect myself from SQL injection and keep all the code in one place for easier replacement.
0
 
CluskittAuthor Commented:
Had to change to OR (obviously), but it works as intended.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now