help with asp classic if else loop

[omar999]

hi guys

im having trouble with my asp if else loop. I'm very close but not 100% quite there yet.

in my sql server table I have flight routes which consist of prices, dates, months - I am then pulling this out using classic asp on my site

what i want is
1. if all 5 prices are NULL to not display anything.
2. and only display rows that have a price. e.g I wouldnt want to display row 1 & 4 by looking at the same data below. so in the sample data's case just to skip row 1 and row 4.

sample data

GATWICK to TORONTO
Price 	Flight Date/s
£
£219 	Dec 7
£149 	Jan 13, 14, 16, 20, 21, 23
£179 	Feb 3, 4, 6, 10, 11, 13, 17, 18, 24, 25, 27
£

in my asp logic I can skip the first row, display the next 3 rows and skip the 4th row - see below

<%
'DISPLAY PRICES
DIM objConn1
Set objConn1 = Server.CreateObject("ADODB.Connection")
objConn1.ConnectionString = "Provider=SQLOLEDB;Data Source=IPREMOVED" & _
"Initial Catalog=DBNAMEREMOVED;User ID=IDREMOVED;Password=PASSWORDREMOVED"
objConn1.Open

DIM FLIGHTROUTE1
FLIGHTROUTE1 = "SELECT * FROM UK_Specials WHERE ID = 1"

DIM objRS1
Set objRS1 = Server.CreateObject("ADODB.Recordset")
objRS1.Open FLIGHTROUTE1, objConn1

'retreive 1st row/date band routes
If (IsNull(objRS1("Price_Band_1"))) Then
    Response.Write ""
Else
Response.Write "<div id=""outboundroute"">" & objRS1("Anchor_Tag") 
Response.Write "<fieldset class=""fieldsetspecialpricebox"">" & vbCrLf
Response.Write "<legend>" & vbCrLf & "<span class=""specialtitlesmall"">" & objRS1("Flight_Route") & "</span>" & vbCrLf & "</legend>" & vbCrLf
Response.Write "<table align=center cellpadding=0 cellspacing=0 class=""specialpricetable"">" & vbCrLf
Response.Write "<tr><td class=""dateheadertd"">Flight Date/s</td></tr>" & vbCrLf
Response.Write "<tr><td height=10>&nbsp;</td></tr>" & vbCrLf

'write your table content
Response.Write "<tr><td class=""datetd""><span class=""monthspan"">" & objRS1("Month_Band_1") & "&nbsp;</span>" & objRS1("Date_Band_1") & "</td></tr>"
End If

'retreive 2nd row/date band routes
If (IsNull(objRS1("Price_Band_2"))) Then
    Response.Write ""

Else
Response.Write "<tr><td class=""datetd""><span class=""monthspan"">" & objRS1("Month_Band_2") & "&nbsp;</span>" & objRS1("Date_Band_2") & "</td></tr>"
End If

'retreive 3rd row/date band routes
If (IsNull(objRS1("Price_Band_3"))) Then
    Response.Write ""

Else
Response.Write "<tr><td class=""datetd""><span class=""monthspan"">" & objRS1("Month_Band_3") & "&nbsp;</span>" & objRS1("Date_Band_3") & "</td></tr>"
End If

'retreive 4th row/date band routes
If (IsNull(objRS1("Price_Band_4"))) Then
    Response.Write ""

Else
Response.Write "<tr><td class=""datetd""><span class=""monthspan"">" & objRS1("Month_Band_4") & "&nbsp;</span>" & objRS1("Date_Band_4") & "</td></tr>"
End If

'retreive 5th row/date band routes
If (IsNull(objRS1("Price_Band_5"))) Then
    Response.Write ""

Else
Response.Write "<tr><td class=""datetd""><span class=""monthspan"">" & objRS1("Month_Band_5") & "&nbsp;</span>" & objRS1("Date_Band_5") & "</td></tr>"
End If

Response.Write "</tr></table></fieldset>"
Response.Write "</div>"

objRS1.Close
Set objRS1 = Nothing
objConn1.Close
Set objConn1 = Nothing
%>

but to cover all angles i think i need the first if statement to check if price band 1 - 5 are empty then do nothing - otherwise build table header and proceed..

but i dont think this is the way to do a if is null check on multiple recordset values???

'check to see if there are any prices
If (IsNull(objRS1("Price_Band_1" & "Price_Band_2" & "Price_Band_3" & "Price_Band_4" & "Price_Band_5"))) Then  
Response.Write "" 

Else
'build table header
Response.Write "<div id=""outboundroute"">" & objRS1("Anchor_Tag") 
Response.Write "<fieldset class=""fieldsetspecialpricebox"">" & vbCrLf
Response.Write "<legend>" & vbCrLf & "<span class=""specialtitlesmall"">" & objRS1("Flight_Route") & "</span>" & vbCrLf & "</legend>" & vbCrLf
Response.Write "<table align=center cellpadding=0 cellspacing=0 class=""specialpricetable"">" & vbCrLf
Response.Write "<tr><td class=""dateheadertd"">Flight Date/s</td></tr>" & vbCrLf
Response.Write "<tr><td height=10>&nbsp;</td></tr>" & vbCrLf

'retreive 1st row/date band routes
If (IsNull(objRS1("Price_Band_1"))) Then
    Response.Write ""
Else

and so on...... etc%>

please advise

thanks in advance
Omar.

[danp129]

If I'm understanding correctly, I would just change the SQL statement to only pull records that do not have a null or empty value. Then after opening the recordset you can see if the recordset is empty.

If objRS1.EOF Then
'do nothing
Else
'your code here
End If

[omar999]

I've been working on this problem since friday and managed to come up with a solution. took me ages to work it out though

this is how I adapted my if statement to check for null/not null on multiple values which works great;

If (NOT IsNull(objRSLGWYUL("Price_Band_1"))) OR (NOT IsNull(objRSLGWYUL("Price_Band_2"))) OR (NOT IsNull(objRSLGWYUL("Price_Band_3"))) OR (NOT IsNull(objRSLGWYUL("Price_Band_4"))) OR (NOT IsNull(objRSLGWYUL("Price_Band_5"))) Then

I now have another problem. but I will try fixing it if not I will open a new thread.

thanks
Omar.

[sharmajv]

If not IsNull("COL1") or trim(COL1) = "" OR not IsNull("COL2") or trim(COL2) = "" ..... then

Else

End If