Display data from database in tooltip on chart mouse move using VB.Net in Windows Application

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Hello everyone,

I have this chart that I want to fix it up, here is the issue :

This is the chart how it looks,

 this is the code behind it :

    Private Function GetDetails1(ByVal Month As String) As String
       Dim data As String = ""
       Dim constr As String = ""
       Dim query As String = "SELECT DISTINCT SUM(Price) AS Total,Department FROM [dbo].[Expenses] "
       query += "WHERE DATEPART(Month,Date) = @Month "
       query += "AND DATEPART(Year,Date) = @Year "
       query += "GROUP BY 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("@Month", Month)
               command.Parameters.AddWithValue("@Year", DateTime.Now.Year)
               Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                   sda.Fill(dt)
               End Using
           End Using
       End Using
 
       If dt.Rows.Count > 0 Then
           Dim distinctValues As DataTable = New DataView(dt).ToTable(True, "Total", "Department")
           For Each row As DataRow In distinctValues.Rows
               Dim department As String = row("Department")
               Dim total As String = row("Total").ToString()
               data += data & " - " & " | " & total & " - " & " | " & " - " & " | " & department & Environment.NewLine
           Next
       End If
       Return data
 
   End Function

and this is the SQL  

SELECT DISTINCT SUM(Price) AS Total,Department FROM [dbo].[Expenses]
WHERE CONVERT(char(7), date, 120) = '2020-12'
GROUP BY Department

where the "Month" is now with the year together so I can reduce the code and maybe will be more precise in the results. Just now I have trouble fixing the current code.

what parameter to input now instead of the MONTH?

You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago

Hi elvisidrizi1,

Refer below sample code.

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms.DataVisualization.Charting

Public Class Form1
    Dim constr As String = "Data Source=./SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        LoadYearData()
    End Sub

    Private Function GetData1() As DataTable
        Dim dtChartData As New DataTable()
        Using conn As New SqlConnection(constr)
            Using cmd As New SqlCommand("SELECT Sum(Price) As Total,
                                        CONVERT(char(7), date, 120) [year],
                                        Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as Lesh        
                                        FROM dbo.Expenses
                                        WHERE Date BETWEEN DATEADD(MM, -12, GETDATE()) AND GETDATE()
                                        GROUP BY CONVERT(char(7), date, 120)", conn)
                cmd.CommandType = CommandType.Text

                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").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

    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 yearMonth As String = result.Series.Points(result.PointIndex).AxisLabel
                tooltip.BackColor = Color.LightGray
                tooltip.ToolTipTitle = "Data Information"
                tooltip.Show(GetDetails(yearMonth), 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 yearMonth As String) As String
        Dim data As String = ""
        Dim query As String = "SELECT DISTINCT SUM(Price) AS Total,Department FROM [dbo].[Expenses] "
        query += " WHERE CONVERT(char(7), date, 120) = @Month "
        query += " GROUP BY 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("@Month", yearMonth.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 += " - " & " | " & total & " - " & " | " & " - " & " | " & department & Environment.NewLine
        Next
        Return data
    End Function
End Class

Screenshot