Hi Everyone,
I have been struggling a while with this code and I can't seem to fix it. I need to show information in annotation form when I move my mouse over the points in my charts.
The SQL code works fine in SQL but in my vb.net format does not work.
this is the SQL code :
SELECT Date,Description,Price,Quantity,Department 'Department' FROM [dbo].[Expenses]
WHERE DATEPART(DAY,Date) = '06' AND DATEPART(Month,Date) = '11' AND LTRIM(RTRIM(Department)) = LTRIM(RTRIM('Grocery'))
which gives me this values :
2020-11-06 00:00:00.000 Delivery Food 14.07 5 Grocery
2020-11-06 00:00:00.000 Grocery 1.95 1 Grocery
This is the code that I have for the data chart :
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_daily1", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
'Counts Year only'
cmd.Parameters.AddWithValue("@DateD", Convert.ToDateTime(DateTimePickerM.Text).ToString("yyyy-MM"))
cmd.Parameters.AddWithValue("@Department", ComboBoxDepartment.Text)
Dim reader As SqlDataReader = cmd.ExecuteReader()
dtChartData.Load(reader)
End Using
End Using
Return dtChartData
End Function
and this is for the annotation :
Private prevPosition As Point? = Nothing
Private tooltip As ToolTip = New ToolTip()
Private Sub Chartday_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 Year 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(Year, 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 Year As String, ByVal department As String) As String
Dim data As String = ""
Dim query As String = "SELECT Date,Description,Price,Quantity,Department 'Department' FROM [dbo].[Expenses] "
query += "WHERE DATEPART(DAY,Date) = @Day AND DATEPART(Month,Date) = @Month AND LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Username))"
Dim dt As DataTable = New DataTable()
Using con As SqlConnection = New SqlConnection("Data Source=ELVIS\SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;")
Using command As New SqlCommand(query, con)
command.CommandType = CommandType.Text
command.Parameters.AddWithValue("@Day", Year.Trim())
command.Parameters.AddWithValue("@Month", DateTime.Now.Month)
command.Parameters.AddWithValue("@UserName", department.Trim())
' command.Parameters.AddWithValue("@UserName", (ComboBoxDepartment.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 [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
with this code, i get information in my chart with specific date and month but not a specific year but all the years included in the dataset. Here is the view :
as you can see in the picture how is shown. Hower this shows information only when i select ALL button but when i select the specific department it wont work at all :
here is a view :
What I want is that I can see annotation when I select the departments and when I select all of them. I know it's all confusing but please let me know if there are any questions. I am able to provide anything that is necessary to fix this issue.
Thank you, best REgards, Elvis Idrizi
