Creating a Chart using Data from a Database with MS Chart Control - Assigning retrieved data to an array to display on the chart

(Page 4 of 4 )

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.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

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!