locked
DateTime format problem when adding record (MySQL) RRS feed

  • Question

  • User-952550035 posted

    Hello

    One of field of MySql table has type DateTime.

    When I add new record, I form this DateTime entry from 2 testbox - 1st textbox has format dd.MM.yyyy, 2nd textbox has format hh:mm:

    cmd1.Parameters.Add("@parDateTime1", MySqlDbType.DateTime).Value = txt1.Text + " " + txt2.Text;

    cmd1.ExecuteNonQuery();

    When executing MySqlCommand cmd1, FormatException fires with following message:

    String was not recognized as a valid DateTime.

    Is there some function that can customize my datetime format to one of the MySql.

    Thanks in advance,

    Pavel.

    Wednesday, December 21, 2011 1:10 PM

Answers

  • User-952550035 posted

    Hello,

    I was deceived by the tool. After making a lot of manipulations, I discovered that another textbox was the cause of error. This another textbox isn't associated with RequiredField Validator, so its filling is optional. But its value were associated with DATE field in database table, while the tool rapported DATETIME format violation. A lot of hours lost for nothing ...

    I apologize for troubles to all the answerers who participated in this post.

    Regards.

    Pavel.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 22, 2011 9:42 AM

All replies

  • User617492218 posted
    string newDate = txt1.Text + " " + txt2.Text;
    DateTime formatDate = DateTime.Parse(newDate);
    cmd1.Parameters.AddWithValue("@parDateTime1", formatDate);
    Wednesday, December 21, 2011 1:29 PM
  • User-952550035 posted

    Hello Rob,

    Thanks for response. Unfortunately doesn't work. Possible cause - in my version of MySql the Date format is yyyy-MM-dd and I don't know how to change it.

    Is there some function in .NET that allow convert dateformat according to particular template ?

    Reagards,

    Pavel.

    Wednesday, December 21, 2011 1:42 PM
  • User60291871 posted

    Hi,

    I believe you will need to format your data appropriately for MySQL DateTime data Type. Try this:

                DateTime dt = DateTime.Parse(txt1.Text + " " + txt2.Text);
                cmd1.Parameters.Add("@parDateTime1"MySqlDbType.DateTime).Value = String.Format("{0:yyyy-MM-dd HH:mm}", dt); 
    Wednesday, December 21, 2011 1:45 PM
  • User-1407477457 posted

    Disregard the format.  A formatted date is a string and you can't put a string into a DateTime field. 

    In your application code, convert the string to a DateTime variable and put that variable into your db.  Also do some checking to ensure that the values entered in the form represent valid dates and times.

    Wednesday, December 21, 2011 1:51 PM
  • User-952550035 posted

    Hi,

    But it's the same as robwscott already proposed. This solution doesn't work as the problem is probably in incompatibility with native MySql DateTime format

    Wednesday, December 21, 2011 1:58 PM
  • User-952550035 posted

    Hello Dan,

    Frankly speaking, I didn't clearly understand your proposition. Can you precise, please.

    What you mean under "convert the string to a DateTime variable and put that variable into your db" ?

    I put in my db the constant of type DateTime.

    Regards.

    Wednesday, December 21, 2011 2:04 PM
  • User-952550035 posted

    Hi,

    I believe you will need to format your data appropriately for MySQL DateTime data Type. Try this:

                DateTime dt = DateTime.Parse(txt1.Text + " " + txt2.Text);
                cmd1.Parameters.Add("@parDateTime1"MySqlDbType.DateTime).Value = String.Format("{0:yyyy-MM-dd HH:mm}", dt); 

    Sorry I was inattentive writing my previous answer to your message (I didn't notice second line from your solution). I just tried whole one. Unfortunately doesn't work - the same exception.

    Regards,

    Pavel.

    Wednesday, December 21, 2011 2:20 PM
  • User-1407477457 posted

    The first reply in this thread, from robwscott, had what should have been the correct answer.  You said it didn't work.  It might not have worked, but the only way the reason could have been related to formats would have been at the stage where he creates the DateTime variable.

    What error message did you get when you tried that suggestion?

    Wednesday, December 21, 2011 2:31 PM
  • User-952550035 posted

    The error message is the same in all cases:

    String was not recognized as a valid DateTime.

    Regards,

    Pavel.

    Wednesday, December 21, 2011 2:40 PM
  • User-1407477457 posted

    If you were running this code:

    string newDate= txt1.Text + " " + txt2.Text; 

    DateTime formatDate= DateTime.Parse(newDate); 

    cmd1.Parameters.AddWithValue("@parDateTime1", formatDate);

    Put a breakpoint on the last line.  This will ensure the line above it ran successfully and that the problem happens later on.  Step through your code until the error occurs.

    The problem might be with Parameters.AddWithValue.  I tend to use this method:

    cmd1.Parameters.Add(string.Empty, OdbcType.DateTime).Value = formatDate; 

    Wednesday, December 21, 2011 3:21 PM
  • User60291871 posted

    Disregard the format.  A formatted date is a string and you can't put a string into a DateTime field. 

    Oh yes you can... take a look here:

    http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html

    http://dev.mysql.com/doc/refman/5.0/en/using-date.html

    Wednesday, December 21, 2011 6:09 PM
  • User60291871 posted

    Hi Pavel_147,

    Are you sure the information you are inputting into txt1 and txt2 textbox's represents a valid date and time? What is the resulting String generated as a result of mu suggested code? take a look here for more on MySql DateTime string formats:

    http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html

    Also, can you show your cmd1.Commandtext ?

    Wednesday, December 21, 2011 6:22 PM
  • User-952550035 posted

    The exception occurs when executing MySqlCommand:

    cmd1.ExecuteNonQuery();

    I even tried to add seconds to DateTime

    string newDate= txt1.Text + " " + txt2.Text + ":00";

    Without any effect.

    The newDate value, catched during debuging I used directly in MySql workbench. There it works perfectly.

    Mystery ...

    Wednesday, December 21, 2011 6:33 PM
  • User60291871 posted

    What happens if you do this as a test:

    cmd1.Parameters.AddWithValue("@parDateTime1", "2011-06-21 14:36:00");
    Wednesday, December 21, 2011 6:38 PM
  • User60291871 posted

    Hi,

    Using my previous example try this modification for Universal DateTime format:

        DateTime dt = DateTime.Parse(txt1.Text + " " + txt2.Text);
                cmd1.Parameters.Add("@parDateTime1", MySqlDbType.DateTime).Value = String.Format("{0:u}", dt); 

    You might also need to look at the current Culture. Take a look here: http://msdn.microsoft.com/en-us/library/k494fzbf.aspx

    Also take a look at previous posts on this subject here:

    http://forums.asp.net/t/17257.aspx

    http://forums.mysql.com/read.php?38,43294,43717#msg-43717

    http://stackoverflow.com/questions/1423198/asp-net-c-sharp-format-a-mysql-date-field-properly-in-a-listview


    Thursday, December 22, 2011 4:58 AM
  • User-952550035 posted

    Hello,

    I was deceived by the tool. After making a lot of manipulations, I discovered that another textbox was the cause of error. This another textbox isn't associated with RequiredField Validator, so its filling is optional. But its value were associated with DATE field in database table, while the tool rapported DATETIME format violation. A lot of hours lost for nothing ...

    I apologize for troubles to all the answerers who participated in this post.

    Regards.

    Pavel.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 22, 2011 9:42 AM