By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,162 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,162 IT Pros & Developers. It's quick & easy.

help with sql where in statement within asp if statement

100+
P: 120
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

Expand|Select|Wrap|Line Numbers
  1. <%
  2. 'CHECK FOR PRICES
  3. DIM objConn
  4. Set objConn = Server.CreateObject("ADODB.Connection")
  5. objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=HIDDEN;" & _
  6. "Initial Catalog=HIDDEN;User ID=HIDDEN;Password=HIDDEN"
  7. objConn.Open
  8.  
  9. DIM SQL
  10. SQL = "SELECT * FROM TESTCA_Specials WHERE ID IN (10, 14, 18)"
  11.  
  12. DIM objRS
  13. Set objRS = Server.CreateObject("ADODB.Recordset")
  14. objRS.Open SQL, objConn
  15.  
  16. 'check to see if there are any prices
  17. If (NOT IsNull(objRS("Price_Band_1"))) OR (NOT IsNull(objRS("Price_Band_2"))) OR _ 
  18. (NOT IsNull(objRS("Price_Band_3"))) OR (NOT IsNull(objRS("Price_Band_4"))) OR _
  19. (NOT IsNull(objRS("Price_Band_5"))) Then 
  20. Response.Write "I HAVE DATA"
  21.  
  22. Else
  23. Response.Write "I HAVE NO DATA"
  24.  
  25. End IF
  26.  
  27. objRS.Close
  28. Set objRS = Nothing
  29. objConn.Close
  30. Set objConn = Nothing
  31. %>
sample data
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 10    GATWICK to MONTREAL    NULL    Nov    NO FLIGHT    NULL    Dec    NO FLIGHT    NULL    Jan    NO FLIGHT    NULL    Apr    NO FLIGHT    NULL    May    NO FLIGHT
  3. 14    GATWICK to VANCOUVER    500    Nov    25    300    Dec    9    50    Jan    27    250    Apr    7, 14, 28    NULL    May    NO FLIGHT
  4. 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.
Nov 26 '10 #1
Share this Question
Share on Google+
12 Replies


jhardman
Expert 2.5K+
P: 3,405
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...
Expand|Select|Wrap|Line Numbers
  1. select *, price_band_1+price_band_2+price_band_3+etc as pricesum from etc
Expand|Select|Wrap|Line Numbers
  1. if objRS("pricesum")<0
  2.       'At least one of the prices have some value
  3. Else
  4.       Response.write "I have no value"
  5. End if
Nov 27 '10 #2

100+
P: 120
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
Expand|Select|Wrap|Line Numbers
  1. SQL = "select *, price_band_1+price_band_2+price_band_3+price_band_4+price_band_5 as pricesum from UK_Specials"
  2.  
  3. if objRS("pricesum")<0 THEN
  4.       Response.Write "At least one of the prices have some value"
  5. Else
  6.       Response.write "I have no value"
  7. End if
but this outputs 'I HAVE NO VAUE'..weird??

I've also tried this
Expand|Select|Wrap|Line Numbers
  1. if NOT ISNULL(objRS("pricesum")) THEN
  2.       Response.write "At least one of the prices have some value"
  3. Else
  4.       Response.write "I have no value"
  5. End if
but I get the same results as 'I HAVE NO VALUE' - I dont understand where im going wrong?
Nov 27 '10 #3

jhardman
Expert 2.5K+
P: 3,405
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
Nov 27 '10 #4

100+
P: 120
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
Dec 1 '10 #5

100+
P: 120
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 1 '10 #6

jhardman
Expert 2.5K+
P: 3,405
Ok, this is odd, and you are right, it is not doing addition or concatenation. What data types are the price columns?

Jared
Dec 1 '10 #7

jhardman
Expert 2.5K+
P: 3,405
Expand|Select|Wrap|Line Numbers
  1. Select isnull(column, 0)
if the value is null then isnull will return zero (or whatever you put as the second argument)

Jared
Dec 1 '10 #8

100+
P: 120
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.
Dec 2 '10 #9

jhardman
Expert 2.5K+
P: 3,405
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?
Expand|Select|Wrap|Line Numbers
  1. If pricesum > 0 then
  2.       'Atleast one price has a value
  3. Else
  4.       'All values are null
  5. End if
Jared
Dec 2 '10 #10

jhardman
Expert 2.5K+
P: 3,405
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
Dec 2 '10 #11

100+
P: 120
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
Expand|Select|Wrap|Line Numbers
  1. select pricesum = SUM(pricesum) from
  2. (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) 
  3. 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
Dec 4 '10 #12

100+
P: 120
problem solved
Dec 4 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.