asked on

Trying to nest two loops in asp 3.0

I'm not really sure if I ama even thinking this through right but this is the only way I can logically see doing this.

I have a two tables with a one-to-many relationship. One table has a record per "issue" and the other table has comments that relate to a specific issue. Therefor you can have many comments for an individual issue. I want to display this information in a table. Each row of the table will have an issue with the last column listing all the comments for that issue.

Here is the code I have so far:

<% mSQL1 = "SELECT * FROM issues WHERE status='open'"
Set rs1 = connectstr.Execute(mSQL1)%>

<table border="1" cellspacing="0" cellpadding="2">
        <tr>
          <td><b>Issue Date</b></td>
          <td><b>Assigned</b></td>
          <td><b>Customer</b></td>
          <td><b>Job Number</b></td>
          <td><b>Due Date</b></td>
          <td><b>Issue</b></td>
          <td><b>Comments</b></td>
          </tr>
<%Do until rs1.EOF %>
        <tr>
          <td width="100"><%Response.Write rs1("idate")%></td>
          <td width="100"><%Response.Write rs1("assigned")%></td>
          <td width="100"><%Response.Write rs1("customer")%></td>
          <td width="100"><%Response.Write rs1("jobno")%></td>
          <td width="100"><%Response.Write rs1("duedate")%></td>
          <td width="300"><%Response.Write rs1("issue")%></td>
          <td width="300"><p>
		  <% mSQL2 = "SELECT * FROM issue_comments WHERE id = '" & Response.Write rs1("id") & "'"
					Set rs2 = connectstr.Execute(mSQL2)%>
          			<%Do until rs2.EOF %>
          			<%Response.Write rs2("commentor")%> - <%Response.Write rs2("cdate")%> - <%Response.Write rs2("comment")%></br>
          			<%rs2.MoveNext()
					Loop%>
          </p></td>
       </tr>
<%rs1.MoveNext()
Loop%>
</table>

Open in new window

This is the error I get:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/portal/issues2.asp, line 39

mSQL2 = "SELECT * FROM issue_comments WHERE id = '" & Response.Write rs1("id") & "'"
---------------------------------------------------------------------^

The arrow point directly under the rs1.

Am I approaching this all wrong?

Big Monty

your error is indicating most likely that there is an invalid value for rs1("id") for that particular record. if you do a Response.Write of that sql statement, what do you get?

Big Monty

as for your approach, it'll work, as long as there aren't too many records you shouldn't notice any performance issues. if you have 100's and 100's of issues, you may want to look into paging, but for now, let's just get this resolved...

what data type is your ID field?

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Fell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Big Monty

Otherwise, make sure to account for a null value in the current rs1("id")

you do not need to do this since your id value is already in quotes. if there is no ID value, it will still process with no error, as your sql where clause will read

where id = ''

the only thing I would add to your code is a check for any comments:

<% mSQL1 = "SELECT * FROM issues WHERE status='open'"
Set rs1 = connectstr.Execute(mSQL1)%>

<table border="1" cellspacing="0" cellpadding="2">
        <tr>
          <td><b>Issue Date</b></td>
          <td><b>Assigned</b></td>
          <td><b>Customer</b></td>
          <td><b>Job Number</b></td>
          <td><b>Due Date</b></td>
          <td><b>Issue</b></td>
          <td><b>Comments</b></td>
          </tr>
<%Do until rs1.EOF %>
        <tr>
          <td width="100"><%Response.Write rs1("idate")%></td>
          <td width="100"><%Response.Write rs1("assigned")%></td>
          <td width="100"><%Response.Write rs1("customer")%></td>
          <td width="100"><%Response.Write rs1("jobno")%></td>
          <td width="100"><%Response.Write rs1("duedate")%></td>
          <td width="300"><%Response.Write rs1("issue")%></td>
          <td width="300"><p>
		  <% mSQL2 = "SELECT * FROM issue_comments WHERE id = '" & rs1("id") & "'"
			Set rs2 = connectstr.Execute(mSQL2)
                        if not rs2.BOF and not rs2.EOF then    '-- check to ensure there are comment records
          			Do until rs2.EOF 
          			   Response.Write rs2("commentor")%> - <%Response.Write rs2("cdate")%> - <%Response.Write rs2("comment")%></br>
          			<%rs2.MoveNext()
					Loop
                         end if%>
          </p></td>
       </tr>
<%rs1.MoveNext()
Loop%>
</table>

Open in new window

Big Monty

I'm not sure if it matters, but I believe the answer should have been this one, as you cannot concatenate the Response.Write statement to the rest of your string...

Scott Fell

Or this should be the answer http:Q_28548400.html#a40415794

Robert Francis

ASKER

Actually Big Monty said the same thing right before you. Yes I chose the wrong Big Monty answer for the solution but figured it really didn't matter which one I chose.

Scott Fell

We answered at virtually the same time. You can split points or just give to him.

Big Monty

your answer was a repeat of my answer, plus an incorrect suggestion to check for a null value in the recordset when it wasn't needed, plus a vague suggestion to use a join in the sql with no follow up.

i don't really see where you offered anything useful that wasn't already stated