Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 returning the SUM of a column to ASP
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dirwin26
Yak Posting Veteran

81 Posts

Posted - 09/23/2005 :  13:53:40  Show Profile  Reply with Quote
How do I get the compute result of a column (sum) into my webpage using vbscript from sql. I am using the
<OPTION VALUE="<%=RS("REVTURN")%>"><%=RS("REVTURN")%>
method in ASP and my SP looks like:

SELECT TheDate, revturn
FROM #res2
where TheDate = @NextQDate - 120
order by thedate
compute sum (revturn)

SELECT TheDate, REVTURN AS 'revturn'
FROM #res2
ORDER BY TheDate
GO

The option value is returning the entire column and NOT the sum of the column to my webpage. All I need is the sum. Any ideas on this ?

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/23/2005 :  14:21:47  Show Profile  Reply with Quote
You need to do a SUM with a GROUP BY.

If you don't know how to do this, here are a couple of online courses that are a good introduction to the basics of SQL.
http://www.sqlcourse.com/
http://sqlcourse2.com/



CODO ERGO SUM
Go to Top of Page

dirwin26
Yak Posting Veteran

81 Posts

Posted - 09/23/2005 :  14:28:29  Show Profile  Reply with Quote
Thanks!
Go to Top of Page

dirwin26
Yak Posting Veteran

81 Posts

Posted - 09/23/2005 :  14:51:59  Show Profile  Reply with Quote
Sorry, I got a bit ahead of myself there. Group BY seems easy enough, but it doesn't wotk. The problem has to do with the fact that the RS is not picking up the entire resultant table, or the sum total for that matter. It's picking up the results of only the inputs in the ADODB.connection and not the entire result.
So if this is my call procedure ;

<%
SQL = "EXEC ddx..rev @Ticker='" & ticker & "', @NextQDate='"&NextQDate&"'
Set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open SQL, odbc, 0, 1.......


I am only geting the value for "ticker" and "NextDate" and not the entire table which was created off of these variables.....seems kind of weird.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/23/2005 :  15:28:50  Show Profile  Reply with Quote
It sounds like you are saying that you are having trouble with ADO, not with SQL.

This is the Transact-SQL forum, so you may want to post this question on a forum that deals with client issues, like the ASP.NET forum.



CODO ERGO SUM

Edited by - Michael Valentine Jones on 09/23/2005 15:29:54
Go to Top of Page

dirwin26
Yak Posting Veteran

81 Posts

Posted - 09/23/2005 :  15:49:26  Show Profile  Reply with Quote
I'll give it a try...thanks.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/23/2005 :  16:14:31  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
I believe COMPUTE returns another recordset, so you'd need to move from the first recordset returned via ADO to the next recordset to get the total ... use the NextRecordset() method of your intial recordset object to do this, after reaching EOF on the first one.

I'd accumulate the total within your ASP page using a variable and return the total that way if I were you, it is much easier.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000