MSChart false information

Hi everybody! as all of us who work with the MSChart control know, it has the nasty habit of displaying false data when none exists.

Currently I have a MSCHart control and a set of ADO recordsets to allow users to filter and graph information. The problem that I have is that when a user submits a query with multiple conditions and one of the conditions happens not to exist in the database, MSChart still shows information for it.

example

strQuery = "SELECT Name, Social_Status FROM Personnel WHERE Social_Status = "Single" OR Social_Status = "Divorced" ORDER BY Name"

will yield 2 columns titled "Single" and "Divorced." If the database contains no records where Social_Status = "Divorced", then rather than showing a column with a value of 0, the chart control throws some random number into the graph.

How can I stop it from doing this?

Thanks!

Andrew

I don't even understand what type of "graph" you could create given the Query you mentioned. Both fields are non-numeric (Name, SocialStatus).
Could you give us more details?

First off, your WHERE clause says "Single" OR "Divorced". This will NOT yield 2 columns. It will only give you one, that contains "Single" or "Divorced" (or the appropriate code for each).

Secondly, how do you propose to "graph" the strings returned by the SQL statement?

Oops! I forgot to include the field "Quantity" in the SQL in my previous message. Sorry about that. Let me try to explain the problem a little more clearly.

I am trying to create a graph that will graph filtered information like
how many employees from a table list of personnel are single, married, or divorced with the columns broken down by criteria selected by the user, and the rows broken into 'Single', 'Married,' or 'Divorced' i.e. the user selects the states North Caroliona, Illinois, Texas, and Alabama, and the graph will show 4 rows labeled with those state names and columns for each state with the total single, married, and divorced employees in the state. The table is further broken down into cities, thus each status is entered several times for each state. For example, with 3 cities entered for North Carolina, the table looks like this:



'North Carolina Divorced 18
'North Carolina Divorced 17
'North Carolina Divorced 23
'North Carolina Married 34
'North Carolina Married 22
'North Carolina Married 19
'North Carolina Single 19
'North Carolina Single 18
'North Carolina Single 26





I think that the problem lies somewhere in the ColumnCount property but I can´t seem to find a solution for it.

The code looks like this:



public Sub ConstructGraph(byval strQuery)
Dim axisID as VtChAxisId
Dim i, j, Quantity, ColumnCounter, ColumnNumber, RowCounter, _
RowNumber, Total as Integer
Dim ColumnName, ColumnCompare, ColumnValue, FieldName, _
FileCompare, FileName, FileTest, FileValue as string

on error GoTo Error_In_Base

'close recordset if it is open; this is necessary for when _
the user selects a filter, clicks "Apply", and later returns _
to the tab "Filers" to make changes

If rsFilterResults.State = adStateOpen then
rsFilterResults.Close
End If

FileName = ComboFile.Text
ColumnName = ComboColumn.Text

'FileName is the name of the field selected by the user from a combo box _
that will be used to divide the information into rows (by state in this case) _
and ColumnName is the name of the field selected by the user to filter the _
information into columns (by status in this case)

rsFilterResults.Open strQuery, cn, adOpenKeyset

set MSChart1.DataSource = rsFilterResults

'with the example I gave above, the SQL would read _
"SELECT State, Quantity, Social_Status FROM Employees WHERE SOCIAL_STATUS = _
'Single' AND (State = 'North Carolina' OR State = 'Illinois' OR State = _
'Texas' OR State = 'Alabama') ORDER BY State, Social_Status

'which generates a recordset such as
'State Status Quantity

'Alabama Divorced 22
'Alabama Divorced 11
'Alabama Divorced 10
'Alabama Single 9
'Alabama Single 11
'Alabama Single 21
'Illinois Divorced 11
'Illinois Divorced 13
'Illinois Divorced 44
'Illinois Married 46
'Illinois Married 22
'Illinois Married 12
'Illinois Single 18
'Illinois Single 17
'Illinois Single 24
'North Carolina Divorced 18
'North Carolina Divorced 17
'North Carolina Divorced 23
'North Carolina Married 34
'North Carolina Married 22
'North Carolina Married 19
'North Carolina Single 19
'North Carolina Single 18
'North Carolina Single 26
'Texas Divorced 22
'Texas Divorced 38
'Texas Divorced 29
'Texas Married 46
'Texas Married 26
'Texas Married 39
'Texas Single 47
'Texas Single 37
'Texas Single 35

If rsFilterResults.RecordCount = 0 then
MsgBox "Your query has generated no results.", _
vbInformation
Exit Sub
End If

Total = 0
RowCounter = 0
RowNumber = 0

