Solved

Trying to nest two loops in asp 3.0

Posted on 2014-10-31
11
127 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Replacing smart quotes from word 7 67
Classic ASP error- Object required: 'objFolder' 6 36
Determining if Request.Form is empty 1 45
EOF BOF error classic asp 8 43
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/…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

21 Experts available now in Live!

Get 1:1 Help Now