How To Databound Chart Control Requires First Column To Be Text (190214)

SUMMARY

When the Microsoft Chart Control 6.0 is databound using the ADO DataControl or the Data Environment, the first column in the resulting recordset must be of datatype Text (or a corresponding datatype based on the database in use.) The Chart control attempts to plot the data in the first column if it contains non-text values. The result of this can be a Chart that does not plot at all, or a Chart that contains additional (and unwanted) plots. This requirement is not discussed in the Visual Basic documentation.

MORE INFORMATION

The following example provides an illustration of a Chart that contains an additional data plot and provides a solution.

Step by Step Example

Create a new Standard EXE project in Visual Basic. Form1 is created by default.
Choose Components from the Project menu, and check "Microsoft ADO DataControl 6.0 (OLEDB)" and "Microsoft Chart Control 6.0." Click OK. These controls are now in the Toolbox.
Place an instance of the Chart Control, MSChart1, on Form1.
Place an instance of the ADO DataControl, Adodc1, on Form1.
For this step, it may be necessary to adjust the path information for the Biblio.mdb database. Set the ConnectionString property of the ADO DataControl to:
```
      Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data
      Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb
					
```

Set the RecordSource property of the ADO DataControl to:

      SELECT Titles.[Year Published], Count(Titles.Title) AS CountOfTitle
      FROM Titles GROUP BY Titles.[Year Published];

Set the following properties for the MS Chart Control:

      chartType:  VtChChartType2dLine
      datasource: Adodc1

Run the project. You should see a green line that is the actual plot of the data, and an additional red line that is the Chart Control's attempt to chart the non-text value of the first column of data.
To resolve this problem, it is necessary to convert the first column of data to text. This can be accomplished by modifying the ADO Control's RecordSource property. The CStr function will be used for this example. This may require a different function depending on the type of database in use. Change the RecordSource property of the ADO DataControl to:
```
      SELECT cstr(Titles.[Year Published]), Count(Titles.Title) AS
      CountOfTitle FROM Titles GROUP BY Titles.[Year Published];
					
```
Run the project. You should now see one Red data plot that represents the data in the Recordset.