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 June 7th, 2007, 02:34 PM
Registered User
 
Join Date: Jun 2007
Location: Syracuse, Utah, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default SUM Function in ASP

Hi, I'm working on a new website for my travel company. The newest feature we want to add is points reward system. I've written the script to pull the records for the specified user from the database and loop through and show all their point earnings. Now the next thing I need is to show a balance of all their total points. I've included pictures below of the database table, and the area that shows up on the My Account section of the website.

I just can't seem to get the SUM function right! I'm always getting an error, so all help is appreciated!

- Microsoft Access Table (for POINTS)
LINK: http://67.171.119.96/images/ads/points_1.png

- Webpage Showing Points
LINK: http://67.171.119.96/images/ads/my_points_ad.png

Thanks!

- Kyle J

 
Old June 7th, 2007, 03:21 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I didn't view your links but you want to do something like:

SELECT Sum(points) From Table where userID = 'user'

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
 
Old June 7th, 2007, 03:21 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

How does the code for your SUM function look like right now?

If you're displaying the data in a loop while getting it from a recordset, you can simply keep a running total. E.g.:

total = total + CInt(myRecordset("Points"))
myRecordset.MoveNext

Alternatively, you can fire a separate query against the database that SUMS the data while grouping it by the user's ID.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
 
Old June 7th, 2007, 03:29 PM
Registered User
 
Join Date: Jun 2007
Location: Syracuse, Utah, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, I've tried this, but I get this error:

Microsoft VBScript compilation error- Error '800a03fd'

Expected 'Case'

/content/my_points.html, line 16

SELECT Sum(ADD_USER) From POINTS where ADD_USER = '" & USERNAME & "'
-------^

Thanks for any help!

Quote:
quote:Originally posted by dparsons
 I didn't view your links but you want to do something like:

SELECT Sum(points) From Table where userID = 'user'

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
 
Old June 7th, 2007, 03:40 PM
Registered User
 
Join Date: Jun 2007
Location: Syracuse, Utah, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, I tried this code and it's working to a point. The problem is it's not the right total.

The code is:

<%
SET POINT_VIEW = SERVER.CREATEOBJECT("ADODB.RECORDSET")
POINT_VIEW.OPEN "SELECT * FROM POINTS WHERE ADD_USER = '" & USERNAME & "' ORDER BY ADD_ID DESC", DBCONN, 1, 4

balance = balance + Int(POINT_VIEW("ADD_POINTS"))
POINT_VIEW.MoveNext
%>

The values in the table are:

10
5
15
-15
-10

(for this one user)

And the result I'm getting is:

-10 (although it should be 5)


Any answers to this? Thanks in advance!

 
Old June 7th, 2007, 03:48 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I prefer to do this totally in SQL.

This is my SQL statement that i used (I have used a temp table to recreate your data)

DECLARE @tbl Table(value int)
Insert into @tbl(value) Values(10)
Insert into @tbl(value) Values(5)
Insert into @tbl(value) Values(15)
Insert into @tbl(value) Values(-15)
Insert into @tbl(value) Values(-10)

SELECT Sum(Value) as Points from @tbl --When executed, the value 5 is returned in my result set

So instead of
POINT_VIEW.OPEN "SELECT * FROM POINTS WHERE ADD_USER = '" & USERNAME & "' ORDER BY ADD_ID DESC", DBCONN, 1, 4

why not try
POINT_VIEW.OPEN "SELECT sum(points) as points FROM POINTS WHERE ADD_USER = '" & USERNAME & "' ORDER BY ADD_ID DESC", DBCONN, 1, 4

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
 
Old June 7th, 2007, 04:21 PM
Registered User
 
Join Date: Jun 2007
Location: Syracuse, Utah, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks dparsons - Before I ruin all of my code that I've done and try the one your provided, I just wanted to clarify some stuff. You have set the values into the script there, but I would like to remind you that these values will be constantly changing, as points are added and deducted from the user's account.

Do you know any reason why the "balance = balance + Int..." wouldn't be working?

Thanks
 
Old June 7th, 2007, 04:42 PM
Registered User
 
Join Date: Jun 2007
Location: Syracuse, Utah, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Okay, for anyone willing to help, this is the webpage (portion of the webpage) that I'm working with. It displays the individual records of point accumulations, but I need it to total correctly. Thanks (and hope this helps)!

<%
SET POINT_VIEW = SERVER.CREATEOBJECT("ADODB.RECORDSET")
POINT_VIEW.OPEN "SELECT * FROM POINTS WHERE ADD_USER = '" & USERNAME & "' ORDER BY ADD_ID DESC", DBCONN, 1, 4

balance = balance + Int(POINT_VIEW("ADD_POINTS"))
POINT_VIEW.MoveNext
%>

