Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 194005 - Last Review: March 14, 2005 - Revision: 4.3
HOWTO: Use ADO to Return a Summary Row
This article was previously published under Q194005
The sample code in this article demonstrates how to extract the value of
summary rows using ADO. Summary rows are produced when you use a COMPUTE BY
clause with any row aggregate functions in a SQL statement. These summary
values appear as additional rows in the query results, unlike the aggregate
function results of a GROUP BY clause, which appear as new columns.
For example, the following query returns the summation for the "price" and
"advance" columns in addition to the columns in the SELECT clause:
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type
If you create a recordset based on this SQL statement, and if you loop
through the recordset, you can only see the columns specified in the SELECT
clause. This is because ADO returns the results of a query with a COMPUTE
statement as multiple recordsets. To get the summary rows, you must loop
through each recordset from the multiple recordsets.
The following code demonstrates this technique. This code uses the
NextRecordset method of the Recordset object to loop through the multiple
recordsets. The code uses the Pubs database supplied with Microsoft SQL
Server.
Sample Code
<%@ LANGUAGE="VBSCRIPT" %>
<HTML>
<HEAD>
<TITLE>Compute Row results</TITLE>
</HEAD>
<BODY>
<%
sql="SELECT price, advance,type FROM titles "
sql= sql & "ORDER BY type, price "
sql= sql & "COMPUTE SUM(price), SUM(advance) BY type "
sql= sql & "COMPUTE SUM(price), SUM(advance)"
set conn = Server.CreateObject("ADODB.Connection")
' Modify the connection string to reflect your
' Data Source Name (DSN).
conn.open "Pubs","sa",""
set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = sql
set cmd.ActiveConnection = conn
set rs = Server.CreateObject("ADODB.Recordset")
set rs = cmd.Execute
%>
<table>
<%count = 1
Do Until rs Is Nothing%>
<tr>
<%For x=0 to rs.Fields.count-1%>
<td><b><%response.write rs(x).name%> </b><hr></td>
<%next%>
</tr>
<%Do While Not rs.EOF%>
<tr>
<%For x=0 to rs.Fields.count-1%>
<td><%=rs(x).value%></td>
<%next%>
</tr>
<%rs.MoveNext
Loop
Set rs = rs.NextRecordset
count = count + 1
Loop
%>
</table>
</BODY>
</HTML>
For additional information on retrieving Multiple Recordsets, please see
the following article in the Microsoft Knowledge Base:
182290Â
(http://kbalertz.com/Feedback.aspx?kbNumber=182290/EN-US/
)
HOWTO: Return Multiple Recordsets with Column Names and Values
APPLIES TO
- Microsoft Active Server Pages 4.0
- Microsoft ActiveX Data Objects 1.5
- Microsoft ActiveX Data Objects 2.0
- Microsoft ActiveX Data Objects 2.1 Service Pack 2
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
- Microsoft Internet Information Server 4.0
- Microsoft Internet Information Services 5.0
- Microsoft Data Access Components 1.5
- Microsoft Data Access Components 2.0
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
| kbcodesnippet kbdatabase kberrmsg kbhowto kbscript KB194005 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate