I have a ASP document:

Code:

________________________________________________

<%
Set rs=con.execute("select * from trip_master where agencyid = '"&agencyid&"' and regular_trip = 'Option'")
%>
<table width=700 cellspacing=1 cellpadding=2 border=1 align=center>
<tr bgcolor='#ececec'bordercolor='#cccccc'>
<td align='left'>
Name
</td>
<td width='10%' align=center>
Status
</td>
<td align='center'>
Booking No.
</td>
<td align='center'>
Departure Date
</td>
<td align='center'>
Itinerary
</td>
<td align='center'>
Booking Date
</td></tr>
<%
set rscrm = con.execute("select * from trip_details where trip_id = 'MK/"&rs("A1_pnr")&"' order by tatoo asc")
'response.write ("select * from trip_details where trip_id = 'MK/"&rs("A1_pnr")&"'")
set rscrm2 = con.execute("select dep_airport from trip_details where trip_id = 'MK/"&rs("A1_pnr")&"' order by tatoo asc")
'response.Write ("select dep_airport from trip_details where trip_id = 'MK/"&rs("A1_pnr")&"' order by tatoo asc")
departing_air = rscrm2("dep_airport")
%>
<% while not rs.eof %>
<tr bgcolor='#ececec'bordercolor='#cccccc'>
<td align='left'>
<%=rs("leadname")%>
</td>
<% if rs("status") = "TL" then %>
<td width='10%' align=center>
Option
</td>
<% else %>
<td width='10%' align=center>
<%=rs("status")%>
</td>
<% end if %>
<td align='center'>
<a href="mytrip_new.asp?pnrno=<%=rs("A1_pnr")%>"><%=r s("A1_pnr")%></a>
</td>
<td align='center'>
<%=rs("Destination_Arrival_Date")%>
</td>
<%
counter = 0
while not rscrm.eof
counter = counter+1

var_arr = rscrm("arr_airport")&"-"

arriving_air = arriving_air & var_arr

'abcd = var_arr+var_arr
'response.write "abcd = " & abcd & "<br>"
'response.write "departure airport = " & var & "<br>"
'response.write "arrival airport = " & var_arr & "<br>"
crm_iti = departing_air&"-"&arriving_air
rscrm.movenext
wend


%>
<td align="center">
<%=left(crm_iti,len(crm_iti)-1)%>
</td>
<td align="center">
<%=rs("date_of_booking")%>
</td>
</tr>
<%
rs.movenext
wend
%>
</table>
_______________________________________________

The problem i am having is that the when i am making the crm_iti variable it is only adding the 1st instance of it into the HTML table. Why is this? How can i get round it?

Heres my loop in more detail:

_______________________________________________

<%
counter = 0
while not rscrm.eof
counter = counter+1

var_arr = rscrm("arr_airport")&"-"

arriving_air = arriving_air & var_arr


rscrm.movenext
wend
crm_iti = departing_air&"-"&arriving_air

%>

____________________________________________

the variable crm_iti is added into the HTML table like this:

__________________________________________

<td align="center">
<%=left(crm_iti,len(crm_iti)-1)%>
</td>

Can anyone see the problem?

www.crmpicco.co.uk

__________________
_______________________
Ayrshire Minis - a Mini E-Community
http://www.ayrshireminis.com
http://www.crmpicco.co.uk

Did you initialize the variables.
I mean if it is string just provide str_var = "" after defining the variable.
I had the simillar problem last week which was solved in this manner

Shibu Narayanan
Software Associates

   It is not adding the first occcurance, it is the last. You have this line crm_iti = departing_air&"-"&arriving_air outside of the loop, so it only shows the last value once the loop ends.

I've tried re-arranging the loop to suit what you are saying benson, but it doesnt work:

<%
counter = 0
while not rscrm.eof
counter = counter+1

var_arr = rscrm("arr_airport")&"-"

arriving_air = arriving_air & var_arr

crm_iti = departing_air&"-"&arriving_air
rscrm.movenext
wend

%>


www.crmpicco.co.uk

Name Status Booking No. Departure Date Itinerary Booking Date
TEST, AAA Option 2B67TJ 12AUG2005 GLA-EWR-SAN-EWR-GLA 17/02/05
TEST, A Confirmed 2B8F9O 10MAY2005 GLA-EWR-SAN-EWR-GLA 17/02/05
TEST, A Confirmed Z4TXDO 11APR2005 GLA-EWR-SAN-EWR-GLA 16/02/05
TEST, A Confirmed Z2NWTI 01MAY2005 GLA-EWR-SAN-EWR-GLA 16/02/05

Thats my HTML table.

It always give me the same itinerary. How can i change this to suit in with the loop?

Picco


www.crmpicco.co.uk

It's because the query for the details is run before (outside) the loop of the master table, so the trip details don't change as the trip master is traversed.

You need to move this line:
set rscrm = con.execute("select * from trip_details where trip_id = 'MK/"&rs("A1_pnr")&"' order by tatoo asc")
inside the loop of the rs recordset

You also need to move the "set rscrm2 = ..." line, but I don't really see why you need a separate query. Can't you just read the "dep_airport" from the rscrm recordset? The 2 queries look exactly the same.

