loop within a loop

[ziggs]

I'm a newbie. So, I can write an asp that loops so that I can get
multiple returns. However, I can't figure out how to write an asp
that has a many to one relationship in several different areas. Is
there a good example somewhere that shows this in action? Does anyone
have an asp they can forward me that shows it? TIA

For example, I wrote an asp (it's at work right now or I'd attach it)
that gives four events at one address. So, I can get all four events
with my loop. Now, within each address there is a list of people
associated to the address. Maybe one person, maybe three. Although I
can get the four events at one address, I can't get all the persons to
pop up. It just shows one person and no more. Hence my problem.

 

[bellmd]

This does not sound that tricky, but to help we'll need a few more details.

 

[srikanthasp]

I really could not interpret your problem? Try to be specific.

regards
srikanth

 

[ziggs]

Okay, here it goes. The asp will give this:

123 test st 4-1-82
123 test st 3-20-89
123 test st 3-30-99

So, my loop produces above. Now, within each address, there might be one to four people associated to the entry on that date. Thus, the loop within the loop that I don't understand.

The proper results that I want is:

123 test st 4-1-82
Jack
Jeff
123 test st 3-20-89
Mary
123 test st 3-30-99
Jennifer
Daniel
Joseph
Eugene

Thus, a loop (the names) within a loop (the number of times the address is entered into database)

 

[link9]

I'll assume that you have a join where the key value (the first line of output) will repeat itself in the recordset so that you can check to see if that value has changed.

while not rs.eof
  outputTheKeyValue
    while theKeyValueIsTheSame
      'Here is where you would output the other values
      rs.movenext
    wend
  rs.movenext
wend

Without seeing a code example from your page, that's about the best I can do. If you post some code, we could probably help you more.

Paul Prewett

 

[ziggs]

Thanks for the offer! Here's some of the code. I took out some so that it wasn't as confusing (It takes a lot of data from other areas that are not a problem).

Here's the Select statement:

SQL1 = "SELECT * From dbo_address"
SQL1 = SQL1 & " WHERE dbo_address.st_no = '" & Request.QueryString ("digits&quot &"' and"
SQL1 = SQL1 & " dbo_address.st_name = '" & Request.QueryString ("street&quot &"'"
set rs1 = my_conn.Execute(SQL1)

SQL5 = "SELECT dbo_person.lic_no, dbo_person.last_name"
SQL5 = SQL5 & " FROM dbo_person"
SQL5 = SQL5 & " WHERE dbo_person.lic_no = '" & rs1("lic_no&quot & "'"
set rs5 = my_conn.Execute(SQL5)

_____________________

Here's the code:

<TR>
<TD bgcolor=<%=FieldColor%> align=center colspan=1><B>ADDRESS</B></TD>
<TD bgcolor=<%=FieldColor%> align=center colspan=1><B>CITY</B></TD>
<TD bgcolor=<%=FieldColor%> align=center colspan=1><B>STATE</B></TD>
<TD bgcolor=<%=FieldColor%> align=center colspan=1><B>ZIP</B></TD>

</TR>

<%if rs1.eof then%>
<TR>
<TD bgcolor=<%=InfoField%> align=center colspan=10><B>No entry found for address given</B></TD>
</TR>

<%
else
do until rs1.eof
%>
<TR>

<TD bgcolor=<%=InfoField%> colspan=1>&nbsp;
<% FinalAdd = rs1(&quot;st_no&quot & &quot; &quot;& rs1(&quot;st_prefix&quot & &quot; &quot;& rs1(&quot;st_name&quot & &quot; &quot;& rs1(&quot;st_type&quot & &quot; &quot;& rs1(&quot;unit_no&quot
Response.Write FinalAdd
%>
<TD bgcolor=<%=InfoField%> align=center><%=rs1(&quot;city&quot%>&nbsp;</TD>
<TD bgcolor=<%=InfoField%> align=center><%=rs1(&quot;State&quot%>&nbsp;</TD>
<TD bgcolor=<%=InfoField%> align=center><%=rs1(&quot;zip&quot%>&nbsp;</TD>
</TD>
</TR>

<TR>
<TD bgcolor=<%=InfoField%> align=center><%=rs1(&quot;lic_no&quot%>&nbsp;</TD>
<TD bgcolor=<%=InfoField%> align=center><%=rs1(&quot;add_no&quot%>&nbsp;</TD>
</TR>

<TR>
<TD bgcolor=<%=InfoField%> align=center colspan=10><B>No Address found</B></TD>
</TR>


<%if not rs5.eof then%>
<TR>
<TD bgcolor=<%=FieldColor%> align=center colspan=5><B>Person</B></TD>
</TR>

<%do until rs5.eof%>
<TR>
<TD bgcolor=<%=InfoField%> align=center><%=rs5(&quot;last_name&quot%>&nbsp;</TD>
</TR>
<%
rs5.movenext
loop
else
%>
<TR>
<TD bgcolor=<%=InfoField%> align=center colspan=10><B>No Person found</B></TD>
</TR>
<%
end if
%>

<TR>
<TD colspan=10 bgcolor=#7b68ee align=center>
<FONT color=#FFFFFF><B>Next</B></FONT>
</TD>
</TR>
<%
rs1.movenext
loop
end if
%>

 

[link9]

Looks like you are forgetting to move your rs5 back to the first record before you loop around for the rs1 loop.

right before the rs1.movenext call, add this:

if not (rs5.eof and rs5.bof) then
  rs5.movefirst
end if

I hope that clears it up
Paul Prewett

 

[ziggs]

I must be doing something else wrong, that did not work. I'm still showing only one record for persons

 

[link9]

Ok, I think the entire code is flawed... It looks like you are only selecting the records for rs5 that correspond to whatever the first record in rs1 is... That would cause one problem.

Then, even if you did have all the records in rs5, you aren't checking to see if the link between the two recordsets match in order to do the output. Something like

 if rs5("lic_no") = rs1("lic_no"0 then doOutput end if

See what I mean? You aren't looking for any match between the two. Just outputing the values carte blanche (at least that's what it looks like).

That being said, let me suggest that you make those two recordsets into one recordset through a simple SQL JOIN statement. That way, the basic structure of your recordset would resemble something like this: (I'm naming the two columns &quot;date&quot; and &quot;name&quot; for simplicity)

date       name

4-1-82     Jack
4-1-82     Jeff
3-20-89    Mary
3-30-99    Jennifer
3-30-99    Daniel
3-30-99    Joseph
3-30-99    Eugene

Note how the date-looking value that is similar between the records repeats itself. This type of setup would just make it easier on you (or at least to me it would).

Then, the code to loop through that rs would look like:

dim keyValue

while not rs.eof
  call doOutputDate(rs(&quot;date&quot;))   '<-----more or less
  keyValue = rs(&quot;date&quot;)
  while (rs(&quot;date&quot;) = keyValue) and not rs.eof
    call doOutputName(rs(&quot;name&quot;))
    rs.movenext
  wend
wend

I hope that makes some sense to you. I was just still having a hard time following what you were doing up there.

I hope you get it working
Paul Prewett