Solved

Trying to nest two loops in asp 3.0

Posted on 2014-10-31
11
130 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
Independent Software Vendors: 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Weighted Randomizing 6 38
Html CheckBox obtain Its Value 5 43
PHP encrypted string and passing to a ASP Page 12 87
How to post data to an API using ASP Classic 3 50
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

732 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