How to exit code and close recordset

• • Reply

  14 years ago

  by dsmith76

  Dave Smith

  , United States

  Joined 14 years ago

  I am doing a report which subtotals the number of points accumulated for golfers in events they have played during the summer. I am having trouble at the end of the recordset. When I get  to the end of the recordset I have some code that says to print the last golfer and his results, but I don't want it to loop again because it creates an error. Not every player plays in every event, which complicates the code. I have to show the name of every golfer on the report that earned Wilwinpts in at least one event. Here is my coding. It prints everything out including the last record, but I get an error message you can see at this link.
  http://winnetkagolf.com/reporter/playerreportswilwinsummary1a.asp
  
  <% set oRS=server.createobject("ADODB.recordset")  %>
         <% SQL="SELECT wilwinpt,fname, Lname from scores,maillist where scores.mailid=maillist.mailid and wilwinpt > 0 order by lname, fname"
     
      ORS.open SQL, OC, adOpenStatic, adLockOptimistic, adCmdText
      Do while not ors.eof
       %>
  <tr><td>
  <%     Sum=0
          fname=ors("fname")
          lname=ors("lname")
          Do while (ors("lname") = lname and ors("fname")=fname)
              Sum=Sum+ ors("wilwinpt")
              ors.movenext
                  If ors.eof then
                      response.write sum
                      End if
          loop
          ors.moveprevious
          response.write sum
          %>
  </td></tr>
      <%
      ors.movenext
      loop
      %>
     
             </table>
      <!--End of BELMONT Totals table-->
      </td></tr>
  
  </table>
  <%
      ors.close
      set ors=nothing
      oc.close
      set oc=nothing %>
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by chrisxu

  chrisxu

  , China

  Joined 14 years ago

         Do while (ors("l_name") = l_name and ors("f_name")=f_name)
              Sum=Sum+ ors("wilwinpt")
              ors.movenext
                  If ors.eof then
                      response.write sum
  
  
  
  

                       exit do  'exit do while
                      End if
          loop
          ors.moveprevious
          response.write sum
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by Serval

  PIERRE

  , France

  Joined 14 years ago

  hello.

  i think that you just have to make another query to make the sum : one to scan the players and another one to calculate

  It will not take a longer time to execute for your queries are very easy.

  And you will not have to do the moveprevious ...

   

   

  Report abuse
• • Reply

  14 years ago

  by Serval

  PIERRE

  , France

  Joined 14 years ago

  hello again

  had you try this :

  1) remember that you are in the first do while loop sequence

  2) the user_id is the id in your scores table

  set rs2 = server. create..... recordset")

  rs2.open "select SUM(wilwinpt) as SCORE from   scores where user_id = xxxxx " , oc, 3, 1

  response.write rs2("SCORE")

  regards.

  Malko

   

   

  Report abuse