How do I fix this code ?

Question

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 VB.net I have formated in 'yyyy-MM', this is my Code for annotation

VB

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)
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("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
Next
Return data
end sub
End Function

What I have tried:

I tried to modify this part of the code :

SQL

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

C#

command.Parameters.AddWithValue("@Day", day.Trim() & "-" & DateTime.Now.Day.ToString())

but it gives me error in this line

C#

sda.Fill(dt)

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

Thank you for your help,
Best Regards
E

Posted 21-Sep-20 3:45am

Member 13410460

Updated 21-Sep-20 5:58am

Sandeep Mewara

v2

Add a Solution

Comments

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 Vb.net 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2020-09-21T05:58:00

Quote:

VB.NET

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:

VB.NET

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:

VB.NET

Dim dayNumber As Integer
If Not Integer.TryParse(day, dayNumber) Then
    ' TODO: Display an error to the user
    Return
End If

Dim today As DateTime = DateTime.Today
Dim dayValue As New DateTime(today.Year, today.Month, dayNumber)
command.Parameters.AddWithValue("@Day", dayValue)

How do I fix this code ?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us