Click here to Skip to main content
14,869,969 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello Everyone, I have this code which runs perfectly fine and there is no error just it shows me the wrong information, in fact, it's not wrong it just shows me more then it should, i have datachart that Shows me the datas in months, for example I see datachart of August but when i sent my mouse to the dot it Shows me Information of September also, my date in SQL is formated in DATETIME and then in I have formated in 'yyyy-MM', this is my Code for annotation

Public Sub (day as String, Username as String) as String 
Dim data As String = ""
Dim constr As String = ";"
Dim query As String = "SELECT Date,Description,Price,Quantity,Username FROM [dbo].[ShareCost] WHERE DATEPART(DAY,Date) = @Day AND LTRIM(RTRIM(Username)) = @UserName"
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("@Day", day.Trim())
command.Parameters.AddWithValue("@UserName", userName.Trim())
Using sda As SqlDataAdapter = New SqlDataAdapter(command)
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("Username")
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 & " - " & description & " - " & price & " - " & quantity & Environment.NewLine
Return data
end sub
End Function

What I have tried:

I tried to modify this part of the code :

"SELECT Date,Description,Price,Quantity,Username FROM [dbo].[ShareCost] WHERE (Date BETWEEN @Day AND DATEADD(MONTH,1,@Day)) AND LTRIM(RTRIM(Username)) = @UserName"

command.Parameters.AddWithValue("@Day", day.Trim() & "-" & DateTime.Now.Day.ToString())
but it gives me error in this line

Conversion failed when converting date and/or time from character string.

Thank you for your help,
Best Regards
Updated 21-Sep-20 5:58am
Richard MacCutchan 21-Sep-20 9:57am
You are converting @Day to a string where it should be a DateTime.
Member 13410460 21-Sep-20 10:00am
I am converting to a string because my date is formated in this way '2020-09'. I dont know if this makes sense.
Richard MacCutchan 21-Sep-20 12:07pm
No it does not make sense. Dates in databases should always be stored as DateTime or Date types. Converting them to strings just makes things complicated (as you have discovered).
Member 13410460 22-Sep-20 1:32am
Thank you for your Reply, in SQL they are stored as DateTime, but when I bring them to Dashboard I convert them into that Format beacuse I Need in my Dashboard to Show me the months of the year , not the days. But now when I want to see all the Information stored in specific day of the year I Kind of Need to get to the days of the month.
Richard MacCutchan 22-Sep-20 3:30am
Well you are still doing it wrong. The only time you need it as a string is when you want to display it (or part of it) for the benefit of the user. All internal manipulation and calculations should be done using DateTime objects.
Member 13410460 22-Sep-20 3:35am
Thank you for your Reply, but in this case how do I use datetime instead of String, Do i just write this : command.Parameters.AddWithValue("@Day", day.Trim() & "-" & DateTime.Now() ?
Richard MacCutchan 22-Sep-20 3:56am
No. Stop using strings where you need DateTime objects. It really will make your life much easier.
Member 13410460 22-Sep-20 3:57am
Thank you for your tip, but in this case how do I fix this issue by changing string to datetime ?
Richard MacCutchan 22-Sep-20 4:08am
Assuming Day = "12", then the expression day.Trim() & "-" & DateTime.Now() will give you: "12-22 Sep 2020", or something similar which makes no sense at all.

If you want to change the day value to some predetermined number then use the properties or methods of the DateTime structure. It is no good trying to guess, programming rules are quite specific, and well documented.
Member 13410460 22-Sep-20 4:11am
Thank you for your Reply.
Michael Hulthin 21-Sep-20 10:18am
What if you pass in year and month to the function (instead of "<year>-<month>") and change the where part to
... DATEPART(YEAR,Date) = @Year AND DATEPART(MONTH,Date) = @Month ...
Member 13410460 21-Sep-20 10:21am
Thank you for your Reply, This would be good if I would have date as normal Format ('dd-MM-yyyy') but in this case I have date like this 'yyyy-MM' since I use for my Charts and different results in the Dashboard.

1 solution

command.Parameters.AddWithValue("@Day", day.Trim() & "-" & DateTime.Now.Day.ToString())
You're passing a string to the query which cannot be interpreted as a valid datetime by SQL. For example, if day is "2", you are passing in "2-21", which is obviously not a valid date.

Pass in date parameters as dates:
command.Parameters.AddWithValue("@Day", DateTime.Today)
It's not clear from your question what date range you are actually trying to view. If it's the current month starting on the specified day, for example:
Dim dayNumber As Integer
If Not Integer.TryParse(day, dayNumber) Then
    ' TODO: Display an error to the user
End If

Dim today As DateTime = DateTime.Today
Dim dayValue As New DateTime(today.Year, today.Month, dayNumber)
command.Parameters.AddWithValue("@Day", dayValue)
Member 13410460 22-Sep-20 1:33am
Thank you for your Reply sir, I will try it today when I go home. It's not for the currenct Month, I have a Datetimepicker text which has values like "yyyy-MM" and I can choose months within the year to Show me datacharts with values.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900