some [more] asp help required please

[RabbieBurns]

I am trying to display a list of students, their subjects, and some data for each subject. It kinda works, except it repeats the pupils name for each subject:



I want it to show the pupils name just once in the left column, then, in the one row, show each of the students subjects, and corresponding data

(so it would look something like this)




Here is my query from the database:

Query="SELECT pupils.PupilID, pupils.Surname, pupils.Forename, pupils.Form, subjects.subjectName, reports.attitud, pupils.attcomment"
Query=Query & " FROM (subjects RIGHT JOIN sets ON subjects.subjectID = sets.setsubject) RIGHT JOIN (pupils LEFT JOIN reports ON pupils.PupilID = reports.pupilID) ON sets.setid = reports.setID"
Query=Query & " WHERE form like '%" & request("form") &"%'"
Query=Query & " ORDER by surname"

and here is the table code

<TABLE border="1" cellspacing="1" style="border-collapse: collapse" width="50%">
<TR><font size="2">
<TD width="10%" align="center"><b>Pupil</b></TD>
  <TD width="5%"><b>Subjects</b></TD>
  <TD width="3%"><b>Attitudinals</b></TD>
  <TD width="25"><b>Attitudinal Comment</b></td>
</font></TR>
<%
Do While Not RSlist.EOF
%>
<TR>
<TD width="15%" valign="top">
<%=RSlist("Forename")%>&nbsp;<%=RSlist("Surname")%><br>
<%=RSlist("Form")%></TD>
<TD width="5%" valign="top">
 <% ' Needed is some code to iterate and display each subject  without showing the name again
 response.write(RSlist("subjectname") & "<br />")
 %>
</TD>
<TD width="3%" valign="top" align="center">
    <%   
	      ' write the attitudinal and a line break
        response.Write(RSlist("attitud") & "<br />")
    %>
  </TD>
 <TD width="25%" valign="top"><%=RSlist("attcomment")%>&nbsp;</TD>
</TR>
<%
RSlist.Movenext
Loop
%>
</TABLE>

I know I need to somehow do a count of how many subjects per student and then iterate through that, but I dont really know how to go about it.

Sorry for the long post, but if anyone can help me please it would be much appreciated. Thanks

[srochford]

I generally do this by tracking the name I last displayed. if the name I'm going to display next is the same then i don't print it, else I do print.

Something like this:

sOldName=""
Do While Not RSlist.EOF
%>
<TR>
<TD width="15%" valign="top">
<%
  sName=RSlist("Forename") &nbsp; & RSlist("Surname")
  if sOldName<>sName then 
    response.write sName & "<br/>" & rslist("form") 
    sOldName=sName
  else
    response.write "&nbsp;"
 end if
%></TD>
<TD width="5%" valign="top">
 <% ' Needed is some code to iterate and display each subject  without showing the name again
 response.write(RSlist("subjectname") & "<br />")
 %>
</TD>
<TD width="3%" valign="top" align="center">
    <%   
	      ' write the attitudinal and a line break
        response.Write(RSlist("attitud") & "<br />")
    %>
  </TD>
 <TD width="25%" valign="top"><%=RSlist("attcomment")%>&nbsp;</TD>
</TR>
<%
RSlist.Movenext
Loop

This isn't quite going to do what you want because you'll end up with blank cells for each student where the name was. It looks less cluttered but it might not be what you want.

I'm guessing that each pupil won't have the same number of subjects. You can find out how many they have with a SQL query:

Query="SELECT pupils.PupilID, count(subjects.subjectName) as numsubjects"
Query=Query & " FROM (subjects RIGHT JOIN sets ON subjects.subjectID = sets.setsubject) RIGHT JOIN (pupils LEFT JOIN reports ON pupils.PupilID = reports.pupilID) ON sets.setid = reports.setID"
Query=Query & " WHERE form like '%" & request("form") &"%'"
Query=Query & " group by pupils.pupilid ORDER by surname"

and you could put the result of that into a temp table which would then be linked to your original query to give the number of subjects as a field in the list of data you already had.

[RabbieBurns]

Thanks for the reply. Yep, the subject numbers will vary. I just tried out your suggestion, blank cells would be a lot better than now and be a good start, however I got:

Microsoft VBScript compilation (0x800A0401)
Expected end of statement
attsummaryfull.asp, line 43, column 30
sName=RSlist("Forename")   & RSlist("Surname")

[webman]

Microsoft VBScript compilation (0x800A0401)
Expected end of statement
attsummaryfull.asp, line 43, column 30

sName=RSlist("Forename")   & RSlist("Surname")

Should be

sName = RSlist("Forename") & " " & RSlist("Surname")

[RabbieBurns]

That looks a lot better. Thanks.

