Creating a Chart using Data from a Database with MS Chart Control
We have seen earlier how to bring in a Microsoft Chart Control to a VB 6 project and configure some of its properties. The data displayed by the chart was hard-coded. In real life scenarios, the data will be generated by a program and the format of the output must be fed to the chart programmatically. Again this is one of the frequently asked popular questions on many forums: how do you use the MS Chart Control with data retrieved from a database?
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.
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.
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.
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
"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.