counting DB entry values (not rows)

Discussion in 'ASP General' started by Lord Merlin, May 14, 2004.

  1. Lord Merlin

    Lord Merlin Guest

    How do I add up the values of the data from all of the rows returned? i.e -
    I have a DB, with 148 rows of client info, and I want to addup all the
    scores. Each entry's score differes. Here is the code I have sofar:

    SQL = "select * from company_scores order by company_name"
    Set objRS = Server.CreateObject("ADODB.Recordset")

    objRS.CursorLocation = adUseServer
    objRS.CursorType = adOpenStatic
    objRS.ActiveConnection = objConn
    objRS.open SQL, objConn

    i=0
    icount = 0
    Do until objRS.EOF


    company_name = objRS("company_name")


    'currentday = day(objRS("sDate"))
    'currentmonth = month(objRS("sDate"))
    'currentyear = year(objRS("sDate"))

    'Select Case currentmonth
    'Case 1 iMonth = "January"
    'Case 2 iMonth = "February"
    'Case 3 iMonth = "March"
    'Case 4 iMonth = "April"
    'Case 5 iMonth = "May"
    'Case 6 iMonth = "June"
    'Case 7 iMonth = "July"
    'Case 8 iMonth = "August"
    'Case 9 iMonth = "September"
    'Case 10 iMonth = "October"
    'Case 11 iMonth = "November"
    'Case 12 iMonth = "December"
    'End Select

    'thecurrentdate = Cstr(currentday) & " " & iMonth & " " &
    Cstr(currentyear)
    on error resume next

    iCompliments = objRS("compliments")
    iComplaints = objRS("complaints")
    If iCompliments < 1 Then iCompliments = 0
    If iComplaints < 1 Then iComplaints = 0
    If isnull(iCompliments) Then iCompliments = 0
    If isnull(iComplaints) Then iComplaints = 0
    tCompliments = iCompliments + iCompliments
    tComplaints = iComplaints + iComplaints

    If isnull(objRS("score")) then
    myendscore = "No data yet"
    Else
    myendscore = objRS("score") & "/" & 10
    End If

    if i=0 then
    tdcolor="#CEC6D6"
    i=1
    Else
    tdcolor="#BCB1C7"
    i=0
    End if
    %>
    <TR>
    <TD COLSPAN="5"><IMG SRC="images/blank.gif" WIDTH=2
    HEIGHT=2></TD>
    </TR>
    <TR>
    <TD WIDTH="862" VALIGN="top" BGCOLOR="<%=tdcolor%>">
    <TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0">
    <TR>
    <TD WIDTH="7"><IMG SRC="images/blank.gif" WIDTH="7"
    HEIGHT="3"></TD>
    <TD VALIGN="top" ALIGN="left" CLASS="cwc">
    <%company_name=trim(company_name)

    %> <B><SPAN CLASS="link1"><A HREF =
    "cwcsearch.asp?searchby=1000&location=&city=&Country=0&supplier=<%=company_n
    ame%>&keyword=&industry=non&alias=&cwc=Y" TITLE="Click here to see recent
    comments">
    <%=company_name%></A></SPAN></B> </TD>
    </TR>
    </TABLE></TD>
    <TD WIDTH="45" ALIGN="RIGHT" VALIGN="middle"
    BGCOLOR="<%=tdcolor%>" CLASS="cwc"><%=iCompliments%></TD>
    <TD WIDTH="45" ALIGN="RIGHT" VALIGN="middle"
    BGCOLOR="<%=tdcolor%>" CLASS="cwc"><%=iComplaints%></TD>
    <TD WIDTH="7" BGCOLOR="<%=tdcolor%>" CLASS="cwc"><IMG
    SRC="images/blank.gif" WIDTH="7" HEIGHT="3"></TD>
    <%
    icount = icount +1
    ObjRS.Movenext
    Loop



    I now want to display the total amount of Compliments and the total amount
    of Complaints.As you can see, i tried the following approach, but it didn't
    work out:

    iCompliments = objRS("compliments")
    iComplaints = objRS("complaints")
    If iCompliments < 1 Then iCompliments = 0
    If iComplaints < 1 Then iComplaints = 0
    If isnull(iCompliments) Then iCompliments = 0
    If isnull(iComplaints) Then iComplaints = 0
    tCompliments = iCompliments + iCompliments
    tComplaints = iComplaints + iComplaints

    Thanx :)

    --

    Kind Regards
    Rudi Ahlers
    +27 (82) 926 1689

    Greater love has no one than this, that he lay down his life for his friends
    (John 15:13).
     
    Lord Merlin, May 14, 2004
    #1
    1. Advertisements

  2. Lord Merlin

    Ray at Guest

    "Lord Merlin" <SP4M_Rudi@SP4M_Bonzai.org.za_SP4M> wrote in message
    news:...
    > How do I add up the values of the data from all of the rows returned?

    i.e -
    > I have a DB, with 148 rows of client info, and I want to addup all the
    > scores. Each entry's score differes. Here is the code I have sofar:


    I think you could do this with all one a single record resultset, like:

    SQL = "SELECT SUM(compliments),SUM(complaints) FROM company_scores"
    objConn.Open <your connection string>
    Set objRS = objConn.Execute(SQL)
    iCompliments = objRS.Fields.Item(0).Value
    iComplaints = objRS.Fields.Item(1).Value
    objRS.Close : Set objRS = Nothing
    objConn.Close : Set objConn = Nothing

    Ray at work
     
    Ray at, May 15, 2004
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Subba Rao via DotNetMonster.com

    script for moving rows up and down and traverse thru rows of HTML table

    Subba Rao via DotNetMonster.com, Mar 19, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    8,790
    Subba Rao via DotNetMonster.com
    Mar 19, 2005
  2. AtomicBob
    Replies:
    14
    Views:
    1,221
    Toby Inkster
    May 2, 2006
  3. Newmanbt

    problems with gridview counting rows

    Newmanbt, Jan 29, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    447
    John Timney \(MVP\)
    Jan 29, 2007
  4. =?Utf-8?B?RGFuaWVs?=
    Replies:
    2
    Views:
    417
    =?Utf-8?B?RGFuaWVs?=
    Feb 27, 2007
  5. Mike Owen

    Allowing entry of a Carriage Return during data entry

    Mike Owen, Jul 27, 2006, in forum: ASP .Net Web Controls
    Replies:
    3
    Views:
    1,081
    Alessandro Zifiglio
    Jul 27, 2006
  6. Noozer
    Replies:
    2
    Views:
    483
    Dr John Stockton
    Aug 1, 2005
  7. edwardfredriks

    counting up instead of counting down

    edwardfredriks, Sep 6, 2005, in forum: Javascript
    Replies:
    6
    Views:
    464
    Dr John Stockton
    Sep 7, 2005
  8. Replies:
    8
    Views:
    343
    Dennis Lee Bieber
    Dec 19, 2012
Loading...