I'm getting into real difficulty when trying to store and retreive dates into a MySQL database and my web application.
I have a form that allows users to select a start and stop date for a message to be displayed, the dates are selected using the Ajax calendar extender and the value placed into a textbox. Then when I 'save' the data it inserts the record into the database
- I have set the calendar extender to format the date as yyyy-MM-dd which is the MySQL date format and it inserts the record no problem. The problem comes when I want to update an existing record, the form fills the textboxes in the MM-dd-YYYY format, and
if I 'Update' the record it says:
Unable to convert MySQL date/time value to System.DateTime
and when I check the database, the date values are 0000-00-00.
Any help or suggestions as to how best to deal with this problem would be greatly appreciated.
Dim sql As String = "UPDATE Table SET DateTimeColumn = @Now WHERE Id = @Id"
Using con As New MySqlConnection("<CONNECTION STRING GOES HERE>"), _
cmd As New MySqlCommand(sql, con)
cmd.Parameters.Add("@Now", MySqlDbType.Datetime).Value = Now
cmd.Parameters.Add("@Id", MySqlDbType.Int32).Value = 1
con.Open()
cmd.ExecuteNonQuery()
End Using
Let the connector do the dirty work for you such as formating the date.
obrien.james
Member
11 Points
55 Posts
MySql Dates and Vb.net
Dec 21, 2012 08:49 AM|LINK
Hi,
I'm getting into real difficulty when trying to store and retreive dates into a MySQL database and my web application.
I have a form that allows users to select a start and stop date for a message to be displayed, the dates are selected using the Ajax calendar extender and the value placed into a textbox. Then when I 'save' the data it inserts the record into the database - I have set the calendar extender to format the date as yyyy-MM-dd which is the MySQL date format and it inserts the record no problem. The problem comes when I want to update an existing record, the form fills the textboxes in the MM-dd-YYYY format, and if I 'Update' the record it says:
Unable to convert MySQL date/time value to System.DateTime
and when I check the database, the date values are 0000-00-00.
Any help or suggestions as to how best to deal with this problem would be greatly appreciated.
Many thanks
James
jprochazka
Contributor
4896 Points
740 Posts
Re: MySql Dates and Vb.net
Dec 21, 2012 12:11 PM|LINK
You could try using the .NET connector for MySQL to make life easier.
http://dev.mysql.com/downloads/connector/net/1.0.html
You can then do something like so:
Dim sql As String = "UPDATE Table SET DateTimeColumn = @Now WHERE Id = @Id" Using con As New MySqlConnection("<CONNECTION STRING GOES HERE>"), _ cmd As New MySqlCommand(sql, con) cmd.Parameters.Add("@Now", MySqlDbType.Datetime).Value = Now cmd.Parameters.Add("@Id", MySqlDbType.Int32).Value = 1 con.Open() cmd.ExecuteNonQuery() End UsingLet the connector do the dirty work for you such as formating the date.