Contents of bcb2.asp:
<html> <body bgcolor="#cccccc"> <Table border="1"> <tr><td valign='middle' bgcolor='#999999'><img src="captsm.gif"></td> <td valign='middle' bgcolor='#999999'><center><font color="#ffffff"><font face="Comic Sans MS"> <br> <b>A Simple ASP Example<br>Double Level Control Break</b> <br> </td></tr></table> </center> <p> <font face="Comic Sans MS"><b> <p> <p>Begin execution...<p> <% sub writegrandtotal (wlabel,wcount) response.write "<tr><td bgcolor='#ffffff' align='right'>"+wlabel+" = "+cstr(wcount)+"</td><td></td><td></td><td></td></tr>" end sub sub writelocationtotal (wlabel,wcount) response.write "<tr><td colspan='2' bgcolor='#ccccff' align='right'>"+wlabel+" = "+ cstr(wcount)+"</td>" response.write "<td></td><td></td></tr>" end sub sub writenewlocationheader(newloc) response.write "<tr><td></td><td bgcolor='#ccccff' align='center'><b>"+newloc+"</td>" response.write "<td></td><td></td></tr>" end sub sub writelastnametotal (wlabel,wcount) response.write "<tr><td colspan='3' bgcolor='#ffcccc' align='right'><b>"+wlabel+" = "+cstr(wcount)+"</td>" response.write "<td></td></tr>" end sub sub writenewlastnameheader(newloc) response.write "<tr><td></td><td></td><td bgcolor='#ffcccc' align='center'><b>"+newloc+"</td>" response.write "<td></td></tr>" end sub ' '***** top of main ' set rs=Server.CreateObject("ADODB.Recordset") ' '***** Note sort in the order the breaks appear '***** First on the outside, then inside ' SQLString="SELECT * FROM badge order by bloc ASC, blastname ASC, bid ASC" response.write "<P>Opening badge rs with SQLstring=<br>--->"+cstr(SQLString)+"<------ " response.write "<P><b>NOTE: Primary sort is location, secondary sort is lastname, third level sort is badge id</b>" rs.open SQLString,"DSN=gl001;UID=gl001;PWD=11111;" response.write "<P>Recordset opened OK<p>" 'if NOT rs.EOF then ' '*** records found -- work to do ' response.write "<table border='1'>" response.write "<tr><td align='center'><b>Badge id</td><td align='center' bgcolor='#ccccff'><b>Location</td><td align='center' bgcolor='#ffcccc'><b>Last Name</td><td align='center'><b>First Name</td></tr>" ' '***** set all level counters to zero ' c=0 cloc=0 st1_control_check=0 st2_control_check=0 cname=0 ' '***** set all lastxxx to the current (first value from the file) ' lastloc=rs("bloc") lastname=rs("blastname") call writenewlocationheader(rs("bloc")) call writenewlastnameheader(rs("blastname")) while NOT rs.EOF ' '***** Start control breaks '***** Top level break first (i.e, location) ' if lastloc <> rs("bloc") then st1_control_check=st1_control_check+cloc st2_control_check=st2_control_check+cname call writelastnametotal ("Last Name Count for "+cstr(lastname),cname) call writelocationtotal ("<b>Location Count for "+cstr(lastloc)+"</b>",cloc) call writenewlocationheader(rs("bloc")) call writenewlastnameheader (rs("blastname")) cloc=0 cname=0 else ' '***** now the inside level (note we use an embedded if in the order of the levels ' if lastname <> rs("blastname") and cname <> 0 then st2_control_check=st2_control_check+cname call writelastnametotal ("Last Name Count for "+cstr(lastname),cname) call writenewlastnameheader (rs("blastname")) cname=0 end if end if ' '**** write the detail ' response.write "<tr><td>"+cstr(rs("bid"))+"</td>" response.write "<td align='center'>"+cstr(rs("bloc"))+"</td>" response.write "<td>"+cstr(rs("blastname"))+"</td>" response.write "<td>"+cstr(rs("bfirstname"))+"</td></tr>" ' '**** add this rec to all counters ' c=c+1 cloc=cloc+1 cname=cname+1 ' '**** set all lastxxx too this rec's values ' lastloc=rs("bloc") lastname=rs("blastname") rs.movenext wend call writelastnametotal ("Last Name Count for "+cstr(lastname),cname) st2_control_check=st2_control_check+cname call writelocationtotal ("<b>Location Count for "+cstr(lastloc)+"</b>",cloc) st1_control_check=st1_control_check+cloc call writegrandtotal ("<b>GRAND TOTAL</b>",c) response.write "</table><b>" response.write "<p>Records read=" response.write cstr(c) if c=st2_control_check then response.write "<p>Control total check OK -- Number of records read ("+cstr(c) response.write ") = Sum of second level subtotals ("+cstr(st2_control_check)+")" else response.write "<p>Control total check FAILS -- Number of records read ("+cstr(c) response.write ") NOT = Sum of second level subtotals ("+cstr(st2_control_check)+")" end if if c=st1_control_check then response.write "<br>Control total check OK -- Number of records read ("+cstr(c) response.write ") = Sum of first level subtotals ("+cstr(st1_control_check)+")" else response.write "<br>Control total check FAILS -- Number of records read ("+cstr(c) response.write ") NOT = Sum of first level subtotals ("+cstr(st1_control_check)+")" end if 'else ' response.write "<p>Table is empty -- No records returned" 'end if response.write "<p>Terminating Normally -- "+cstr(now) rs.close set rs=nothing %> <p> <a href="bdoc.asp?fl=2"> Click here for the program listing </a> (bcb2.asp)<br> </body> </html>
***** end of file *** printed: 175 lines on: 11/23/2009 6:01:51 AM
