Need help with a simple(ish) SELECT statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with a simple(ish) SELECT statement

Author

Topic

alexellice
Starting Member

13 Posts

Posted - 2011-11-19 : 06:00:37

First of all.. a huge thankyou to the people here supporting this community.

I'll get straight to my question...

I'm try to create a database for houses to be sold etc. It all works fine but... I have a checkbox (using .net with c# btw) that is called [HouseIsVisible]. When i run this test script (below), it returns the correct results.

quote:

DECLARE @LIVINGAREAHIGH INT, @LIVINGAREALOW INT, @GROUNDAREAHIGH INT, @GROUNDAREALOW INT, @HOUSETYPE1 NVARCHAR(50),@HOUSETYPE2 NVARCHAR(50),@HOUSETYPE3 NVARCHAR(50),@HOUSETYPE4 NVARCHAR(50),@PRICEHIGH FLOAT, @PRICELOW FLOAT, @BRUTTOHIGH FLOAT, @BRUTTOLOW FLOAT, @NETTOHIGH FLOAT, @NETTOLOW FLOAT

SET @LIVINGAREALOW = 10
SET @LIVINGAREAHIGH = 500
SET @GROUNDAREALOW = 0
SET @GROUNDAREAHIGH = 50000
SET @PRICELOW = 0
SET @PRICEHIGH = 7000000
SET @BRUTTOLOW = 0
SET @BRUTTOHIGH = 50000
SET @NETTOLOW = 0
SET @NETTOHIGH = 50000
SET @HOUSETYPE1 = 'Lejlighed'
SET @HOUSETYPE2 = 'landejendom'
SET @HOUSETYPE3 = 'Rækkehus'
SET @HOUSETYPE4 = 'Villa'

SELECT * FROM BS_Tlb_SearchHouses

WHERE HouseIsVisible = 'true'
AND
BS_Tlb_SearchHouses.HouseType = @HOUSETYPE1 or
BS_Tlb_SearchHouses.HouseType = @HOUSETYPE2 or
BS_Tlb_SearchHouses.HouseType = @HOUSETYPE3 or
BS_Tlb_SearchHouses.HouseType = @HOUSETYPE4

AND
BS_Tlb_SearchHouses.LivingArea BETWEEN @LIVINGAREALOW AND @LIVINGAREAHIGH
AND
BS_Tlb_SearchHouses.GroundArea BETWEEN @GROUNDAREALOW AND @GROUNDAREAHIGH
AND
BS_Tlb_SearchHouses.HousePrice BETWEEN @PRICELOW AND @PRICEHIGH
AND
BS_Tlb_SearchHouses.HouseRent_Brutto BETWEEN @BRUTTOLOW AND @BRUTTOHIGH
AND
BS_Tlb_SearchHouses.HouseRent_Netto BETWEEN @NETTOLOW AND @NETTOHIGH

Now to my problem...

When i move this script to a dataset in c# it looks like this ....

quote:

SELECT HouseID, HouseSubject, HouseType, LivingArea, GroundArea, HousePrice, HouseRent_Brutto, HouseRent_Netto, HouseIsVisible, HouseIsSold, HouseTitle, HouseDescr, HouseImg1, HouseImg2, HouseImg3, HouseImg4, HouseImg1_thb, HouseImg2_thb, HouseImg3_thb, HouseImg4_thb, HouseImgSelected

FROM BS_Tlb_SearchHouses
WHERE (HouseIsVisible = 'true')
AND (HouseType = @HOUSETYPE1) OR
(HouseType = @HOUSETYPE2) OR
(HouseType = @HOUSETYPE3) OR
(HouseType = @HOUSETYPE4)

AND (LivingArea BETWEEN @LIVINGAREALOW AND @LIVINGAREAHIGH)
AND (GroundArea BETWEEN @GROUNDAREALOW AND @GROUNDAREAHIGH)
AND (HousePrice BETWEEN @PRICELOW AND @PRICEHIGH)
AND (HouseRent_Brutto BETWEEN @BRUTTOLOW AND @BRUTTOHIGH)
AND (HouseRent_Netto BETWEEN @NETTOLOW AND @NETTOHIGH)

This returns ALL houses.. regardless if [(HouseIsVisible] or not.

1: Why does this work in a srcipt with SQL 2008 and not with a dataset in c#?
2: Which improvements are needed to make it work?

If it's needed to solve this problem.. this is the method used to call the dataset :

quote:
BS_Tlb_SearchHousesTableAdapter _searchHouses = new BS_Tlb_SearchHousesTableAdapter();

rptShowSearchResults.DataSource = searchHouses.GetDataBySearchResults(_houseType1, _houseType2,
_houseType3, _houseType4,_livingAreaLow, _livingAreaHigh, _goundAreaLow, _groundAreaHigh, _housePriceLow,_housePriceHigh, _houseBruttoLow, _houseBruttoHigh, _houseNttoLow, _houseNettoHigh);
rptShowSearchResults.DataBind();

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-11-19 : 06:51:54

hi,

if your procedure or script to retrieve information from your table BS_Tlb_SearchHouses is working fine, than you have problem in your C# application.

can you also send DDL definition of your BS_Tlb_Searchhouses.

alexellice
Starting Member

13 Posts

Posted - 2011-11-19 : 07:13:12

What do you mean by DDL ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-19 : 07:37:59

DataDefinitionLanguage --> for example the CREATE-Statement for a table

see here: http://blog.sqlauthority.com/2008/01/15/sql-server-what-is-dml-ddl-dcl-and-tcl-introduction-and-examples/

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 07:45:14

Why does this work in a srcipt with SQL 2008 and not with a dataset in c#?
because thats how you've coded it

see the below condition


....
WHERE (HouseIsVisible = 'true') 
AND (HouseType = @HOUSETYPE1) OR
(HouseType = @HOUSETYPE2) OR
(HouseType = @HOUSETYPE3) OR
(HouseType = @HOUSETYPE4) 
...

because of OR conditions if HouseType is any one of @HOUSETYPE2,@HOUSETYPE3 etc it will bypass the HouseIsVisible = 'true' condition and will return them

Which improvements are needed to make it work?
just make them like


....
WHERE (HouseIsVisible = 'true') 
AND ((HouseType = @HOUSETYPE1) OR
(HouseType = @HOUSETYPE2) OR
(HouseType = @HOUSETYPE3) OR
(HouseType = @HOUSETYPE4) )
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

alexellice
Starting Member

13 Posts

Posted - 2011-11-19 : 10:36:00

thankyou so much!!! and ofc... when I saw what you wrote... it made sense.

It works fine now...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 12:55:26

quote:
Originally posted by alexellice

thankyou so much!!! and ofc... when I saw what you wrote... it made sense.

It works fine now...

No probs

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-11-19 : 13:15:55

good one visakh16.
i didn't even notice the missing brackets :)
(bow)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 14:18:26

quote:
Originally posted by slimt_slimt

good one visakh16.
i didn't even notice the missing brackets :)
(bow)

Thats ok

Its something which I myself have overlooked quite a lot and faced issues with

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources