+ Post New Thread
Results 1 to 10 of 10
Web Development Thread, some [more] asp help required please in Coding and Web Development; I am trying to display a list of students, their subjects, and some data for each subject. It kinda works, ...
  1. #1

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,532
    Thank Post
    1,341
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    200

    some [more] asp help required please

    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:

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

    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
    Last edited by RabbieBurns; 17th September 2008 at 04:39 AM.

  2. #2

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,159
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    125
    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:

    Code:
    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:
    Code:
    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.

  3. Thanks to srochford from:

    RabbieBurns (12th September 2008)

  4. #3

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,532
    Thank Post
    1,341
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    200
    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:

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

  5. #4

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,420
    Thank Post
    644
    Thanked 967 Times in 667 Posts
    Blog Entries
    2
    Rep Power
    328
    Quote Originally Posted by RabbieBurns View Post
    Code:
    Microsoft VBScript compilation (0x800A0401)
    Expected end of statement
    attsummaryfull.asp, line 43, column 30
    
    sName=RSlist("Forename") &nbsp; & RSlist("Surname")
    Should be

    Code:
    sName = RSlist("Forename") & "&nbsp;" & RSlist("Surname")

  6. Thanks to webman from:

    RabbieBurns (12th September 2008)

  7. #5

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,532
    Thank Post
    1,341
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    200
    That looks a lot better. Thanks.

  8. #6

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,532
    Thank Post
    1,341
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    200
    I found a bug if theres 2 kids with the same surname in the one form class:



    any ideas?

  9. #7

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,159
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    125
    Quote Originally Posted by RabbieBurns View Post
    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

    Code:
    ORDER by surname, forename
    If you have 2 John Smiths that still won't work so you need to include the unique ID -
    Code:
    ORDER by surname, forename, pupilID

  10. Thanks to srochford from:

    RabbieBurns (12th September 2008)

  11. #8

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,532
    Thank Post
    1,341
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    200
    Ah cheers thought it might have been something to do with the SQL.

  12. #9

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,532
    Thank Post
    1,341
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    200

    More help req'd please :)

    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:

    Code:
    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)
    Code:
    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.

    Code:
    ...
    <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?

  13. #10

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,532
    Thank Post
    1,341
    Thanked 470 Times in 307 Posts
    Blog Entries
    6
    Rep Power
    200
    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?



SHARE:
+ Post New Thread

Similar Threads

  1. ASP Host
    By Hightower in forum Web Development
    Replies: 14
    Last Post: 30th June 2008, 09:09 PM
  2. ASP Problem
    By gibbo_ap in forum Web Development
    Replies: 0
    Last Post: 17th April 2008, 01:32 PM
  3. ASP help
    By kerrymoralee9280 in forum Web Development
    Replies: 12
    Last Post: 3rd October 2007, 09:11 AM
  4. ASP Guestbook
    By AnnDroyd in forum Web Development
    Replies: 3
    Last Post: 26th September 2007, 11:46 AM
  5. using IMAP Authentication in ASP
    By wesleyw in forum Coding
    Replies: 1
    Last Post: 17th October 2006, 11:30 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •