<%@ 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 'strXMLData - String variable to contain the entire XML data document for the chart Dim strXMLData strXMLData = "" 'Initialize the recordset object Set oRs = Server.CreateObject("ADODB.Recordset") '--------------------XML Data for TOP 5 COUNTRIES------------------------- 'Query the database strSQL = "SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE YEAR(OrderDate)=" & intYear & " GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC" oRs.Open strSQL, DB_CONNECTION 'Create the element strXMLData = "" & vbCrlf 'Now iterate through each data row intCounter=0 While not oRs.EOF 'Increase the count intCounter=intCounter+1 'Append the value in format strXMLData = strXMLData & "" & vbCrlf oRs.MoveNext() Wend 'Entire XML - concatenation strXMLData = strXMLData & "" oRs.Close() '-------------------------------------------------------------------- 'Destroy objects Set oRs= nothing 'Write the XML data to output stream Response.Write(strXMLData) %>