Solved

Trying to nest two loops in asp 3.0

Posted on 2014-10-31
11
131 Views
Last Modified: 2014-10-31
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?
0
Comment
Question by:princeservice
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
11 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 40415773
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?
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40415780
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?
0
 
LVL 33

Accepted Solution

by:
Big Monty earned 250 total points
ID: 40415793
didn't see this originally, but change

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

to

<% mSQL2 = "SELECT * FROM issue_comments WHERE id = '" & rs1("id") & "'"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 53

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 250 total points
ID: 40415794
		  <% mSQL2 = "SELECT * FROM issue_comments WHERE id = '" & Response.Write rs1("id") & "'"

Open in new window

should be

		  <% mSQL2 = "SELECT * FROM issue_comments WHERE id = '"&  rs1("id") & "'"

Open in new window

Beyond that, just create a joined select and that way you will not throw an error. Otherwise, make sure to account for a null value in the current  rs1("id")
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40415820
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

0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40416215
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...
0
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 40416334
Or this should be the answer http:Q_28548400.html#a40415794
0
 

Author Comment

by:princeservice
ID: 40416342
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.
0
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 40416353
We answered at virtually the same time.  You can split points or just give to him.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40416366
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question