Hi Everyone,
I have this code which supposed to read data from the chart but it does not read anything from the chart information.
The chart looks like this : 
and the code behind it is like this :
Private Function GetData1() As DataTable
Dim dtChartData As New DataTable()
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("db").ConnectionString)
Using cmd As New SqlCommand("Chart_12years", conn) ' Total working hours in the month -- step3
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
dtChartData.Load(reader)
End Using
End Using
Return dtChartData
End Function
Private Sub LoadYearData()
Chart1.DataSource = GetData1()
Chart1.Series("Chart1").Points.Clear()
Chart1.Series("Chart1").XValueMember = "Year"
Chart1.Series("Chart1").YValueMembers = "Total"
'Chart1.Series("Chart1").Color = Color.FromArgb(152, 251, 152)
Chart1.Series("Chart1").IsValueShownAsLabel = True
Chart1.Series("Chart1").BorderWidth = 2
Chart1.Series("Chart1").ChartType = SeriesChartType.Spline
' Chart1.Series("Chart1").Points.DataBindXY(x, y)
Chart1.Series("Chart1").LabelForeColor = Color.NavajoWhite
Chart1.Series("Chart1").EmptyPointStyle.Color = Color.Red
Chart1.Series("Chart1").EmptyPointStyle.AxisLabel = "Empty"
Chart1.Series("Chart1").EmptyPointStyle.IsValueShownAsLabel = False
Chart1.Legends(0).Enabled = False
Dim MyData As Single() = New Single(Chart1.Series.Count - 1) {}
For i = 0 To Chart1.Series.Count - 1
MyData(i) = Convert.ToSingle(5 * Math.Sin(i / CDbl(2)))
Dim withBlock = Chart1.Series(i)
withBlock.BorderWidth = 2
withBlock.MarkerStyle = MarkerStyle.Circle
withBlock.MarkerSize = 8
Chart1.Annotations.Add(New CalloutAnnotation())
Next
End Sub
and the SQL behind it is like this :
Begin
SELECT Sum(Price) As Total,
DATEPART(yyyy, Date) As [year],
Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as Lesh
FROM dbo.Expenses
WHERE Date >= DATEADD(year, -12, GETDATE())
GROUP BY DATEPART(yyyy, Date)
End
and the data annotation looks like this :
Private prevPosition As Point? = Nothing
Private tooltip As ToolTip = New ToolTip()
Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chart1.MouseMove
Dim pos = e.Location
If prevPosition.HasValue AndAlso pos = prevPosition.Value Then Return
tooltip.RemoveAll()
prevPosition = pos
Dim results = Chart1.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
For Each result In results
If result.ChartElementType = ChartElementType.DataPoint Then
Dim year As String = result.Series.Points(result.PointIndex).AxisLabel
tooltip.BackColor = Color.LightGray
tooltip.ToolTipTitle = "Data Information"
tooltip.Show(GetDetails(year), Chart1, pos.X, pos.Y - 15)
End If
Next
End Sub
Private Sub toolTip_Draw(ByVal sender As Object, ByVal e As DrawToolTipEventArgs)
Dim f As Font = New Font("Arial", 8.0F)
Dim b As Brush = New SolidBrush(tooltip.BackColor)
e.Graphics.FillRectangle(b, e.Bounds)
e.DrawBackground()
e.DrawBorder()
e.Graphics.DrawString(e.ToolTipText, f, SystemBrushes.ActiveCaptionText, e.Bounds)
End Sub
Private Function GetDetails(ByVal year As String) As String
Dim data As String = ""
Dim constr As String = "..."
Dim query As String = "SELECT Sum(Price) As Total, DATEPART(yyyy, Date) As [Year], Department FROM dbo.Expenses "
query += " WHERE DATEPART(yyyy, Date) = @Year GROUP BY DATEPART(yyyy, Date), Department"
Dim dt As DataTable = New DataTable()
Using con As SqlConnection = New SqlConnection(constr)
Using command As New SqlCommand(query, con)
command.CommandType = CommandType.Text
command.Parameters.AddWithValue("@Year", year.Trim())
Using sda As SqlDataAdapter = New SqlDataAdapter(command)
sda.Fill(dt)
End Using
End Using
End Using
For Each row As DataRow In dt.Rows
Dim department As String = row("Department").ToString().Trim()
Dim total As String = row("Total").ToString()
data = data & total & " - " & year & " - " & department & Environment.NewLine
Next
Return data
End Function
I did try it different ways but it won't fix it, but when I insert manually year in this line :
tooltip.Show(GetDetails(year), Chart1, pos.X, pos.Y - 15)
instead of year, I insert the year 2020 and then I get all the information needed, otherwise it won't read the information in the vb.net chart.
Thank you
