I have a loop that should cycle through my RS. It does but keeps showing the same data. Any help would be great.
as you can see the sql statement and the fields that they see are dynamically generated. Everything works fine except I seem to be printing the first record over and over?
Code:
dim ha_name,field_1,field_2,field_3,field_4,field_5,field_6,field_7,sort,fieldName1,fieldName2,fieldName3
dim fieldName4,fieldName5,fieldName6,fieldName7,fieldName8
field_1 = RS("field_1")
field_2 = RS("field_2")
field_3 = RS("field_3")
field_4 = RS("field_4")
field_5 = RS("field_5")
field_6 = RS("field_6")
field_7 = RS("field_7")
sort = RS("sort")
ha_name = RS("ha_name")
dim sqlStatement,x
x = 1
do while x < 8
if RS("field_"&x) <> "" then
sqlStatement = sqlStatement & RS("field_"&x)&","
end if
x = x + 1
loop
sqlStatement = mid(sqlStatement,1,len(sqlStatement) - 1)'taking off last comma
sql = "Select "&sqlStatement
sql = sql & " From properties as pp, landlord as ll "
sql = sql & "where pp.users_id = ll.users_id "
sql = sql & " and pp.HA_id = '"&Session("users_id")&"' "
sql = sql & " order by pp.date_listed"
if sort <> "" then
sql = sql & ","&sort
end if
'response.write(sql)
set RS = conn.Execute(sql)
'finding and setting table fields
call setFields(field_1,fieldName1)
call setFields(field_2,fieldName2)
call setFields(field_3,fieldName3)
call setFields(field_4,fieldName4)
call setFields(field_5,fieldName5)
call setFields(field_6,fieldName6)
call setFields(field_7,fieldName7)
call setFields(field_8,fieldName8)
call setFields(sort,0)
sub setFields(field,num)
select case field
case "pp.address1,pp.address2"
field = RS("address1")&RS("address2")
num = "Address"
case "pp.city"
Field = RS("city")
num = "City"
case "pp.subdivision"
field = RS("subdivision")
num = "Subdivision"
case "pp.number_rooms"
field = RS("number_rooms")
num = "Bedrooms"
case "pp.number_baths"
field = RS("number_baths")
num = "Bathrooms"
case "pp.rent_amount"
field = RS("rent_amount")
num = "Rent"
case "ll.first_name,ll.last_name"
field = RS("first_name")&" "&RS("last_name")
num = "Landlord"
case "ll.phone"
field = RS("phone")
num = "Contact Number"
case "pp.date_listed"
field = RS("date_listed")
num = "Date Listed"
end select
end sub
then the loop is here
<%do while not RS.eof%>
<tr align=center>
<td><%=field_1%></td>
<td><%=field_2%></td>
<td><%=field_3%></td>
<td><%=field_4%></td>
<td><%=field_5%></td>
<td><%=field_6%></td>
<td><%=field_7%></td>
<td><%=field_8%></td>
</tr>
<%RS.MoveNext
loop%>