help with sql where in statement within asp if statement

[omar999]

im using classic asp and sql server. my if statement checks if there is a price within 5 places [price_band_1 to 5] and then if any has a value shoud output 'I HAVE DATA' but it doesnt.. can anyone help?

i dont get any errors but Instead 'I HAVE NO DATA' is output on the page. this is clearly wrong if you look at my sample data from sql server

<%
'CHECK FOR PRICES
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=HIDDEN;" & _
"Initial Catalog=HIDDEN;User ID=HIDDEN;Password=HIDDEN"
objConn.Open
 
DIM SQL
SQL = "SELECT * FROM TESTCA_Specials WHERE ID IN (10, 14, 18)"
 
DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQL, objConn
 
'check to see if there are any prices
If (NOT IsNull(objRS("Price_Band_1"))) OR (NOT IsNull(objRS("Price_Band_2"))) OR _ 
(NOT IsNull(objRS("Price_Band_3"))) OR (NOT IsNull(objRS("Price_Band_4"))) OR _
(NOT IsNull(objRS("Price_Band_5"))) Then 
Response.Write "I HAVE DATA"
 
Else
Response.Write "I HAVE NO DATA"
 
End IF
 
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

sample data

ID    Flight_Route    Price_Band_1    Month_Band_1    Date_Band_1    Price_Band_2    Month_Band_2    Date_Band_2    Price_Band_3    Month_Band_3    Date_Band_3    Price_Band_4    Month_Band_4    Date_Band_4    Price_Band_5    Month_Band_5    Date_Band_5
10    GATWICK to MONTREAL    NULL    Nov    NO FLIGHT    NULL    Dec    NO FLIGHT    NULL    Jan    NO FLIGHT    NULL    Apr    NO FLIGHT    NULL    May    NO FLIGHT
14    GATWICK to VANCOUVER    500    Nov    25    300    Dec    9    50    Jan    27    250    Apr    7, 14, 28    NULL    May    NO FLIGHT
18    GLASGOW to CALGARY    NULL    Nov    NO FLIGHT    NULL    Dec    NO FLIGHT    NULL    Jan    NO FLIGHT    NULL    Apr    NO FLIGHT    NULL    May    NO FLIGHT

sample data


thanks in advance
Omar.

[jhardman]

Just a little trick, make a new field in your query that is the sum of the price bands. Then when you check you just have to check one field instead of this field or this field or this field...

select *, price_band_1+price_band_2+price_band_3+etc as pricesum from etc

if objRS("pricesum")<0
      'At least one of the prices have some value
Else
      Response.write "I have no value"
End if

[omar999]

what exactly does the col1+col2+ do within the sql query? does it merge both columns or does it mean col1 plus col2? so for example if col1 had 10 within it and col2 had 10 then it retains 20 as objRS("pricesum")?? am i right?

have tried your suggestion as

SQL = "select *, price_band_1+price_band_2+price_band_3+price_band_4+price_band_5 as pricesum from UK_Specials"
 
if objRS("pricesum")<0 THEN
      Response.Write "At least one of the prices have some value"
Else
      Response.write "I have no value"
End if

but this outputs 'I HAVE NO VAUE'..weird??

I've also tried this

if NOT ISNULL(objRS("pricesum")) THEN
      Response.write "At least one of the prices have some value"
Else
      Response.write "I have no value"
End if

but I get the same results as 'I HAVE NO VALUE' - I dont understand where im going wrong?

[jhardman]

It gives you a single column to check instead of checking if col1 > 0 or col2 > 0 or col3 > 0.

I think we put that less than bracket backwards - it should say if objRS("pricesum") > 0

Next try printing out that value to see if it is mathematically added or concatenated.

Jared

[omar999]

hi jared

ok i understand - i used the query in sql and saw an independant 'pricesum' column but the entire column is NULL which doesnt make sense. I get the same result once I print on front end web page...

so does this mean its neither mathematically adding or concatenating the values?

I also tried objRS("pricesum") > 0 as per your suggestion but I get the same I have no value output...

please advise

[omar999]

update: I've figured out the problem here. the price_band_5 column is NULL and contains no values.

I've verified this as I tried

select price_band_1,price_band_2,price_band_3,price_band_4,price_band_1+price_band_2+price_band_3+price_band_4 as pricesum from TESTCA_Specials

works fine as it seems to be mathematically adding the values on these 4 columns.

is there any way to get around if a column is null to skip/ignore?

thanks a million
Omar.

[jhardman]

Ok, this is odd, and you are right, it is not doing addition or concatenation. What data types are the price columns?

Jared

[jhardman]

Select isnull(column, 0)

if the value is null then isnull will return zero (or whatever you put as the second argument)

Jared

[omar999]

hi jared

the price field columns are of type integer.
does Select isnull(column, 0) replace null with 0 similar to the sql replace function?

we are so close! your suggestion Select isnull(column, 0) works fine if all 5 price_field columns on a record include at least 1 integer - then the 'pricesum' column adds up all 5 columns and ignores any null columns.

problem is now that if all 5 price_field columns on a record are null then the 'pricesum' column prints 0.. obviously 0 + 0 + 0+ 0+ 0 = 0

is there any way around this?

omar.

[jhardman]

Sorry, why is that a problem? If they add up to 0 then obviously they were all null, right? Unless you might have 0 in some columns and you need to distinguish between 0 and null, then obviously isnull(column, 0) isn't going to work for you. So why is it a problem if you get 0 when all of the values are null?

If pricesum > 0 then
      'Atleast one price has a value
Else
      'All values are null
End if

Jared

[jhardman]

Wait, you are putting the isnull() on the individual column names, right? Don't put isnull just on the sum, that would be a waste of time.

Jared

[omar999]

hi jared

thats right im putting the isnull() on the individual column names.

sorry for the confusion but I was just getting myself confused as I wanted to do a check if there are any prices within for example 3 records on all 5 columns. I've achieved this by doing

select pricesum = SUM(pricesum) from
(Select isnull(price_band_1,0)+isnull(price_band_2,0)+isnull(price_band_3,0)+isnull(price_band_4,0)+isnull(price_band_5,0) 
as pricesum from TESTCA_Specials WHERE ID IN (26,27,28,29,30,31) ) x

jared - you are the man. thanks for all your help. would have been stuck otherwise :)

cheers
omar

[omar999]

problem solved