-
Jul 6th, 2021, 09:14 AM
#1
Thread Starter
Addicted Member
[RESOLVED] Parse date to correct format
Hi
I have the following code to save file info in a data table to a mysql database table via an adapter:
Code:
With cmd2.Parameters
.Add("@Name", MySqlDbType.VarChar, 50, "Name")
.Add("@LastWriteTime", MySqlDbType.DateTime, "LastWriteTime")
End With
My issue is with LastWriteTime. The format in the datatable is dd/MM/yyyy HH :mm:ss (eg. 02/07/2021 14:51:57). I know Mysql accepts date in yyyy-MM-dd format. I obviously have to Parse or TryParse in the above. After a lot of searching, I just cannot figure out how to apply it.
Any help would be much appreciated.
Last edited by dday9; Jul 6th, 2021 at 10:10 AM.
Reason: formatted code
-
Jul 6th, 2021, 09:39 AM
#2
Re: Parse date to correct format
Nooooooooooooooo! There is - at least there should be - no format. Format is ONLY an issue when you have a string containing a representation of a date/time. You should not have any such string, so format should not be an issue at all. Your DataTable should contain DateTime values, not Strings. Your database column should be data type datetime, which your parameter indicates it is. In that case, there is not format at either end so there is no issue with format. If there is an issue with format then that means that you are using strings to store date/time values and THAT is the problem you need to fix. Don't do the wrong thing in the first place and there is no format problem to solve.
-
Jul 6th, 2021, 11:14 AM
#3
Thread Starter
Addicted Member
Re: Parse date to correct format
Well I created a data table :
Code:
Dim dt As DataTable = New DataTable
With dt
.Columns.Add("Name", System.Type.GetType("System.String"))
.Columns.Add("LastWriteTime", System.Type.GetType("System.DateTime"))
.Columns.Add("LastCreationTime", System.Type.GetType("System.DateTime"))
End With
So I did set the datatable column to datetime.
I then read data into the table with the following :
Code:
For Each file As IO.FileInfo In New IO.DirectoryInfo("D:\Wingerdbou\LutzvilleProgram\InvoerDataFiles\EZY Wine\Zipped").GetFiles
Dim dr As DataRow = dt.NewRow
dr(0) = file.Name
dr(1) = file.LastWriteTime
dr(2) = file.CreationTime
dt.Rows.Add(dr)
Next
My assumtion that the fields LastWriteTime and LastCreationTime is of type datetime is then clearly wrong.
Last edited by GideonE; Jul 6th, 2021 at 11:24 AM.
-
Jul 6th, 2021, 11:44 AM
#4
Re: Parse date to correct format
My assumtion that the fields LastWriteTime and LastCreationTime is of type datetime is then clearly wrong.
First it shouldn't be an assumption. It should be checked against the documentation ... and yes, they are a dateTime...
Which then leads to second, if your assumption that they are datetime is wrong, what is happening or not happening? Because they are datetime according to the documentation (which was easy enough to get to by typing ".net io.fileinfo" in to the search and pulling up the Microsoft Docs result at the top) ... then it should be fine. If it's not, then something else is wrong. But unless you tell us what that is, we can only guess... and you won't like the guesses we come up with or how to solve those problems.
-tg
-
Jul 6th, 2021, 12:44 PM
#5
Thread Starter
Addicted Member
Re: Parse date to correct format
Thank you for replying techgnome
I get the following error : System.InvalidCastException: 'Conversion from string "LastWriteTime" to type 'Integer' is not valid.'
at the following line;
Code:
.Add("@LastWriteTime", MySqlDbType.DateTime, "LastWriteTime")
If I insert a break in my code and inspect the data table with the magnifying glass the data table seems fine with the data tablevisualizer.
I must be missing something when adding the the command parameter?
Regards
Last edited by GideonE; Jul 6th, 2021 at 01:02 PM.
-
Jul 6th, 2021, 01:24 PM
#6
Re: Parse date to correct format
I wondered about that... The add isn't doing what you think it is doing... the parmeters for the .Add is 1) parameter name, 2) Parameter type 3) Parameter Size ... at least for the overlaod version you're using... so it's trying to use the literal value "LastWriteTime" as your parametter size... and as you're finding out, "LastWriteTime" isn't a number... but a string. It is NOT, as you're probably expecting, using the LastWriteTime column of your datatable. Why would it? The datatable is the datatable... and the command is the command... They are two separate objects that don't know a thing about each other. It's like opening a cookbook expecting the first recipe to be about tomato soup because that's what's on your TV.
What you probably want to do is create the command, add the parameters - NO VALUES... and then loop through the rows of the datatable, set the values of the parameters to the values of the columns in the datatable, then execute the command. Wash, rinse, repeat.
-tg
edit thanks to wes for the correction on the thrid param
Last edited by techgnome; Jul 6th, 2021 at 01:33 PM.
-
Jul 6th, 2021, 01:27 PM
#7
Re: Parse date to correct format
Originally Posted by GideonE
Thank you for replying techgnome
I get the following error : System.InvalidCastException: 'Conversion from string "LastWriteTime" to type 'Integer' is not valid.'
at the following line;
Code:
.Add("@LastWriteTime", MySqlDbType.DateTime, "LastWriteTime")
If I insert a break in my code and inspect the data table with the magnifying glass the data table seems fine with the data tablevisualizer.
I must be missing something when adding the the command parameter?
Regards
The third value in the "Add" method is "Size" and it expects an integer. You don't need that, try
Code:
.Add("@LastWriteTime", MySqlDbType.DateTime)
https://docs.microsoft.com/en-us/dot...t-plat-ext-5.0
-
Jul 6th, 2021, 08:28 PM
#8
Re: Parse date to correct format
Now that we have more information, the issue here is obvious:
vb.net Code:
With cmd2.Parameters
.Add("@Name", MySqlDbType.VarChar, 50, "Name")
.Add("@LastWriteTime", MySqlDbType.DateTime, "LastWriteTime")
End With
You can't just arbitrarily choose which parameters to use when calling a method. You MUST use the parameters that that method declares. If you are going to specify a source column for data when adding a parameter then you MUST specify a size. You're doing it correctly the first time but you have arbitratily ommitted the size the second time. If the data type doesn't need a size then just use zero:
vb.net Code:
With cmd2.Parameters
.Add("@Name", MySqlDbType.VarChar, 50, "Name")
.Add("@LastWriteTime", MySqlDbType.DateTime, 0, "LastWriteTime")
End With
-
Jul 6th, 2021, 11:19 PM
#9
Thread Starter
Addicted Member
Re: Parse date to correct format
Thank you all.
Lessons learnt :
1. When asking questions on the forum be specific about the error and include an error message
2. When storing a date/datetime value in MySQL formatting is unnecessary. MySQL will store it as a date/datetime - albeit in a different format
3. Read the Microsoft Doc with intent
4. I have much to learn
Ps. techgnome,, love the cookbook analogy. wes4dbt, thank you for the link (I will study it closely). jmchilhinney, your comment (If the data type doesn't need a size then just use zero) and date formatting lesson has had big impact.
Regards
-
Jul 6th, 2021, 11:26 PM
#10
Re: Parse date to correct format
Originally Posted by GideonE
When storing a date/datetime value in MySQL formatting is unnecessary. MySQL will store it as a date/datetime - albeit in a different format
Storing dates/times anywhere doesn't require formatting, if stored as binary date/time values. Most common databases have at least one binary date/time data type and many have several. SQLite is a notable exception but, even then, I believe that the ADO.NET provider can do the conversion for you. Binary date/time values are generally just numbers, often representing an offset from a specific point in time. If there is any translation to be done between your app and the database, the ADO.NET provider will handle it. You only need to consider format when storing dates/times as text, which you should NEVER do unless necessary.
Originally Posted by GideonE
jmchilhinney, your comment (If the data type doesn't need a size then just use zero) and date formatting lesson has had big impact.
You can use the actual size in bytes of the data type if you like but I prefer to just use zero rather than try to remember those actual sizes. In fact, I think that you can use any value you like there as, if it's not a variable-sized data type, e.g. varchar and varbinary, I think that the size you specify is simply ignored. Some value is required by the .NET method though, so zero seems like an appropriate dummy. Maybe it would actually be most appropriate to use Nothing, which equates to zero for numeric data types but is visibly distinct when reading the code.
-
Jul 7th, 2021, 01:27 AM
#11
Thread Starter
Addicted Member
Re: Parse date to correct format
jmchilhinney thank you for clearing that up.
-
Jul 7th, 2021, 06:47 AM
#12
Re: Parse date to correct format
Originally Posted by GideonE
Thank you all.
Lessons learnt :
1. When asking questions on the forum be specific about the error and include an error message
2. When storing a date/datetime value in MySQL formatting is unnecessary. MySQL will store it as a date/datetime - albeit in a different format
3. Read the Microsoft Doc with intent
4. I have much to learn
Ps. techgnome,, love the cookbook analogy. wes4dbt, thank you for the link (I will study it closely). jmchilhinney, your comment (If the data type doesn't need a size then just use zero) and date formatting lesson has had big impact.
Regards
In my signature is a link about removing eels from your hovercraft ... It's a thread on how to get effective help around here...
Originally Posted by jmcilhinney
Storing dates/times anywhere doesn't require formatting, if stored as binary date/time values. Most common databases have at least one binary date/time data type and many have several. SQLite is a notable exception but, even then, I believe that the ADO.NET provider can do the conversion for you. Binary date/time values are generally just numbers, often representing an offset from a specific point in time. If there is any translation to be done between your app and the database, the ADO.NET provider will handle it. You only need to consider format when storing dates/times as text, which you should NEVER do unless necessary.
You can use the actual size in bytes of the data type if you like but I prefer to just use zero rather than try to remember those actual sizes. In fact, I think that you can use any value you like there as, if it's not a variable-sized data type, e.g. varchar and varbinary, I think that the size you specify is simply ignored. Some value is required by the .NET method though, so zero seems like an appropriate dummy. Maybe it would actually be most appropriate to use Nothing, which equates to zero for numeric data types but is visibly distinct when reading the code.
And decimal... decimal is the other type that needs a specific size - as well as scale and precision (set separately) - if I remember right.
-tg
-
Jul 7th, 2021, 10:30 AM
#13
Thread Starter
Addicted Member
Re: Parse date to correct format
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|