Problems using Select query in sql server & asp.net

Question

SELECT * 
FROM [productDetail] 
WHERE 
     Price BETWEEN 0 AND 2000 
     OR Price BETWEEN 2000 AND 12000 
     AND CategoryID = 2  
     AND caravanOffline = 0 
ORDER BY 
     price

The query doesn't produce the desired output, it also shows the products whose categoryID is 3,1 etc.

Can any one spot the problem in the query.

Updated code:

static public DataTable GetSelectedFilterMotorhomes(ArrayList caravans)
{

    string sqldefault = "Select * from productDetail Where";
    string sqlBuilder = "";
    int starter = 0;
    int count = caravans.Count;
    string sqlOperator = "OR";

    if (caravans.Count > 0)
    {

        while (count > starter) //build the query depending on the number of caravans.selected.count 
        {

            sqlBuilder += "((Price between " + caravans[count - 1] + "))" + sqlOperator;
            count--;


        }

        string addQuery = sqldefault + sqlBuilder;
        string removeOR = addQuery.Substring(0, addQuery.Length - 2);  //remove OR
        string finalQuery = removeOR + "AND (CategoryID = 2)  AND (caravanOffline = 0)  order by Price"; //Display caravans in ascending order

        SqlDataAdapter da = new SqlDataAdapter(finalQuery, ConnectionString);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    else
    {
        string returnAllCaravans = "Select * from productDetail WHERE CategoryID = 2 AND caravanOffline = 0";

        SqlDataAdapter da = new SqlDataAdapter(returnAllCaravans, ConnectionString);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

}

It would be helpful if you could let us know what you are trying to achieve with the WHERE clause, it's rather vague. — TabbyCool
– TabbyCool, Commented May 31, 2011 at 11:03
Actually , i have got the checklist box on the website , query builts on the number of check box user checks, there is a price category which have different range for instance in the above example i have just given two ranges — Mr A
– Mr A, Commented May 31, 2011 at 11:13
It's still not clear what you actually want, can't you just explain what you want the query to bring back? — TabbyCool
– TabbyCool, Commented May 31, 2011 at 11:16

Ryan · Accepted Answer · 2011-05-31 10:57:53Z

3

You may need some parenthesis? I'd need to know exactly what you were trying to pull back with your query but likely you need a parenthesis after the OR and then again either after the CategoryID = 2 or after the caravanOffline = 0. See if either of the following queries returns what you're looking for.

Select
    *
from [productDetail]
WHERE
    Price between 0 AND 2000
    OR (Price between 2000 AND 12000 AND CategoryID = 2) AND caravanOffline = 0
order by price

Select
    *
from [productDetail]
WHERE
    Price between 0 AND 2000
    OR (Price between 2000 AND 12000 AND CategoryID = 2 AND caravanOffline = 0)
order by price

answered May 31, 2011 at 10:57

Ryan

6,86613 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TabbyCool Over a year ago

They're not, the first one always uses the caravanOffline = 0 restriction, whereas the last one only applies it to caravans in the 2000 to 12000 price range with a category of 2.

marc_s · Accepted Answer · 2011-05-31 11:00:25Z

1

What do you really want to do with your WHERE clause?? Without any parenthesis, it's not clear at all what you're trying to select

DO you really mean:

WHERE 
     (Price BETWEEN 0 AND 2000 OR Price BETWEEN 2000 AND 12000)
     AND CategoryID = 2  
     AND caravanOffline = 0

Price is between 0 and 2000, or between 2000 and 12000 (doesn't really make sense), AND in any case, the CategoryID is 2 ?

Or do you mean:

WHERE 
     Price BETWEEN 0 AND 2000 
     OR 
     (Price BETWEEN 2000 AND 12000 AND CategoryID = 2 AND caravanOffline = 0)

The price is either between 0 and 2000 (with no other restriction) OR the price is between 2000 and 12000, but only if the CategoryID = 2 and CaravanOffline = 0 are true at the same time) ??

Or are you looking for something else than these two options??

answered May 31, 2011 at 11:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

Mr A Over a year ago

Actually , i have got the checklist box on the website , query builts on the number of check box user checks, there is a price category which have different range for instance in the above example i have just given two ranges

marc_s Over a year ago

@Muhammad Awais: you need to explain (in plain English) what you're trying to do - not just post more code which still doesn't make it clear what your intention is....

TabbyCool · Accepted Answer · 2011-06-02 08:19:22Z

1

I think it might need some brackets...

SELECT * FROM productDetail
WHERE Price BETWEEN 0 AND 2000 
OR (Price BETWEEN 2000 AND 12000
AND CategoryId = 2
AND caravanOffline = 0)
ORDER BY price

Assuming that's the logic you want! Your question is rather vague, I'm assuming that you want caravans under 2000 regardless of category and caravanOffline, or caravans between 2000 and 12000 with a categoryid of 2 and caravanOffline of 0.

edited Jun 2, 2011 at 8:19

answered May 31, 2011 at 10:59

TabbyCool

2,8391 gold badge25 silver badges36 bronze badges

Collectives™ on Stack Overflow

Problems using Select query in sql server & asp.net

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related