Creating a Chart using Data from a Database with MS Chart Control - Connecting the chart to data

(Page 3 of 4 )

Code to retrieve data using DataSource binding

Drag and drop a Microsoft Chart control onto the form, Form1 as shown.

From the properties window some of the following properties were set as shown:

Series Type: 1 - VtChSeriesType2Bar
RowCount: 4
Row: 1
RowLabel :R1
ShowLegend: False
ColumnCount: 3
Column:1
ColumnLabel:C1

It could happen that the design time settings can override run time settings and you should test the control's behavior at run time and tune it appropriately.

You could also access the Property pages for this ActiveX control to set some of the properties.

In the form's Form_OnLoad () event use the following snippet to retrieve and connect the data using the DataSource property of the MS Chart Control. The snippet in purple is Microsoft's suggestion to bind data to the MS Chart Control (this is Microsoft's example adopted for this tutorial).

Private rsProducts As ADODB.Recordset

Private cn As ADODB.Connection

Private Sub Form_Load()

Set cn = New ADODB.Connection

MSChart1.ToDefaults

' Establish the connection using the connection string.

   cn.ConnectionString = 

   _"Provider=SQLOLEDB.1;Password=XXXXXXX;" & _

   "Persist Security Info=True; User ID=sa;" & _

   "Initial Catalog=TestWiz; Data Source=XPHTEK"

' Open the connection.

   cn.Open

   Dim strQuery As String ' SQL query string.

   ' Create a query that retrieves only four fields.

   'strQuery = "SELECT ProductName, UnitPrice,

   'UnitsInStock, UnitsOnOrder  FROM Products"

   strQuery = "SELECT ProductName, UnitPrice, UnitsInStock, 

UnitsOnOrder FROM Products"

   Set rsProducts = New ADODB.Recordset

   ' Open the recordset.

   rsProducts.CursorLocation = adUseClient

   rsProducts.Open strQuery, cn, adOpenKeyset

   ' Set the DataSource to the recordset.

   With MSChart1

         .ShowLegend = True

       Set .DataSource = rsProducts

    End With

End Sub

When the program runs without any errors, you get a chart as shown in the next picture. Instead of four rows there are only three. Also the display of the legend is not complete. This display is irrespective of the changes you make either in the Properties window, or the Property Pages.

The databinding to MS Chart is less than satisfactory as it does not give a correct picture of the data.

Next: Assigning retrieved data to an array to display on the chart >>
 

More Code Examples Articles
More By Jayaram Krishnaswamy

Comments On: Creating a Chart using Data from a Database with MS Chart Control

· Progress has a price to pay. Software getting outdated is going to remain for ever...   >>> Post your comment now!