VB.NET/MS ACCESS Saving today's date produces 2020-02-25 08:28:00.946341400
-
When I save the attendance date from a datetimepicker as a date prior to today's date it records acceptable format but not what I really want. When I select today's date it records a date like this 2020-02-25 08:28:00.946341400. The secondary issue is what I eluded to above. I set the custom format in the form_load to "MM, dd, yyyy" and when I select a date prior to today's date it records the date like this 2020-01-29 08:34:25. Here is the Save Button code
Dim inserted As Integer = 0
For Each row As DataGridViewRow In dgvAttend.Rows
Dim isSelected As Boolean = Convert.ToBoolean(row.Cells("chkbox").Value)
If isSelected Then
Access.AddParam("@DTE", dtPick5.Value)
Access.AddParam("@SID", row.Cells("StudentID").Value)
Access.AddParam("@LN", row.Cells("LastName").Value)
Access.AddParam("@FN", row.Cells("FirstName").Value)Access.ExecQuery("INSERT INTO Attend (TrainDate,StudentID,LastName,FirstName) " & "VALUES (@DTE,@SID,@LN,@FN); ") inserted = inserted + 1 End If Next If inserted > 0 Then MessageBox.Show(String.Format("{0} records inserted.", inserted), "ACJTracker", MessageBoxButtons.OK, MessageBoxIcon.Information) End IfHere is the form load
dtPick5.Format = DateTimePickerFormat.Custom
dtPick5.CustomFormat = "MM, dd, yyyy"
RefreshAttendGrid()Here is the refresh Grid
dtPick5.Format = DateTimePickerFormat.Custom
dtPick5.CustomFormat = "MM, dd, yyyy"' RUN QUERY Access.ExecQuery("SELECT StudentData.StudentID, StudentData.LastName, StudentData.FirstName FROM StudentData ORDER BY StudentID ASC") ' REPORT & ABORT ON ERRORS If NoErrors(True) = False Then Exit Sub dgvAttend.DataSource = Access.DBDTDoes anyone know how I correct these issues?
The issue is how you are displaying the saved value. Setting a date format on a DatePicker just changes the display. The actual value will be stored as a DateTime value, so when you print or display it in the future you will need to set the required format.
-
The issue is how you are displaying the saved value. Setting a date format on a DatePicker just changes the display. The actual value will be stored as a DateTime value, so when you print or display it in the future you will need to set the required format.
It is actually saving it to the table in these formats. How can I get the app to save it in the correct format in the access table? Also, what causes the ..946341400?
-
The issue is how you are displaying the saved value. Setting a date format on a DatePicker just changes the display. The actual value will be stored as a DateTime value, so when you print or display it in the future you will need to set the required format.
Thanks for the answer. I figured it out once I stopped over thinking it. dtPick5.Value.ToString("MM/dd/yyyy" did the trick for both issues.
-
It is actually saving it to the table in these formats. How can I get the app to save it in the correct format in the access table? Also, what causes the ..946341400?
-
Thanks for the answer. I figured it out once I stopped over thinking it. dtPick5.Value.ToString("MM/dd/yyyy" did the trick for both issues.
-
You should never store dates as strings, use Date or DateTime values, and only apply formatting when it is displayed. For example the date 01/02/03 could be 1st February 2003, 2nd January 2003, or even 3rd February 2001, depending on where you live.
If dtPick5.Value.ToString("MM/dd/yyyy" isn't the correct way then can you post an example of how to properly do it?
-
If dtPick5.Value.ToString("MM/dd/yyyy" isn't the correct way then can you post an example of how to properly do it?
-
At the risk of repeating myself again and again: use the Value. That way the date can be formatted for display into any culture.
Heaven forbid you repeat yourself or provide a code example.... dickhead. It may be wrong but it works the way I want so until I see an example I am going to keep it this way and move on. I will worry about figuring out what exactly you are talking about later.
-
Heaven forbid you repeat yourself or provide a code example.... dickhead. It may be wrong but it works the way I want so until I see an example I am going to keep it this way and move on. I will worry about figuring out what exactly you are talking about later.
Member 11866893 wrote:
Heaven forbid you repeat yourself or provide a code example....
The explanation is spot on, but to expand with code;
dim someDateString as String = dtPick5.Value.ToString(..
This creates a text, with the date formatted in some culture.
dim someDate as DateTime = dtPick5.Value
Here the value is taken as is, without formatting. It is stored as a decimal value, with "days" in the integer part from a given offset, and the decimal part representing time. It is a format-independant version, and that's how it should be stored. Internally, your application should use DateTime values, and only formatting them as strings when displaying. Saving the value in DateTime type is important; if you use a localized and formatted version tht is actually a string, the computer will have to convert that to a real DateTime for each operation.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Heaven forbid you repeat yourself or provide a code example.... dickhead. It may be wrong but it works the way I want so until I see an example I am going to keep it this way and move on. I will worry about figuring out what exactly you are talking about later.
-
Heaven forbid you repeat yourself or provide a code example.... dickhead. It may be wrong but it works the way I want so until I see an example I am going to keep it this way and move on. I will worry about figuring out what exactly you are talking about later.
-
When I save the attendance date from a datetimepicker as a date prior to today's date it records acceptable format but not what I really want. When I select today's date it records a date like this 2020-02-25 08:28:00.946341400. The secondary issue is what I eluded to above. I set the custom format in the form_load to "MM, dd, yyyy" and when I select a date prior to today's date it records the date like this 2020-01-29 08:34:25. Here is the Save Button code
Dim inserted As Integer = 0
For Each row As DataGridViewRow In dgvAttend.Rows
Dim isSelected As Boolean = Convert.ToBoolean(row.Cells("chkbox").Value)
If isSelected Then
Access.AddParam("@DTE", dtPick5.Value)
Access.AddParam("@SID", row.Cells("StudentID").Value)
Access.AddParam("@LN", row.Cells("LastName").Value)
Access.AddParam("@FN", row.Cells("FirstName").Value)Access.ExecQuery("INSERT INTO Attend (TrainDate,StudentID,LastName,FirstName) " & "VALUES (@DTE,@SID,@LN,@FN); ") inserted = inserted + 1 End If Next If inserted > 0 Then MessageBox.Show(String.Format("{0} records inserted.", inserted), "ACJTracker", MessageBoxButtons.OK, MessageBoxIcon.Information) End IfHere is the form load
dtPick5.Format = DateTimePickerFormat.Custom
dtPick5.CustomFormat = "MM, dd, yyyy"
RefreshAttendGrid()Here is the refresh Grid
dtPick5.Format = DateTimePickerFormat.Custom
dtPick5.CustomFormat = "MM, dd, yyyy"' RUN QUERY Access.ExecQuery("SELECT StudentData.StudentID, StudentData.LastName, StudentData.FirstName FROM StudentData ORDER BY StudentID ASC") ' REPORT & ABORT ON ERRORS If NoErrors(True) = False Then Exit Sub dgvAttend.DataSource = Access.DBDTDoes anyone know how I correct these issues?
Weird since examples seem to work with a single comma. You could try to escape extra characters (i.e., commas):
dtPick5.CustomFormat = "MM',' dd',' yyyy"
or keep using a more common form.
"Five fruits and vegetables a day? What a joke! Personally, after the third watermelon, I'm full."
-
Ah yes, the lazy person's fallback position when you don't get someone else to do your work: abuse.
-
-
When I save the attendance date from a datetimepicker as a date prior to today's date it records acceptable format but not what I really want. When I select today's date it records a date like this 2020-02-25 08:28:00.946341400. The secondary issue is what I eluded to above. I set the custom format in the form_load to "MM, dd, yyyy" and when I select a date prior to today's date it records the date like this 2020-01-29 08:34:25. Here is the Save Button code
Dim inserted As Integer = 0
For Each row As DataGridViewRow In dgvAttend.Rows
Dim isSelected As Boolean = Convert.ToBoolean(row.Cells("chkbox").Value)
If isSelected Then
Access.AddParam("@DTE", dtPick5.Value)
Access.AddParam("@SID", row.Cells("StudentID").Value)
Access.AddParam("@LN", row.Cells("LastName").Value)
Access.AddParam("@FN", row.Cells("FirstName").Value)Access.ExecQuery("INSERT INTO Attend (TrainDate,StudentID,LastName,FirstName) " & "VALUES (@DTE,@SID,@LN,@FN); ") inserted = inserted + 1 End If Next If inserted > 0 Then MessageBox.Show(String.Format("{0} records inserted.", inserted), "ACJTracker", MessageBoxButtons.OK, MessageBoxIcon.Information) End IfHere is the form load
dtPick5.Format = DateTimePickerFormat.Custom
dtPick5.CustomFormat = "MM, dd, yyyy"
RefreshAttendGrid()Here is the refresh Grid
dtPick5.Format = DateTimePickerFormat.Custom
dtPick5.CustomFormat = "MM, dd, yyyy"' RUN QUERY Access.ExecQuery("SELECT StudentData.StudentID, StudentData.LastName, StudentData.FirstName FROM StudentData ORDER BY StudentID ASC") ' REPORT & ABORT ON ERRORS If NoErrors(True) = False Then Exit Sub dgvAttend.DataSource = Access.DBDTDoes anyone know how I correct these issues?
I think the issue you may be facing is that you are storing the data as datetime which naturally includes the time component. If you do not need the time component then you should store it as a date data type (not sure if Access has such a data type). If the is a requirement for the time component then you need to format every instance of the data as it is presented and you need to cater for the time component in all your queries ie you can no longer query for a date you will need to query for the range of datetime records between midnight and midnight the date after.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
Eddy Vluggen wrote:
Don't take it as a personal insult.
I don't; my shoulders are broad enough. But I do take exception to people being abusive on this site.
Richard MacCutchan wrote:
But I do take exception to people being abusive on this site.
Good for me I never did that :rolleyes: It is one of umpteen coders frustrated at how dates don't work like they imagine. Not something unique nor new.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Richard MacCutchan wrote:
But I do take exception to people being abusive on this site.
Good for me I never did that :rolleyes: It is one of umpteen coders frustrated at how dates don't work like they imagine. Not something unique nor new.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Eddy Vluggen wrote:
Don't take it as a personal insult.
I don't; my shoulders are broad enough. But I do take exception to people being abusive on this site.
Richard MacCutchan wrote:
But I do take exception to people being abusive on this site.
Why is that, Thou sodden-witted lord?
Social Media - A platform that makes it easier for the crazies to find each other. Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.