Tableadapter Query Configuration Wizard Too Smart

[EagleTempest]

VS 2005 SP1

I want to have

WHERE (@RegionID IS NULL OR Region = @RegionID)

but instead the wizard always changes it to

WHERE (@RegionID IS NULL) OR (Region = @RegionID)

which is not the same thing. Does anyone have any ideas how to stop the wizard from being too smart?

 

[SgtJarrow]

Unless you have more to your WHERE clause, I think these two statements would evaluate the same. If you have more to your WHERE clause, wrap these two evaluations in a set of parens should keep them grouped as a logical pair.

WHERE [b]([/b](@RegionID IS NULL) OR (Region = @RegionID)[b])[/b] AND ...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[EagleTempest]

The first statement evaluates to

If RegionID = NULL THEN display all records ELSE filter by Region

the second statement evaluates to

If RegionID = NULL OR a value THEN filter by NULL or value

 

[AlexCuse]

you already asked this question:

http://www.tek-tips.com/viewthread.cfm?qid=1418427&page=1

First of all, you are incorrect in your assessment. They both will evaluate *exactly* the same unless there is more to your where clause that you are not sharing. And if that is the case, you need to show your whole where clause because the example given is not where the difference comes from.

Second, I do not think you understand how where clauses are processed by a database. If OR is used, then you will get all rows from the table where one OR the other is true, regardless of how the parentheses are arranged.

Have you even tested the queries with the different where clause layouts against the database (without going through .net) to confirm that they don't return the same results, as you claim?

If not, you are kind of wasting everybody's time.

Hope this helps,

Alex




[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

 

[chrissie1]

Best advise, and I won't charge you for it.

DON'T USE THE WIZARD.

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett