[Solved] Windows Form Chart data annotation not working after ComboBox selection in VB.Net

elvisidrizi1
 
on Jan 20, 2021 11:48 PM
1125 Views

Hello everyone,

Thanks to this forum I have achieved some progress on my application, though, I am stuck in one part where I can’t seem to fix it up.

I have this chart that I change it whit my Combo box options, i.e. (grocery, department, electronics...) and I have created annotations for those charts, the chart where I select the option "All" from my combo box works fine but when I select any other option in my combo box then I have no results (No error as well), it's just empty. 

This is the full code for what I have described so far:

    Private Sub DataChartExpensesbyDay1()
        Dim dt As DataTable = GetData("[Chart_ExpensesbyDayTotal2]")
        Dim Department As List(Of String) = (From p In dt.AsEnumerable()
                                             Select p.Field(Of String)("Department")).Distinct().ToList()

        Dim years As List(Of Int32) = (From p In dt.AsEnumerable()
                                       Order By p.Field(Of Int32)("Day")
                                       Select p.Field(Of Int32)("Day")).Distinct().ToList()

        If Chartday.Series.Count() = 1 Then
            Chartday.Series.Remove(Chartday.Series(0))
        End If
        Chartday.Series.Clear()


        For Each Departments As String In Department
            'Dim x As List(Of Decimal) = New List(Of Decimal)()
            'Dim y As List(Of Decimal) = New List(Of Decimal)()

            Dim x As List(Of String) = New List(Of String)()
            Dim y As List(Of Decimal) = New List(Of Decimal)()
            For Each year As String In years
                x.Add(year)
                y.Add((From p In dt.AsEnumerable()
                       Where p.Field(Of String)("Department") = Departments And p.Field(Of Int32)("Day") = year
                       Order By p.Field(Of Int32)("Day")
                       Select p.Field(Of Decimal)("Total")).FirstOrDefault())
            Next
            Chartday.Series.Add(New Series(Departments))
            Chartday.Series(Departments).IsValueShownAsLabel = True
            Chartday.Series(Departments).BorderWidth = 2
            Chartday.Series(Departments).ChartType = SeriesChartType.Spline
            Chartday.Series(Departments).Points.DataBindXY(x, y)
            Chartday.Series(Departments).LabelForeColor = Color.NavajoWhite
            Chartday.Series(Departments).EmptyPointStyle.Color = Color.Red
            Chartday.Series(Departments).EmptyPointStyle.AxisLabel = "Empty"
            Chartday.Series(Departments).EmptyPointStyle.IsValueShownAsLabel = False
            'Chart1.Series(department).Color = Color.FromArgb(rnd.Next(253), rnd.Next(256), rnd.Next(256))
        Next

        Chartday.Legends(0).Enabled = True
        Dim MyData As Single() = New Single(Chartday.Series.Count - 1) {}
        For i = 0 To Chartday.Series.Count - 1
            MyData(i) = Convert.ToSingle(5 * Math.Sin(i / CDbl(2)))
            Dim withBlock = Chartday.Series(i)
            withBlock.BorderWidth = 2
            withBlock.MarkerStyle = MarkerStyle.Circle
            withBlock.MarkerSize = 8
            Chartday.Annotations.Add(New CalloutAnnotation())
        Next

    End Sub

    Private Function GetData2(ByVal query As String) As DataTable
        Dim dt As DataTable = New DataTable()
        Dim constr As String = "
        Using con As SqlConnection = New SqlConnection(constr)
            Using command As New SqlCommand("Chart_ExpensesbyDayTotal2", con)
                command.CommandType = CommandType.StoredProcedure
                If Not String.IsNullOrEmpty(DateTimePickerM.Text) Then
                    command.Parameters.AddWithValue("@DateDe", Convert.ToDateTime(DateTimePickerM.Text).ToString("yyyy-dd")) '"2020-01-01"
                Else
                    command.Parameters.AddWithValue("@DateDe", DBNull.Value)
                End If

                command.Parameters.AddWithValue("@Month", "1")

                If Not ComboBoxDepartment.Text = "All" Then
                    command.Parameters.AddWithValue("@Department", ComboBoxDepartment.Text)
                Else
                    command.Parameters.AddWithValue("@Department", DBNull.Value)
                End If

                Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                    sda.Fill(dt)
                End Using
            End Using
        End Using
        Return dt
    End Function

    Private prevPosition As Point? = Nothing
    Private tooltip As ToolTip = New ToolTip()

    '--------The right Version---------
    Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chartday.MouseMove

        Dim pos = e.Location
        If prevPosition.HasValue AndAlso pos = prevPosition.Value Then Return
        tooltip.RemoveAll()
        prevPosition = pos
        Dim results = Chartday.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
        For Each result In results
            If result.ChartElementType = ChartElementType.DataPoint Then
                Dim department As String = result.Series.Name
                Dim day As String = result.Series.Points(result.PointIndex).AxisLabel
                Dim total As Double = result.Series.Points(result.PointIndex).YValues(0)
                Dim yVal = result.ChartArea.AxisY.PixelPositionToValue(pos.Y)
                tooltip.BackColor = Color.LightGray
                tooltip.ToolTipTitle = "Data Information"
                tooltip.Show(GetDetails(day, department), Chartday, 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 day As String, ByVal department As String) As String
        Dim data As String = ""
        Dim constr As String = 
        Dim query As String = ""
        Dim dt As DataTable = New DataTable()
        Using con As SqlConnection = New SqlConnection(constr)
            Using command As New SqlCommand()
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Day", day.Trim())
                command.Parameters.AddWithValue("@Month", (DateTimePickerM.Text.Trim()))
                'command.Parameters.AddWithValue("@Year", DateTime.Now.Year)
                query += "SELECT Date,Description,Price,Quantity,Department FROM [dbo].[Expenses] "
                query += "WHERE DATEPART(DAY,Date) = @Day AND CONVERT(char(7), date, 120) = @Month AND LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Department)) "
                'query += "AND DATEPART(Year,Date) = @Year"
                If ComboBoxDepartment.Text.Trim() = "All" Then
                    command.Parameters.AddWithValue("@Department", department.Trim())
                Else
                    command.Parameters.AddWithValue("@Department", ComboBoxDepartment.Text.Trim())
                End If
                If Not query = "" Then
                    command.CommandText = query
                    command.Connection = con
                    Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                        sda.Fill(dt)
                    End Using
                End If
            End Using
        End Using

        For Each row As DataRow In dt.Rows
            Dim [date] As String = Convert.ToDateTime(row("Date")).ToString("dd/MM/yyyy")
            Dim Username1 As String = row("Department")
            Dim description As String = row("Description")
            Dim price As String = If(row("Price").ToString = "", 0, row("Price").ToString)
            Dim quantity As String = If(row("Quantity").ToString = "", 0, row("Quantity").ToString)
            data = data & [date] & " - " & Username1.Trim() & " - " & description & " - " & price & " - " & quantity & Environment.NewLine
        Next

        Return data
    End Function

and this is the SQL part :

ALTER Procedure [dbo].[Chart_ExpensesbyDayTotal2]
( 
    @DateDe VARCHAR(50) = NULL,
    @Month VARCHAR(50) = NULL,
    @Department VARCHAR(50) = NULL
) 
as 
Begin 
    SELECT SUM(Price) AS 'Total' ,
        Sum(CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as NOA,
        CONVERT(char(7), Date, 120) as 'Month', 
        Department as 'Department',
        DATEPART(DAY, Date) AS 'Day' 
    FROM [dbo].[Expenses] 
    WHERE (CONVERT(char(7), date, 120) = @DateDe OR @DateDe IS NULL)
    AND (DATEPART(Month,Date) = @Month OR @Month IS NULL)
    AND (LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Department)) OR @Department IS NULL)
    GROUP BY Description,CONVERT(char(7),date,120),Department,DATEPART(DAY,Date) 
    ORDER BY SUM(Price) DESC 
End  

Any helps is much appreciated.

Thank you

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jan 22, 2021 02:05 AM
on Feb 06, 2021 07:02 AM

Hi elvisidrizi1,

Check this example. Now please take its reference and correct your code.

SQL

CREATE Procedure [dbo].[Chart_ExpensesbyDayTotal2]
(
    @DateDe VARCHAR(50) = NULL,
    @Month VARCHAR(50) = NULL,
    @Department VARCHAR(50) = NULL
)
as
Begin
    SELECT SUM(Price) AS 'Total' ,
        Sum(CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as NOA,
        CONVERT(char(7), Date, 120) as 'Month',
        Department as 'Department',
        DATEPART(DAY, Date) AS 'Day'
    FROM [dbo].[Expenses]
    WHERE (CONVERT(char(7), date, 120) = @DateDe OR @DateDe IS NULL)
    AND (DATEPART(Month,Date) = @Month OR @Month IS NULL)
    AND (LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Department)) OR @Department IS NULL)
    GROUP BY Description,CONVERT(char(7),date,120),Department,DATEPART(DAY,Date)
    ORDER BY SUM(Price) DESC
End 

Code

Dim constr As String = "Data Source=.;Initial Catalog=Expencess;UID=sa;PWD=pass@123;"
Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    DataChartExpensesbyDay1()
End Sub

Private Sub btnFilter_Click_1(sender As Object, e As EventArgs) Handles btnFilter.Click
    DataChartExpensesbyDay1()
End Sub

Private Sub DataChartExpensesbyDay1()
    Dim dt As DataTable = GetData("[Chart_ExpensesbyDayTotal2]")
    Dim Department As List(Of String) = (From p In dt.AsEnumerable()
                                            Select p.Field(Of String)("Department")).Distinct().ToList()

    Dim years As List(Of Int32) = (From p In dt.AsEnumerable()
                                    Order By p.Field(Of Int32)("Day")
                                    Select p.Field(Of Int32)("Day")).Distinct().ToList()

    If Chartday.Series.Count() = 1 Then
        Chartday.Series.Remove(Chartday.Series(0))
    End If
    Chartday.Series.Clear()


    For Each Departments As String In Department
        'Dim x As List(Of Decimal) = New List(Of Decimal)()
        'Dim y As List(Of Decimal) = New List(Of Decimal)()

        Dim x As List(Of String) = New List(Of String)()
        Dim y As List(Of Decimal) = New List(Of Decimal)()
        For Each year As String In years
            x.Add(year)
            y.Add((From p In dt.AsEnumerable()
                    Where p.Field(Of String)("Department") = Departments And p.Field(Of Int32)("Day") = year
                    Order By p.Field(Of Int32)("Day")
                    Select p.Field(Of Decimal)("Total")).FirstOrDefault())
        Next
        Chartday.Series.Add(New Series(Departments))
        Chartday.Series(Departments).IsValueShownAsLabel = True
        Chartday.Series(Departments).BorderWidth = 2
        Chartday.Series(Departments).ChartType = SeriesChartType.Spline
        Chartday.Series(Departments).Points.DataBindXY(x, y)
        Chartday.Series(Departments).LabelForeColor = Color.Black
        Chartday.Series(Departments).EmptyPointStyle.Color = Color.Red
        Chartday.Series(Departments).EmptyPointStyle.AxisLabel = "Empty"
        Chartday.Series(Departments).EmptyPointStyle.IsValueShownAsLabel = False
        'Chartday.Series(Departments).SmartLabelStyle.Enabled = False
        Chartday.Series(Departments).SmartLabelStyle.CalloutStyle = LabelCalloutStyle.None
        Chartday.Series(Departments).SmartLabelStyle.CalloutLineAnchorCapStyle = LineAnchorCapStyle.None
        Chartday.Series(Departments).SmartLabelStyle.CalloutLineColor = Color.Transparent
        'Chart1.Series(department).Color = Color.FromArgb(rnd.Next(253), rnd.Next(256), rnd.Next(256))
    Next
    Chartday.Legends(0).Enabled = False
    Dim MyData As Single() = New Single(Chartday.Series.Count - 1) {}
    For i = 0 To Chartday.Series.Count - 1
        MyData(i) = Convert.ToSingle(5 * Math.Sin(i / CDbl(2)))
        Dim withBlock = Chartday.Series(i)
        withBlock.BorderWidth = 2
        withBlock.MarkerStyle = MarkerStyle.Circle
        withBlock.MarkerSize = 8
        Chartday.Annotations.Add(New CalloutAnnotation())
    Next

