Separating Chart Series

Question

I currently have a chart element that has 4 Series. My problem is that while they work individually, if I set more than 1 Series it sets the data for all of them to the last DataSet (So Series 1,2 & 3 have the same positions as 4).

Could someone have a look at my broken code and let me know where it's all going wrong? And maybe some pointers on neatening it up... I have never worked with any form of charts before.

Using con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Response.mdb;")


    Dim MyQuery As String = "SELECT qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood, Count(qry_Response_By_Date_1.Mood) AS CountOfMood FROM qry_Response_By_Date_1 GROUP BY qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood HAVING (((qry_Response_By_Date_1.Mood)='Happy'));"
    Using cmd = New OleDbCommand(MyQuery, con)
        Dim MyData As New OleDbDataAdapter(MyQuery, con)
        Dim MyDataSet As New DataSet
        con.Open()
        MyData.Fill(MyDataSet, "Table")
        ChrtMoodChanges.DataSource = MyDataSet.Tables("Table")
        Dim Series1 As Series = ChrtMoodChanges.Series("Series1")
        Series1.Name = "Happy"
        ChrtMoodChanges.Series(Series1.Name).XValueMember = "Actual_Date"
        ChrtMoodChanges.Series(Series1.Name).YValueMembers = "CountOfMood"
    End Using


    Dim MyQuery2 As String = "SELECT qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood, Count(qry_Response_By_Date_1.Mood) AS CountOfMood FROM qry_Response_By_Date_1 GROUP BY qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood HAVING (((qry_Response_By_Date_1.Mood)='Neutral'));"
    Using cmd = New OleDbCommand(MyQuery2, con)
        Dim MyData2 As New OleDbDataAdapter(MyQuery2, con)
        Dim MyDataSet2 As New DataSet
        MyData2.Fill(MyDataSet2, "Table")
        ChrtMoodChanges.DataSource = MyDataSet2.Tables("Table")
        Dim Series2 As Series = ChrtMoodChanges.Series("Series2")
        Series2.Name = "Neutral"
        ChrtMoodChanges.Series(Series2.Name).XValueMember = "Actual_Date"
        ChrtMoodChanges.Series(Series2.Name).YValueMembers = "CountOfMood"
    End Using


    Dim MyQuery3 As String = "SELECT qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood, Count(qry_Response_By_Date_1.Mood) AS CountOfMood FROM qry_Response_By_Date_1 GROUP BY qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood HAVING (((qry_Response_By_Date_1.Mood)='Sad'));"
    Using cmd = New OleDbCommand(MyQuery3, con)
        Dim MyData3 As New OleDbDataAdapter(MyQuery3, con)
        Dim MyDataSet3 As New DataSet
        MyData3.Fill(MyDataSet3, "Table")
        ChrtMoodChanges.DataSource = MyDataSet3.Tables("Table")
        Dim Series3 As Series = ChrtMoodChanges.Series("Series3")
        Series3.Name = "Sad"
        ChrtMoodChanges.Series(Series3.Name).XValueMember = "Actual_Date"
        ChrtMoodChanges.Series(Series3.Name).YValueMembers = "CountOfMood"
    End Using


    Dim MyQuery4 As String = "SELECT qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood, Count(qry_Response_By_Date_1.Mood) AS CountOfMood FROM qry_Response_By_Date_1 GROUP BY qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood HAVING (((qry_Response_By_Date_1.Mood)='Angry'));"
    Using cmd = New OleDbCommand(MyQuery4, con)
        Dim MyData4 As New OleDbDataAdapter(MyQuery4, con)
        Dim MyDataSet4 As New DataSet
        MyData4.Fill(MyDataSet4, "Table")
        ChrtMoodChanges.DataSource = MyDataSet4.Tables("Table")
        Dim Series4 As Series = ChrtMoodChanges.Series("Series4")
        Series4.Name = "Angry"
        ChrtMoodChanges.Series(Series4.Name).XValueMember = "Actual_Date"
        ChrtMoodChanges.Series(Series4.Name).YValueMembers = "CountOfMood"
    End Using
End Using

P.S. the SQL returns the correct data for each... it's just getting it to store the correct data in the charts is my problem.

Answer 1

Everytime you set the ChrtMoodChanges.DataSource you are overwriting the datasource for the chart, thats why you end up with only the last dataset.

I would suggest you merge your datatables before you start binding them to the chart then reference each series to a column in your datasource/datatable

ChrtMoodChanges.Series(Series1.Name).XValueMember = "Actual_Date"
ChrtMoodChanges.Series(Series1.Name).YValueMember = "CountOfMood_Col1"
....
....
ChrtMoodChanges.Series(Series4.Name).XValueMember = "Actual_Date"
ChrtMoodChanges.Series(Series4.Name).YValueMember = "CountOfMood_Col4"

So as a suggestion to cleaning up the code, I would do the below

1. Get the 4 datatables
2. Merge the datatables with "Actual_Date" as the primary key
3. add each column to the chart as a new series

Merging Example: Merge columns of different types from datatables into one larger datatable

Hope this helps