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

Last Reply 3 months ago By dharmendr

Posted 3 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)
               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
       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?

This question does not have replies that have been liked.