VB.NET/Access/Chart - create a windows forms chart/bar graph based on data from access query

Question

I have never created a chart before and don't understand how to do it. I want to create a bar graph that shows the competitor name and how many wins each competitor has. Access.DBDT is my data source.

I tried this but get the following error on "Chart2.DataSource = Access.DBDT.TableName("CompetitionDate")"

"An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll Additional information: Conversion from string "CompetitionDate" to type 'Integer' is not valid."

Here is the code...

Private Sub Charts_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Access.ExecQuery("SELECT CompetitionDate.FirstName, CompetitionDate.LastName, CompetitionDate.Wins FROM CompetitionDate ORDER BY Wins")


    Dim ChartArea2 As ChartArea = New ChartArea()
    Dim Legend2 As Legend = New Legend()
    Dim Wins2 As Series = New Series()
    Dim Chart2 = New Chart()
    Me.Controls.Add(Chart2)

    ChartArea2.Name = "ChartArea2"
    Chart1.ChartAreas.Add(ChartArea2)
    Legend2.Name = "Legend2"
    Chart2.Legends.Add(Legend2)
    Chart2.Location = New System.Drawing.Point(13, 13)
    Chart2.Name = "Chart2"
    Wins2.ChartArea = "ChartArea2"
    Wins2.Legend = "Legend2"
    Wins2.Name = "Wins"
    Chart2.Series.Add(Wins2)
    Chart2.Size = New System.Drawing.Size(800, 400)
    Chart2.TabIndex = 0
    Chart2.Text = "Total Wins"

    Chart2.Series("Wins").XValueMember = "FirstName"
    Chart2.Series("Wins").YValueMembers = "Wins"

    Chart2.DataSource = Access.DBDT.TableName("CompetitionDate")
End Sub

Answer 1

TableName is a string property on the DataTable Class. The default property of the String Class is the Chars Property. This signature is as follows:

Default Public ReadOnly Property Chars(index As Integer) As Char

You are attempting to pass "CompetitionDate" for the index argument.

Possibly the statement should be:

Chart2.DataSource = Access.DBDT

Answer 2

Thanks for the help. I was able to come up with a solution at home last night. For simplicity I created another table for BJJ Wins in my access database and populated it with test data. Then I created a form for Wins/Losses and added a chart to it. I set it up through properties with Wins Series and Losses Series. Then used this code to do the rest of the work.

Private Sub BJJ_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Using cn As New OleDb.OleDbConnection _

("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=SchoolManagement.accdb")

        ds = New DataSet()


        'provider to be used when working with access database
        cn.Open()

        adapt = New OleDbDataAdapter("SELECT FLName,
                             SUM(Wins) AS Wins, SUM(Losses) AS Losses
                      FROM BJJ
                      GROUP BY FLName,Wins;", cn)

        adapt.Fill(ds)

        BJJRecord.DataSource = ds
        BJJRecord.Series("Wins").XValueMember = "FLName"
        BJJRecord.Series("Wins").YValueMembers = "Wins"
        BJJRecord.Series("Losses").YValueMembers = "Losses"
        BJJRecord.Titles.Add("Total Wins")
    End Using
End Sub

Which produced this graph.