<%@ Language=VBScript %> <% 'Request the current year from querystring 'The year was passsed to this page in the form Data.asp?Year=199x (where x=6,7,8) Dim intYear, strDataURL intYear = Request("Year") 'Define database objects 'oRs - Recordset object 'strSQL - String variable to contain the SQL query 'intCounter - Numeric value to keep a track of number of records Dim oRs, strSQL, intCounter intCounter = 0 'Initialize the database objects Set oRs = Server.CreateObject("ADODB.Recordset") 'strXMLData - String variable to contain the entire XML data document for the chart 'strCategories - String variable to store the element and it's child elements. 'strAmountDS - String variable to store the amount 'strQuantityDS - String variable to store Quantity dataset Dim strXMLData, strCategories, strAmountDS, strDiscountDs, strQuantityDS 'Initialize the element strXMLData = "" & vbCrlf 'Initiate element strCategories = "" & vbCrlf 'Initialize the amount and quantityt dataset strAmountDS = "" & vbCrlf strQuantityDS = "" & vbCrlf strDiscountDS = "" & vbCrlf 'Get a list of all the years strSQL = "SELECT TOP 5 CustomerName, SUM(ExtendedPrice) As Total, SUM(Quantity) As Quantity, SUM(Discount*ExtendedPrice) As Discount FROM Invoices WHERE YEAR(OrderDate)=" & intYear & " GROUP BY CustomerName ORDER BY SUM(ExtendedPrice) DESC" oRs.Open strSQL, DB_CONNECTION While not oRs.EOF 'Increment the counter intCounter = intCounter + 1 'Generate the element strCategories = strCategories & "" & vbCrlf 'Generate the set element for both amount and quantity dataset strAmountDS = strAmountDS & "" & vbCrlf strDiscountDS = strDiscountDS & "" & vbCrlf strQuantityDS = strQuantityDS & "" & vbCrlf oRs.MoveNext() Wend 'Close the category and dataset elements strCategories = strCategories & "" & vbCrlf strAmountDS = strAmountDS & "" & vbCrlf strDiscountDS = strDiscountDS & "" & vbCrlf strQuantityDS = strQuantityDS & "" & vbCrlf 'Generate the XML document by concatenating the strings we've generated. Also, close the element. strXMLData = strXMLData & strCategories & strAmountDS & strDiscountDS & strQuantityDS & "" 'Destroy the recordset objects Set oRsYears = nothing Set oRs = nothing 'Write the XML data to output stream Response.Write(strXMLData) %>