Creating a Chart Using Data From an ADO Recordset

See Also

This example creates a chart using data from an ADO recordset. A tab-delimited string is created from the returned recordset data, and then this string is used with the SetData method to set the chart data.

To run this example, copy the remainder of the text in this topic into an HTML page.

<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:350"></object>
<object id=ADOConnection1 classid=CLSID:00000514-0000-0010-8000-00AA006D2EA4></object>

<script language=vbs>
Sub Window_OnLoad()
    Dim rs, categories, values
    
    ' This example connects to the Northwind sample database
    ' and charts the Query "Category Sales for 1995". ADO is used to open a
    ' connection to the database and return the entire recordset. The query
    ' contains two columns: Category Name and Category Sales.
    ' The records are then extracted into strings.

    categories = ""
    values = ""

    ' Open the connection and execute the query.
    ADOConnection1.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\nwind.mdb"
    Set rs = ADOConnection1.Execute("SELECT * FROM [Category Sales for 1995]")

    ' Start at the first record and move through the entire recordset.
    ' Field 0 is the category name, Field 1 is the sales value.
    ' Create a tab-delimited string for the names and one for the values.
    rs.MoveFirst
    While Not rs.EOF
        categories = categories & rs.Fields(0).Value & Chr(9)
        values = values & rs.Fields(1).Value & Chr(9)
        rs.MoveNext
    Wend
    rs.Close
    ADOConnection1.Close
    
    ' Remove the leftover tab character at the end of the strings.
    categories = Left(categories, Len(categories) - 1)
    values = Left(values, Len(values) - 1)
    
    ' Create a chart with one series (called "Sales").
    ChartSpace1.Clear
    ChartSpace1.Charts.Add
    ChartSpace1.Charts(0).SeriesCollection.Add
    ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Sales"

    'Set the series categories and values using the strings created from the recordset.
    Set c = ChartSpace1.Constants
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, c.chDataLiteral, categories
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, c.chDataLiteral, values
    
    ' Set the chart type and format the axis as U.S. dollars.
    ChartSpace1.Charts(0).Type = c.chChartTypeBarClustered
    ChartSpace1.Charts(0).Axes(c.chAxisPositionBottom).NumberFormat = "$#,##0"
End Sub
</script>