locked
MySql Dates and Vb.net RRS feed

  • Question

  • User1051794449 posted

    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

    Friday, December 21, 2012 4:49 AM

Answers

  • User-1404016747 posted

    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 Using
    

    Let the connector do the dirty work for you such as formating the date.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 21, 2012 8:11 AM