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:

 

http://www.peninsulafm.com/atts.JPG

 

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)

 

 

http://www.peninsulafm.com/atts2.JPG

 

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

 

</pre><table border="1" cellspacing="1" style="border-collapse: collapse" width="50%">

Pupil
 Subjects
 Attitudinals
 Attitudinal Comment

<%
Do While Not RSlist.EOF
%>


<%=RSlist("Forename")%> <%=RSlist("Surname")%>

<%=RSlist("Form")%>

<% ' Needed is some code to iterate and display each subject  without showing the name again
response.write(RSlist("subjectname") & "
")
%>


   <%   
      ' write the attitudinal and a line break
       response.Write(RSlist("attitud") & "
")
   %>
 
<%=RSlist("attcomment")%> 

<%
RSlist.Movenext
Loop
%>
</

 

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

Edited September 17, 2008 by RabbieBurns

[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
%>


<%
 sName=RSlist("Forename")   & RSlist("Surname")
 if sOldName<>sName then 
   response.write sName & "
" & rslist("form") 
   sOldName=sName
 else
   response.write " "
end if
%>

<% ' Needed is some code to iterate and display each subject  without showing the name again
response.write(RSlist("subjectname") & "
")
%>


   <%   
      ' write the attitudinal and a line break
       response.Write(RSlist("attitud") & "
")
   %>
 
<%=RSlist("attcomment")%> 

<%
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:

 

http://www.peninsulafm.com/att3.JPG

 

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.

 

...
</pre><table border="1" cellspacing="1" style="border-collapse: collapse" width="50%">

Set
Attitudinal

<%
' While there are still more records
Do While Not RSlist2.EOF
%>


<%=RSlist2("setID")%> 


 <%   
' write the attitudinal
       response.Write(RSlist2("attitud"))
 %>


<%
' move on to next record
RSlist2.Movenext
Loop
%>
</table><br><br><br><p>IT WORKS TO HERE THEN FAILS


</p><form method="POST" action="att3dupdate.asp">
">
   ">

As a result of these grades <%=RSlist("forename")%> : 


	is commended for a high standard of effort
	is commended for improved effort
	maintains a reasonable standard of effort
	will be put on Form Teacher's Report
	will be put on Deputy Headmaster's Report
	will be put on Headmaster's Report




Currently saved as: <%=RSList("attcomment")%>

Target for the next five weeks is to:




	sustain or improve upon the above grades
	eliminate all C grades from the report
	upgrade 1 B grade to A
	upgrade 2 B grades to A's
	upgrade 3 B grades to A's
	upgrade 4 B grades to A's
	upgrade 5 B grades to A's




Currently saved as: <%=RSList("atttarget")%>


This box is for any other optional comments:
<%=RSlist("attcomment2")%>
...
<

 

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?