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

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 returning the SUM of a column to ASP

Author  Topic 

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-23 : 13:53:40
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)

7020 Posts

Posted - 2005-09-23 : 14:21:47
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 - 2005-09-23 : 14:28:29
Thanks!
Go to Top of Page

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-23 : 14:51:59
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)

7020 Posts

Posted - 2005-09-23 : 15:28:50
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
Go to Top of Page

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-23 : 15:49:26
I'll give it a try...thanks.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-23 : 16:14:31
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
   

- Advertisement -