HOWTO: Use ADO to Return a Summary Row (194005)


The information in this article applies to:

  • Microsoft Active Server Pages
  • ActiveX Data Objects (ADO) 1.5
  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.1 SP2
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • ActiveX Data Objects (ADO) 2.7
  • Microsoft Internet Information Server 4.0
  • Microsoft Internet Information Server 5.0
  • Microsoft Data Access Components 1.5
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1 SP2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q194005

SUMMARY

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.

MORE INFORMATION

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>

REFERENCES

For additional information on retrieving Multiple Recordsets, please see the following article in the Microsoft Knowledge Base:

182290 HOWTO: Return Multiple Recordsets with Column Names and Values

Modification Type:MinorLast Reviewed:3/14/2005
Keywords:kbCodeSnippet kbDatabase kberrmsg kbhowto kbScript KB194005
©2004 Microsoft Corporation. All rights reserved.