Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > counting DB entry values (not rows)

Reply
Thread Tools

counting DB entry values (not rows)

 
 
Lord Merlin
Guest
Posts: n/a
 
      05-14-2004
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=&Count ry=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).


 
Reply With Quote
 
 
 
 
Ray at
Guest
Posts: n/a
 
      05-15-2004

"Lord Merlin" <SP4M_Rudi@SP4M_Bonzai.org.za_SP4M> wrote in message
news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
counting how often the same word appears in a txt file...But my codeonly prints the last line entry in the txt file dgcosgrave@gmail.com Python 8 12-19-2012 06:29 PM
Allowing entry of a Carriage Return during data entry Mike Owen ASP .Net Web Controls 3 07-27-2006 02:34 PM
Form field entry directs to diff URLs based on entry? AtomicBob HTML 14 05-02-2006 07:07 AM
counting up instead of counting down edwardfredriks Javascript 6 09-07-2005 03:30 PM
Form entry to Time part of database Date entry? Noozer Javascript 2 08-01-2005 08:10 PM



Advertisments