Solved

Trying to nest two loops in asp 3.0

Posted on 2014-10-31
11
125 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
  • 6
  • 3
11 Comments
 
LVL 32

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 32

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 32

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
 
LVL 52

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 32

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 32

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 52

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 52

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 32

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now