<div class="headBar"><div id="addIcos"><img src="/images/icons/money_dollar.png"></div> myPoints Account - <a style="color: #666; text-decoration: underline" href="/mytravel/mypoints.asp">How to Earn Points</a> <div id="helpCons"><a href="#" onmouseover="showHelp(/*id*/'points',1,'style.visibility','visible')" onmouseout="resetHelp()"><img src="/images/icons/help.png" border="0"></a></div></div>
<div class="smllBar">
<%
POINT_VIEW.PageSize = MESSAGEPERPAGE
IF NOT POINT_VIEW.EOF THEN POINT_VIEW.AbsolutePage = currentpage

IF POINT_VIEW.EOF THEN
%>

<div class="noAlrtTop">You have no myPoints available.</div>
<div class="noAlrtTxt">It's easy to earn more points though! Just login to your account regularly or make reservations, and you'll instantly earn points!</div>
<%ELSE%>
    <table cellpadding="0" cellspacing="0" height="80%" width="100%">
        <tr>
            <td class="pointTabA" id="blogHead">Category</td>
            <td class="pointTabB" id="mailHead">Comment</td>
            <td class="pointTabC" id="itinHead">Points</td>
        </tr>
<%
FOR U = 1 TO MESSAGEPERPAGE
    IF POINT_VIEW.EOF THEN EXIT FOR
%>
        <tr>
            <td class="pointTabA"><%=POINT_VIEW("ADD_CAT")%></td>
            <td class="pointTabB"><%=POINT_VIEW("ADD_COM")%></td>
            <td class="pointTabC"><%=POINT_VIEW("ADD_POINTS")%></td>
        </tr>
<% POINT_VIEW.MOVENEXT
    NEXT%>
        <tr>
            <td height="100%"></td>
            <td height="100%"></td>
            <td height="100%"></td>
        </tr>
    </table>
    <table cellpadding="0" cellspacing="0" height="20%" width="100%">
        <tr>
            <td height="100%" width="100%" valign="bottom">
                <table cellpadding="0" cellspacing="0" height="100%" width="100%">
                    <tr>
                        <td width="70%"><a style="font: normal 9pt verdana; color: #666; text-decoration: underline; padding-left: 10" href="/mytravel/mypoints_account.asp">View All Activity</a></td>
                        <td width="15%"><div style="font: normal 9pt verdana; color: #666; padding-right: 10">Balance:</div></td>
                        <td width="15%"><div style="font: normal 11pt bookman old style; color: #666; text-align: center"><%=balance%></div></td>
                    </tr>
                </table>
            </td>
        </tr>
    </table>
<%END IF%>
</div>

 
Old June 7th, 2007, 04:57 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

You need to keep the running total *inside* the loop; not just once at the beginning. E.g.:

Do While Not MyRecordset.EOF
  ' Present your individual rows here.

  Total = Total + MyRecordset("Points")
  MyRecordset.MoveNext

Loop

Your code uses quite a different approach to loop the data, but you can use the same idea. Just before:

<% POINT_VIEW.MOVENEXT

add:

balance = balance + Int(POINT_VIEW("ADD_POINTS"))

So you end up with something like:

            <td class="pointTabC"><%=POINT_VIEW("ADD_POINTS")%></td>
        </tr>
balance = balance + Int(POINT_VIEW("ADD_POINTS"))
<% POINT_VIEW.MOVENEXT

After the last loop, balance should contain the total number of points.

Doug: to use SUM, don't you need a GROUP BY clause on the user's name?

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
 
Old June 7th, 2007, 05:12 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Imar: It depends. Consider this code:
declare @tbl table (value int, userID int)
INSERT INTO @tbl (value, userID)VALUES(10, 1)
INSERT INTO @tbl (value, userID)VALUES(10, 1)
INSERT INTO @tbl (value, userID)VALUES(10, 1)
INSERT INTO @tbl (value, userID)VALUES(10, 1)
INSERT INTO @tbl (value, userID)VALUES(10, 2)
INSERT INTO @tbl (value, userID)VALUES(10, 2)

SELECT sum(value), userID from @tbl where userID = 1

this will return you a resultset of 40 if, however, you do:
SELECT sum(value), userID from @tbl group by userID

Your resultset will be
Value UserID
40 1
20 2

You only need a group by clause if you are selecting data from a column and you are not using an aggregate function on it, so in my last examle if i were to have done

SELECT sum(value), userID from @tbl where userID = 1

this would have generated a SQL error but simply having SELECT Sum(value) without a group by clause is perfectly legal.

kj, i completely understand that the data will not be known at runtime, i was using the code i provided (using the values you had provided) to illustrate how this would be done in SQL. You could potentially have 1000 rows of data for one user and calling Sum() will return you the sum of all values in that column for that user.



================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========




Similar Threads
Thread Thread Starter Forum Replies Last Post
sum function pcase XSLT 2 January 2nd, 2008 05:51 PM
sum() function felixm_jr Reporting Services 1 April 22nd, 2007 01:59 AM
Xpath: sum function gracehanh XSLT 13 September 27th, 2005 09:30 AM
SUM Function jmss66 Classic ASP Basics 17 July 29th, 2003 08:00 AM
Need Help with the Sum Function athanatos XSLT 1 July 22nd, 2003 10:06 AM





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