Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 8th, 2003, 03:59 PM
Authorized User
 
Join Date: Jun 2003
Location: Gulfport, Ms, USA.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hcweb
Default Counting ?!?

If anyone could help, I'd be greatly indebted.. Again :o) ...

I have a query that runs on a law enforcement website that queries based on charge type and year. The results will then display totals for each suboffense of that particular charge type. Everything displays fine except for one thing. I need to sum all totals in each field. However, some charges contain more suboffenses than others. One charge may contain 3 sub offenses where another charge may contain 8 sub offenses. I know this is confusing, but I have the current working for viewing at http://www.harrisoncountysheriff.com/statistics/

Here is the code that I currently have:

<%
  Dim objComm, objRS, strOff, strDate
  strOFF = Request.Form("offense")
  strDate = Request.Form("offyear")

  Set objComm = Server.CreateObject("ADODB.Command")
  objComm.ActiveConnection = strConnect
  objComm.Commandtext = "SELECT * FROM tbl_offenses WHERE offense = '" & strOFF & "' " & _
                        "AND offyear = '" & strDate & "' ORDER BY suboff ASC"
  objComm.CommandType = adCmdText

  Set objRS = objComm.Execute
  Set objComm = Nothing

  If objRS.EOF Then
    Response.Write "<table align=center width=450 border=0 cellspacing=0 cellpadding=0>" & _
                   "<tr><td class=text>&nbsp</td></tr>" & _
                   "<tr><td class=pagetext align=center><b>Statistics for " & strDate & " will be added soon<br>" & _
                   "Please check back later.</b></td></tr>" & _
                   "<tr><td class=pagetext>&nbsp</td></tr>" & _
                   "<tr><td class=pagetext align=center bgcolor=dddddd><a href='stats.asp'>Search Again</a> | " & _
                   "<a href='index.html'>Statistics Main</a></td></tr>" & _
                   "<tr><td class=text>&nbsp</td></tr>" & _
                   "</table>"
  Else
    Response.Write "<table align=center width=600 border=0 cellspacing=0 cellspadding=0><tr>" & _
                   "<tr><td class=pagetext>&nbsp</td></tr>" & _
                   "<td align=left bgcolor=ffcc00 colspan=6 class=pagetext>" & _
                   "<b>" & strDate & "</b> statistics for <b>" & objRS("offense") & "</b></td></tr>" & _
                   "<tr><td bgcolor=333333 colspan=6></td></tr>" & _
                   "<tr><td class=pagetext align=left colspan=5>Below are the sub-offenses and statistics associated with " & _
                   objRS("offense") & ".</td></tr>" & _
                   "<tr><td class=pagetext>&nbsp</td></tr>" & _
                   "<tr><td align=left bgcolor=cccccc width=255 class=statheader></td>" & _
                   "<td class=statheader align=center width=70 bgcolor=cccccc>Reported</td>" & _
                   "<td align=center class=statheader width=70 bgcolor=cccccc>Unfounded</td>" & _
                   "<td class=statheader align=center width=70 bgcolor=cccccc>Actual</td>" & _
                   "<td align=center class=statheader width=70 bgcolor=cccccc>Arrested</td>" & _
                   "<td class=statheader align=center width=70 bgcolor=cccccc>Under 18</td></tr>"

  Do While Not objRS.EOF
  Dim strReport, strUnfound, strActual, strArrest, strUnder18, strSuboff
  strReport = objRS("reported")
  strUnfound = objRS("unfounded")
  strActual = objRS("actual")
  strArrest = objRS("arrestable")
  strUnder18 = objRS("under18")
  strSubOff = objRS("suboff")

    Response.Write "<tr><td colspan=6 class=pagetext><img src='../menu/shim.gif' height=2 width=10></td></tr>" & _
                   "<tr><td align=left width=255 class=statvalue bgcolor=cccccc><b>" & strSubOff & "</b></td>" & _
                   "<td class=statvalue align=center width=70>" & strReport & "</td>" & _
                   "<td align=center class=statvalue width=70>" & strUnfound & "</td>" & _
                   "<td class=statvalue align=center width=70>" & strActual & "</td>" & _
                   "<td class=statvalue align=center width=70>" & strArrest & "</td>" & _
                   "<td align=center class=statvalue width=70>" & strUnder18 & "</td></tr>" & _
                   "<tr><td colspan=6 class=pagetext bgcolor=cccccc><img src='../menu/shim.gif'" & _
                   " height=2 width=10></td></tr>" & _
                   "<tr><td class=pagetext colspan=6><img src='../menu/shim.gif' height=2 width=10></td></tr>" & _
    objRS.MoveNext
    Loop
    Response.Write "<tr><td class=pagetext colspan=6 bgcolor=333333></td></tr>" & _
                   "<tr><td class=pagetext align=center bgcolor=dddddd colspan=6><a href='stats.asp'>" & _
                   "Search Again</a> | <a href='index.html'>Statistics Main</a></td></tr>" & _
                   "</table>"
  End If


  objRS.Close
  Set objRS = Nothing
%>

I've thought of counting records but I'm not quite sure as to how to employ it correctly. I'll be happy to try and clear up any confusion as need be in a reply.

Thanks!
Chris
 
Old December 8th, 2003, 04:14 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

This seems to be a simple matter of maintaining a counter for all the columns you wish to total up.

Before your loop:
Dim nReportTotal : nReportTotal = 0
Dim nUnfoundTotal : nUnfoundTotal = 0
Dim nActualTotal : nActualTotal = 0
Dim nArrestTotal : nArrestTotal = 0
Dim nUnder18Total : nUnder18Total = 0

Inside the loop:
nReportTotal = nReportTotal + CInt(strReport)
nUnfoundTotal = nUnfoundTotal + CInt(strUnfound)
nActualTotal = nActualTotal + CInt(strActual)
nArrestTotal = nArrestTotal + CInt(strArrest)
nUnder18Total = nUnder18Total + CInt(strUnder18)

Now you can show your column totals.

Peter
------------------------------------------------------
Work smarter, not harder.
 
Old December 8th, 2003, 05:08 PM
Authorized User
 
Join Date: Jun 2003
Location: Gulfport, Ms, USA.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hcweb
Default

Thanks Peter, it worked like a charm! I'd rename one of my kids after you, but since they are all girls, they may not appreciate being called Peter!
:o)

Chris




Similar Threads
Thread Thread Starter Forum Replies Last Post
Error in Counting Jonas Access VBA 2 August 15th, 2006 12:21 AM
record counting keyvanjan Classic ASP Databases 0 June 30th, 2005 01:20 AM
charachter counting bjackman Access 12 March 11th, 2005 04:51 AM
Click Counting mani_he Pro PHP 5 December 25th, 2004 01:33 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.