Specified cast in not valid ?

See more:

VB

SQL

Hello Everyone,

I used a code from Create Multi-Series Line Chart (Graph) in Windows Forms Application using C# and VB.Net[^] for my charts in vb.net and i modified based on my need and i get an error that says " Specified cast is not valid" in my "year" field.

Here is the code :

Dim query As String = "SELECT   SUM(Price) AS 'Total'
        ,CONVERT(char(7), date, 120)  AS 'year'
        ,Department as 'Department'
        FROM     [dbo].[Expenses]
        WHERE    Department IN ('Electronics' ,'Grocery','Other','Vacation','FixCost','Clothes')
        GROUP BY  Rollup (CONVERT(char(7), date, 120)), Department
        HAVING   CONVERT(char(7), date, 120) >= '" & DateTimePickerfrom.Text & "'" _
        & " And Convert(Char(7), Date, 120) <= '" & DateTimePickerto.Text & "'"

       Dim dt As DataTable = GetData(query)
       'Get the DISTINCT Countries.
       Dim countries As List(Of String) = (From p In dt.AsEnumerable()
                                           Select p.Field(Of String)("Department")).Distinct().ToList()

       'Remove the Default Series.
       If Chart1.Series.Count() = 1 Then
           Chart1.Series.Remove(Chart1.Series(0))
       End If

       For Each country As String In countries

           'Get the Year for each Country.
           Dim x As Integer() = (From p In dt.AsEnumerable()
                                 Where p.Field(Of String)("Department") = country
                                 Order By p.Field(Of Integer)("year") '---> Here i get the error
                                 Select p.Field(Of Integer)("year")).ToArray() _
           'Get the Total of Orders for each Country.
           Dim y As Integer() = (From p In dt.AsEnumerable()
                                 Where p.Field(Of String)("Department") = country
                                 Order By p.Field(Of Integer)("year")
                                 Select p.Field(Of Integer)("Total")).ToArray() _

           'Add Series to the Chart.
           Chart1.Series.Add(New Series(country))
           Chart1.Series(country).IsValueShownAsLabel = True
           Chart1.Series(country).BorderWidth = 3
           Chart1.Series(country).ChartType = SeriesChartType.Line
           Chart1.Series(country).Points.DataBindXY(x, y)


       Next
       Chart1.Legends(0).Enabled = True

What I have tried:

I tried a completely different approach by creating for each department a function and then call them in data source but, I just found that the chart only takes one data source.

Thankfully,