End Sub

Private prevPosition As Point? = Nothing
Private tooltip As ToolTip = New ToolTip()

'--------The right Version---------
Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chartday.MouseMove

    Dim pos = e.Location
    If prevPosition.HasValue AndAlso pos = prevPosition.Value Then Return
    tooltip.RemoveAll()
    prevPosition = pos
    Dim results = Chartday.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
    For Each result In results
        If result.ChartElementType = ChartElementType.DataPoint Then
            Dim department As String = result.Series.Name
            Dim day As String = result.Series.Points(result.PointIndex).AxisLabel
            Dim total As Double = result.Series.Points(result.PointIndex).YValues(0)
            Dim yVal = result.ChartArea.AxisY.PixelPositionToValue(pos.Y)
            tooltip.BackColor = Color.LightGray
            tooltip.ToolTipTitle = "Data Information"
            tooltip.Show(GetDetails(day, department), Chartday, 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 day As String, ByVal department As String) As String
    Dim data As String = ""
    Dim query As String = ""
    Dim dt As DataTable = New DataTable()
    Using con As SqlConnection = New SqlConnection(constr)
        Using command As New SqlCommand()
            command.CommandType = CommandType.Text
            query += "SELECT Date,Description,Price,Quantity,Department FROM [dbo].[Expenses] "
            query += "WHERE DATEPART(DAY,Date) = @Day "
            query += "AND (DATEPART(Month,Date) = @Month OR @Month IS NULL) "
            query += "AND (LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Department)) OR @Department IS NULL) "
            query += "AND DATEPART(YEAR,Date) = @Year"
            command.Parameters.AddWithValue("@Day", day.Trim())
            command.Parameters.AddWithValue("@Month", Convert.ToDateTime(DateTimePickerM.Text.Trim()).Month)
            command.Parameters.AddWithValue("@Year", Convert.ToDateTime(DateTimePickerM.Text.Trim()).Year)
            If ComboBoxDepartment.Text.Trim() = "All" OrElse ComboBoxDepartment.Text.Trim() = "" Then
                command.Parameters.AddWithValue("@Department", DBNull.Value)
            Else
                command.Parameters.AddWithValue("@Department", ComboBoxDepartment.Text.Trim())
            End If
            If Not query = "" Then
                command.CommandText = query
                command.Connection = con
                Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                    sda.Fill(dt)
                End Using
            End If
        End Using
    End Using

    For Each row As DataRow In dt.Rows
        Dim [date] As String = Convert.ToDateTime(row("Date")).ToString("dd/MM/yyyy")
        Dim Username1 As String = row("Department")
        Dim description As String = row("Description")
        Dim price As String = If(row("Price").ToString = "", 0, row("Price").ToString)
        Dim quantity As String = If(row("Quantity").ToString = "", 0, row("Quantity").ToString)
        data = data & [date] & " - " & Username1.Trim() & " - " & description & " - " & price & " - " & quantity & Environment.NewLine
    Next

    Return data
End Function

Private Function GetData(ByVal query As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As SqlConnection = New SqlConnection(constr)
        Using command As New SqlCommand(query, con)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.AddWithValue("@DateDe", Convert.ToDateTime(DateTimePickerM.Text.Trim()).ToString("yyyy-MM"))
            command.Parameters.AddWithValue("@Month", Convert.ToDateTime(DateTimePickerM.Text.Trim()).Month)
            If ComboBoxDepartment.Text.Trim() = "All" OrElse ComboBoxDepartment.Text.Trim() = "" Then
                command.Parameters.AddWithValue("@Department", DBNull.Value)
            Else
                command.Parameters.AddWithValue("@Department", ComboBoxDepartment.Text.Trim())
            End If
            Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                sda.Fill(dt)
            End Using
        End Using
    End Using
    Return dt
End Function

Screenshot