Avatar of BOEING39
BOEING39 asked on

WHERE CLAUSE

Please find the attached code utilized in a WHERE clause for a Gridview.   The Gridview displays;  however,  it is not filtering properly.   The only filter that is working is the "station" the "time" range and "date" ranges are not functioning.
SQL-FILTER.docx
ASP.NETC#Microsoft SQL Server

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
N-W

It's not functioning because you are using "&lt;" and "&gt;" in your SQL statements instead of "<" and ">".

Replace "&lt;" with "<" and "&gt;" with ">" in your SQL statement and it should work.

"&lt;" and "&gt;" is commonly used when writing HTML markup, but does not work for SQL queries.
kaufmed

"&lt;" and "&gt;" is commonly used when writing HTML markup
Ah, mon ami, but that is exactly what you are seeing:  ASP.NET markup.

I think this is a logic error. You have numerous AND & OR in your query. But you haven't grouped any of them together (via parentheses). This means you need to worry about precedence. If I recall correctly, AND binds tighter than OR, so anywhere that you have "something AND something-else OR another-something-else", you have effectively written:

(something AND something-else) OR another-something-else

Note the additional parens.

You need to employ a strategic placement of parentheses to force the query engine to use the precedence that you intended to occur.

P.S.

This is (effectively) what the query engine interprets your current query as:

[([Dates] &gt;= @Dates) AND ([Dates] &lt;= @Dates2) AND ([Dep] &gt;= @Dep) AND ([Dep] &lt;= @Dep2) AND ([Station] = @Station) AND (Code = 'MC')]

OR (Code = 'MM')
OR (Code = 'MA')
OR (Code = 'MF')
OR (Code = 'MH')
OR (Code = 'ML')
OR (Code = 'MN')

* Brackets removed from "Code" to satisfy the EE tag parser.
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Qlemo

BTW,
  [Dates] >= @Dates  AND [Dates] <= @Dates2
is exactly the same as
   [Dates] between @Dates and @Dates2
which is more clear (same for Dep), so the reworked WHERE would look like this:
WHERE [Dates] between @Dates and @Dates2
  AND [Dep] between @Dep and @Dep2
  AND [Station] = @Station
  AND [Code] IN ('MC', 'MM', 'MA', 'MF', 'MH', 'ML', 'MN')

Open in new window

On an additional note, take care in regard of dates and "between"-like conditions - the boundaries are included, which often is not what you want to have, because in
/* Query 1 */ .... where dt between @date1 and @date2 ...
/* Query 2 */ .... where dt between @date2 and @date3 ...

Open in new window

you would get the same row matching @date2 in both queries.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PortletPaul

Qlemo
the reason for my question Q1 and Q2 was to see if there is an inherent risk of the query being wrong :(

yes, you could use between - but I hate it for date ranges - and there is absolutely nothing inherently wrong with using >=  together with <=
although it may well need parentheses depending on answers to Q1 & Q2 IMHO

Beware of Between it's not always a good friend.
Qlemo

But there is no function difference using >= AND <= or BETWEEN - en contraire, the former obfuscates the intention. between makes it more clear what you think you ask for (of course that might not be what you get).
PortletPaul

we therefore disagree on the clarity
I believe >= with <=
is actually much more definitive (and clear)

there is a great deal of misunderstanding about the word between in the context of SQL simply because in common language it can have 3 basic meanings:

to fit between (> with <)
to fly between (>= with <=)
group of z between x and y (|>= with <| > with <=|)

clearly you know the SQL between is >= with <= and hence accommodate it: I'm not suggesting otherwise.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Qlemo

That pretty much covers everything ;-)
ASKER
BOEING39

I appreciate everyone's input on this.  Paul thanks for the edification and finite detail in your response.   I learned something and it is now functioning as needed.
PortletPaul

Really pleased it helped. Cheers Paul

ps, see: "Beware of Between"
Your help has saved me hundreds of hours of internet surfing.
fblack61