Hi Everyone,
I have the vb.net chart that I see the last 12 months of expenses.
I tried this code but won't return anything. Thank you for your patience and help.
This is the code that I use to see my data in the chart :
Private Sub Chart2_MouseMove(sender As Object, e As MouseEventArgs) Handles Chart2.MouseMove
Dim pos = e.Location
If prevPosition.HasValue AndAlso pos = prevPosition.Value Then Return
tooltip.RemoveAll()
prevPosition = pos
Dim results = Chart2.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
For Each result In results
If result.ChartElementType = ChartElementType.DataPoint Then
Dim Month As String = result.Series.Points(result.PointIndex).XValue
tooltip.BackColor = Color.LightGray
tooltip.ToolTipTitle = "Data Information"
tooltip.Show(GetDetail(Month), Chart1, pos.X, pos.Y - 15)
End If
Next
End Sub
Private Function GetData2() As DataTable
Dim dtChartData As New DataTable()
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("db").ConnectionString)
Using cmd As New SqlCommand("Chart_last12months", 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 Function GetDetail(ByVal Month As String) As String
Dim data As String = ""
Dim constr As String = "Data Source=ELVIS\SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
Dim query As String = "SELECT Sum(Price) AS Total,
DATEPART(MM, Date) AS [year],
Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) AS Lesh
FROM dbo.Expenses
WHERE Date BETWEEN DATEADD(MM, -12, GETDATE()) AND GETDATE()
GROUP BY DATEPART(MM, Date)"
Dim dt As DataTable = New DataTable()
Using con As SqlConnection = New SqlConnection(constr)
Using command As New SqlCommand(query, con)
command.CommandType = CommandType.Text
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 & " - " & " | " & " - " & " | " & department & Environment.NewLine
Next
Return data
End Function
Private Sub LoadYearData1()
Chart2.DataSource = GetData2()
Chart2.Series("Chart2").Points.Clear()
Chart2.Series("Chart2").XValueMember = "Year"
Chart2.Series("Chart2").YValueMembers = "Total"
'Chart1.Series("Chart1").Color = Color.FromArgb(152, 251, 152)
Chart2.Series("Chart2").IsValueShownAsLabel = True
Chart2.Series("Chart2").BorderWidth = 2
Chart2.Series("Chart2").ChartType = SeriesChartType.Spline
' Chart1.Series("Chart1").Points.DataBindXY(x, y)
Chart2.Series("Chart2").LabelForeColor = Color.NavajoWhite
Chart2.Series("Chart2").EmptyPointStyle.Color = Color.Red
Chart2.Series("Chart2").EmptyPointStyle.AxisLabel = "Empty"
Chart2.Series("Chart2").EmptyPointStyle.IsValueShownAsLabel = False
Chart2.Legends(0).Enabled = False
Dim MyData As Single() = New Single(Chart2.Series.Count - 1) {}
For i = 0 To Chart2.Series.Count - 1
MyData(i) = Convert.ToSingle(5 * Math.Sin(i / CDbl(2)))
Dim withBlock = Chart2.Series(i)
withBlock.BorderWidth = 2
withBlock.MarkerStyle = MarkerStyle.Circle
withBlock.MarkerSize = 8
Chart2.Annotations.Add(New CalloutAnnotation())
Next
End Sub