Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-22
15
Medium Priority
?
545 Views
Last Modified: 2012-08-28
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
Comment
Question by:Cluskitt
  • 6
  • 5
  • 4
15 Comments
 
LVL 2

Expert Comment

by:itmarsolutions
ID: 38320971
why couldnt you loop through the selected item in that listview and then insert into database?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38321007
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38323487
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 2

Expert Comment

by:itmarsolutions
ID: 38323544
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38323582
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
 
LVL 2

Expert Comment

by:itmarsolutions
ID: 38323599
SELECT * FROM MyTable Where ( Field1 between 211 and 216) or ( Field1 between 500 and 600)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38323669
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
 
LVL 18

Author Comment

by:Cluskitt
ID: 38323846
@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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38323950
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
 
LVL 18

Author Comment

by:Cluskitt
ID: 38323998
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
 
LVL 2

Expert Comment

by:itmarsolutions
ID: 38324120
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38324138
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
 
LVL 2

Expert Comment

by:itmarsolutions
ID: 38324149
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
 
LVL 18

Accepted Solution

by:
Cluskitt earned 0 total points
ID: 38324181
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
 
LVL 18

Author Closing Comment

by:Cluskitt
ID: 38340032
Had to change to OR (obviously), but it works as intended.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question