[RabbieBurns]

I found a bug if theres 2 kids with the same surname in the one form class:



any ideas?

[srochford]

I found a bug if theres 2 kids with the same surname in the one form class:



any ideas?

Change the order by so that it says

ORDER by surname, forename

If you have 2 John Smiths that still won't work so you need to include the unique ID -

ORDER by surname, forename, pupilID

[RabbieBurns]

Ah cheers thought it might have been something to do with the SQL.

[RabbieBurns]

Im after a bit more advice if possible... Im trying to tidy up some of my pages, and a couple of pages where I have 2 queries and data connections I am trying to combine them to one. So for example:

Query="SELECT PupilID, Surname, Forename, Form, attcomment, attcomment2, atttarget"
Query=Query&" FROM pupils"
Query=Query&" WHERE PupilID=" & request("pupilID")

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "S1"
Set RSlist = Server.CreateObject("ADODB.recordset")
RSlist.Open Query,DataConn,3

Query2="SELECT setID, attitud, pupilID FROM reports" 
Query2=Query2 & " WHERE pupilID = " & request("pupilID")
Query2=Query2 & " ORDER by setID"

Set DataConn2 = Server.CreateObject("ADODB.Connection")
DataConn2.Open "S1"
Set RSlist2 = Server.CreateObject("ADODB.recordset")
RSlist2.Open Query2,DataConn2,3

I have changed to the below, with just one data conn (RSlist)

Query="SELECT pupils.PupilID, pupils.Surname, pupils.Forename, pupils.attcomment, pupils.form, pupils.atttarget, pupils.attcomment2, reports.setID, reports.attitud"
Query=Query&" FROM pupils LEFT JOIN reports ON pupils.[PupilID] = reports.[pupilID]"
Query=Query&" WHERE pupils.[PupilID]=" & request("pupilID")

The problem is that halfway the page Ive got a loop which moves on to the next record before its finished using data from the current record, and Im not sure how to fix it.

...
<TABLE border="1" cellspacing="1" style="border-collapse: collapse" width="50%">
<TR>
<TD width="10%" align="center"><b>Set</b></TD>
<TD width="7%" align="center"><b>Attitudinal</b></TD>
</TR>
<%
' While there are still more records
Do While Not RSlist2.EOF
%>
<TR>
<TD width="10%" align="center">
<%=RSlist2("setID")%>&nbsp;</TD>
</TD>
<TD width="7%" align="center">
  <%   
' write the attitudinal
        response.Write(RSlist2("attitud"))
  %>
</TD>
</TR>
<%
' move on to next record
RSlist2.Movenext
Loop
%>
</TABLE>
</body>

IT WORKS TO HERE THEN FAILS

<!-- form is opened here  -->
<form method="POST" action="att3dupdate.asp">
	<input type="hidden" name="form" value="<%=RSlist("form")%>">
    <input type="hidden" name="pupilID" value="<%=request("pupilID")%>">
	
<h4><font color="#FF0000">As a result of these grades <%=RSlist("forename")%> : </font></h4>

<select name="attcomment">
		<option>is commended for a high standard of effort</option>
		<option>is commended for improved effort</option>
		<option>maintains a reasonable standard of effort</option>
		<option>will be put on Form Teacher's Report</option>
		<option>will be put on Deputy Headmaster's Report</option>
		<option>will be put on Headmaster's Report</option>
</select>

<!-- Display what has currently been saved -->	
<p>Currently saved as: <font color="#000000"><%=RSList("attcomment")%></font>
<hr>
<h4><font color="#FF0000">Target for the next five weeks is to:</font></h4>

<!-- create targets comment parameter -->

<select name="atttarget">
		<option>sustain or improve upon the above grades</option>
		<option>eliminate all C grades from the report</option>
		<option>upgrade 1 B grade to A</option>
		<option>upgrade 2 B grades to A's</option>
		<option>upgrade 3 B grades to A's</option>
		<option>upgrade 4 B grades to A's</option>
		<option>upgrade 5 B grades to A's</option>
</select>

<!-- Display what has currently been saved -->	
<p>Currently saved as: <font color="#000000"><%=RSList("atttarget")%></font>
<hr>
<!-- create the optional comment parameter -->
<h4><font color="#FF0000">This box is for any other optional comments:</font></h4>
<textarea rows="2" name="attcomment2" cols="67"><%=RSlist("attcomment2")%></textarea>
...

I tried expanding the loop to include the 2nd part of the page but that doesnt seem to work. Do I need to rethink the way the first table is drawn so that I can keep the data for the 2nd part of the page, before moving onto next record?

[RabbieBurns]

interestingly if i move the comments stuff before the loop for setid/attitud, it works fine. is it simply a case of redoing the loop better?