If rsFilterResults.BOF = false And rsFilterResults.EOF = false then
rsFilterResults.MoveFirst
Do Until rsFilterResults.EOF

for i = 0 to rsFilterResults.Fields.Count - 1
If rsFilterResults.Fields(i).Name = "Quantity" then
Quantity = rsFilterResults.Fields(i).Value
Exit for
End If
next

for i = 0 to rsFilterResults.Fields.Count - 1
If rsFilterResults.Fields(i).Name = FileName then
FileValue = rsFilterResults.Fields(i).Value
Exit for
End If
next

for i = 0 to rsFilterResults.Fields.Count - 1
If rsFilterResults.Fields(i).Name = ColumnName then
ColumnValue = rsFilterResults.Fields(i).Value
Exit for
End If
next

Call ConstructRowList(FileValue)

RowCounter = lstFile.ListCount

If ColumnValue <> ColumnCompare then
ColumnNumber = ColumnNumber + 1
ColumnCounter = ColumnCounter + 1
RowNumber = 1
Total = Quantity
End If

If FileValue <> FileCompare then
Total = Quantity
End If

If ColumnValue = ColumnCompare And _
FileValue <> FileCompare then
RowNumber = RowNumber + 1
End If

If ColumnValue = ColumnCompare And _
FileValue = FileCompare then
Total = Total + Quantity
With MSChart1
.RowCount = RowCounter
.Row = RowNumber
.ColumnCount = ColumnCounter
.Column = ColumnNumber
.ColumnLabel = ColumnCompare
.RowLabel = FileCompare
.Data = Total
End With
End If

rsFilterResults.MoveNext

FileCompare = FileValue
ColumnCompare = ColumnValue

Loop
End If

List1.AddItem RowCounter

With MSChart1
.RowCount = RowCounter
.Visible = true
.ShowLegend = true
End With

for axisID = VtChAxisIdX to VtChAxisIdZ
'set chart axis titles
With MSChart1.Plot.Axis(axisID, 1).AxisTitle
.Visible = true
Select Case axisID
Case 0
.Text = txtTitulo(1).Text
Case 1
.Text = txtTitulo(2).Text
Case 2
.Text = txtTitulo(3).Text
Case 3
.Text = txtTitulo(4).Text
End Select
End With
next

Exit Sub

Error_In_Base:
MsgBox "error in Database." & Chr(13) & Chr(10) & _
Chr(13) & Chr(10) & Err.Description, vbCritical

End Sub

public Sub ConstructRowList(byval FileValue)
Dim i as Integer

for i = 0 to lstFile.ListCount - 1
lstFile.Selected(i) = true
If lstFile.Text = FileValue then
Exit Sub
End If
next

lstFile.AddItem (FileValue)





This works fine for when information exists that matches all of the criteria, e.g. all selected states are home to single, married, and divorced empolyees. The problem arises when,like in the example table I gave above, no married employees live in Alabama. The Chart then shows the right numbers for single
and divorced personnel in all states and divorced personnel in Illinois,
Texax, and North Carolina but then puts some random number in for married
personnel in Alabama instead of no column or a value of 0.

Normally to solve this problem I would simply enter a field in the database
where State = Alabama and Social_Status = 'Married' and Quanity = 0. The problem with this is that the client needs to be able to use the program to graph information such as inventory levels of products a, b, c, d, and e in warehouses 1, 2, 3, and 4. With thousands of product types in inventory, I do not want to have to enter a value for each product if it is not necessary. Also, if for example warehouse 4 doesn´t stock product e, it would be ridiculous to waste valuable memory with a field for the product.

Did that confuse everyone enough? I hope so. If ANYONE has any idea how to solve this problem then I will be greatly appreciative! Thank you for taking the time to read and reply to this message, as this is my first time using MSChart and it is giving me some major headaches!

Thanks!

Andrew

I dont know how far this will be useful, but try this: You lines, after you check some conditions, and decide to add that value to the graph data:

If ColumnValue = ColumnCompare And _
FileValue = FileCompare then
Total = Total + Quantity
With MSChart1
.RowCount = RowCounter
.Row = RowNumber
.ColumnCount = ColumnCounter
.Column = ColumnNumber
.ColumnLabel = ColumnCompare
.RowLabel = FileCompare
.Data = Total
End With
End If


, i would say add an else statement and
add a default zero value to data.
Its a combined if - ie something and something - so make sure the "else" makes correct sense in you case.

Another thing: I used MS Chart control once, and was throughly frustrated too. It accepts arrays as data from which it needs to plot. So, you can copy from your DB, into a temparray, and them set it to chart. This also allows you to have some default values, and also makes sure that all of same size etc.

Last, there is one RandomFill Property. Check out it setting that to False can help!


RK