Creating a Chart using Data from a Database with MS Chart Control

Introduction

This tutorial shows you step-by-step how you may use the Microsoft Chart Control with data retrieved from a database, explaining the logic used. The data is retrieved by a suitable query run against a database on the localhost, XPHTEK, a SQL 2000 Server. The VB6 program and the SQL Server are both running on a Windows XP Professional machine.

Retrieving data from a database

Using ADO objects and a suitable connection string, the data will be retrieved from the database. The chart will be using the data from the SQL 2000 Server and therefore the SQL Server must be running.

Database details

The details of a simple table called 'Products' on the TestWiz database will be used. The next picture shows the database, 'TestWiz' on the left, the table in the tables list on the right and all the data it contains at the bottom. It has four rows of data on six columns.

Visual Basic Project to retrieve data

Adding a Microsoft Chart Control

Create a standard exe project and going from Project-->Components, add a reference to the MS Chart Control as shown in the next picture.

This control, Microsoft Chart Control 6.0 (SP4)(OLE DB)[mschrt20.ocx] is located at C:windowssystem32 directory. With this added you will be able to access its properties and methods. It has a large number of properties and methods out of which we will be using only a few. The object browser shows all the properties and methods. Interestingly enough, the DataSource class is not found, a property used in one of Microsoft's own examples. This is very interesting in light of the fact that Microsoft considers MS Chart a control to which data can be bound.

Adding a reference to the ADO 2.8 library

Going from Project-->References in the drop-down, the References window can be accessed, which shows the large number of library references that VB 6 programs can access. Make sure that the Microsoft Activex Data Objects 2.8 library has a check mark against it before you close this window. There are a large number of tutorials on the ASPFree.com site where you can find details about the various objects represented by the ADO object model.

Connecting the chart to data

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.

Assigning retrieved data to an array to display on the chart

MS Chart Control can also use data in an array and display it. The following code can be used to display data after assigning the recordset to the array. The same query as in the previous case is used for retrieving data from the same database.

Private rsProducts As New ADODB.Recordset

Private cn As New ADODB.Connection

Private Sub Form_Load()

MSChart1.ToDefaults

' declare variable to hold query string

Dim strQuery As String 

   'This Connection string is specific to the SQL 2000 on this machine

   cn.ConnectionString = 

   _"Provider=SQLOLEDB.1;Password=XXXXX; Persist Security Info=True;" & _

  "User ID=sa; Initial Catalog=TestWiz; Data Source=XPHTEK"

   ' Open the connection.

   cn.Open

   ' this Query retrieves only four fields.

   strQuery = "SELECT ProductName, UnitPrice, UnitsInStock, 

UnitsOnOrder FROM Products"

   ' Open the recordset.

   rsProducts.CursorLocation = adUseClient

   rsProducts.Open strQuery, cn, adOpenKeyset

'this array will contain data for the 4 columns and variable number of rows

ReDim chrtarr(1 To rsProducts.RecordCount, 1 To 4)

MSChart1.ShowLegend = True

MSChart1.chartType = VtChChartType2dBar

'relate array to the recordset returned

For X = 1 To rsProducts.RecordCount

chrtarr(X, 1) = rsProducts("ProductName")

chrtarr(X, 2) = rsProducts("UnitPrice")

chrtarr(X, 3) = rsProducts("UnitsInStock")

chrtarr(X, 4) = rsProducts("UnitsOnOrder")

rsProducts.MoveNext

Next X

'feed chart from this array

With MSChart1

.ChartData = chrtarr

'only the three columns, 2,3 4 will provide data

.ColumnCount = 3

.ColumnLabelCount = 1

.Column = 1

End With

End Sub

With this code the chart display is as shown in the next picture. The important lines are explained with the comments before the statement.

This agrees with the number of rows and columns with values that can be verified.

Summary

This tutorial has shown some pitfalls in using this control to display data retrieved from a database. The simple table was specially created to test the functionality. All said and done, this is not an easy control to configure. The chart size will not accommodate to the form size. Column and row labels have to be assigned by code. ADO does not bind to this control as it is claimed. However you can bind the data to an array, the first field will be assigned to the x-axis of the bar graph. I recommend the readers of this article read my basic MS Chart article on this site.