hth
Phil

Heres my code now:

<% ' Last Edit: CRM_04mar05 %>


<%
'userid=trim(request.cookies("userid"))
'agencyid=trim(request.cookies("agencyid"))
'agentid=trim(request.cookies("agentid"))

userid=Session("userid")
agencyid=Session("agencyid")
agentid=Session("agentid")

'response.write "User ID " & userid & "<br>"
'response.write "Agency ID " & agencyid & "<br>"
'response.write "Agent ID " & agentid & "<br>"



'ordby=trim(Request.QueryString("ordby"))
'searchname=trim(Request.QueryString("searchname") )
'searchagent=trim(Request.QueryString("searchagent "))

'timeoption =request.querystring("timeoption")
'response.write timeoption & "<br>"
'user=trim(request.querystring("searchby"))
'agencyid=trim(request.querystring("agencyid"))

'Response.Write userid
'Response.Write searchagent

function setdate(Fdate)
                        Select Case Fdate
                        Case "JAN"
                          validdate = "01"
                        Case "FEB"
                         validdate = "02"
                        Case "MAR"
                         validdate = "03"
                        Case "APR"
                         validdate = "04"
                        Case "MAY"
                         validdate = "05"
                        Case "JUN"
                         validdate = "06"
                         Case "JUL"
                         validdate = "07"
                        Case "AUG"
                         validdate = "08"
                         Case "SEP"
                         validdate = "09"
                        Case "OCT"
                         validdate = "10"
                        Case "NOV"
                         validdate = "11"
                         Case "DEC"
                         validdate = "12"
                        end select
                        setdate = validdate
                        end function
%>
<style>
a.{text-decoration:none}
</style>

<body bgcolor="#f5f5f5" alink="blue" link="blue" vlink="blue">
<form name="form">
<%
Set rs=con.execute("select * from trip_master where agencyid = '"&agencyid&"' and regular_trip <> 'Cancelled' limit 50,50")
%>
<table width=700 cellspacing=1 cellpadding=2 border=1 align=center>
<tr bgcolor='#ececec'bordercolor='#cccccc'>
<td align='left'>
Name
</td>
<td width='10%' align=center>
Status
</td>
<td align='center'>
Booking No.
</td>
<td align='center'>
Departure Date
</td>
<td align='center'>
Itinerary
</td>
<td align='center'>
Booking Date
</td></tr>
<%

'response.write ("select * from trip_details where trip_id = 'MK/"&rs("A1_pnr")&"'")
set rscrm2 = con.execute("select dep_airport from trip_details where trip_id = 'MK/"&rs("A1_pnr")&"' order by tatoo asc")
'response.Write ("select dep_airport from trip_details where trip_id = 'MK/"&rs("A1_pnr")&"' order by tatoo asc")
departing_air = rscrm2("dep_airport")
%>
<% while not rs.eof %>
<% set rscrm = con.execute("select * from trip_details where trip_id = 'MK/"&rs("A1_pnr")&"' order by tatoo asc")%>
<tr bgcolor='#ececec'bordercolor='#cccccc'>
<td align='left'>
<%=rs("leadname")%>
</td>
<td width='10%' align=center>
<%=rs("regular_trip")%>
</td>
<td align='center'>
<a href="mytrip_new.asp?pnrno=<%=rs("A1_pnr")%>"><%=r s("A1_pnr")%></a>
</td>
<td align='center'>
<%=rs("Destination_Arrival_Date")%>
</td>
<%
counter = 0
while not rscrm.eof
counter = counter+1

var_arr = rscrm("arr_airport")&"-"

arriving_air = arriving_air & var_arr

'abcd = var_arr+var_arr
'response.write "abcd = " & abcd & "<br>"
'response.write "departure airport = " & var & "<br>"
'response.write "arrival airport = " & var_arr & "<br>"
rscrm.movenext
wend
crm_iti = departing_air&"-"&arriving_air

%>
<td align="center">
<%=left(crm_iti,len(crm_iti)-1)%>
</td>
<td align="center">
<%=rs("date_of_booking")%>
</td>
</tr>
<%
rs.movenext
wend
%>
</table>
</form>
</body>

<%
set rscount=con.execute("select count(*) from trip_master where agencyid = '"&agencyid&"' and regular_trip <> 'Cancelled'")
count_rows = rscount("count(*)")
'response.write "Count Rows = " & count_rows & "<br>"
if count_rows > 100 then
%>
<div align="center">
<a href="javascript: history.back();">
 << Previous 50 </a>
&nbsp;&nbsp;
<a href="bookdates_101.asp">
Next 50 >></a>
</div>
<% else %>
<br>
<div align="center">
<a href="bookings.asp">
Back to Bookings
</a>
</div>
<% end if %>
</html>

what is happeneing now is the same itinerary keeps incrmenting itself, no joy as yet

















www.crmpicco.co.uk

You've only moved one of the recordsets, so you're still always getting the departure airport of the first record.

Also, you're not clearing out the arriving_air variable each time, that's why each trip is adding to the previous. Put arriving_air="" between the 'counter=0' and 'while not rscrm.eof' lines.