DateTime format problem when adding record (MySQL) RSS

• • Reply
  

  Pavel_147

  Member

  360 Points

  189 Posts

  DateTime format problem when adding record (MySQL)

  Dec 21, 2011 05:10 PM|LINK

  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.

• • Reply
  

  robwscott

  Star

  8079 Points

  1491 Posts

  Re: DateTime format problem when adding record (MySQL)

  Dec 21, 2011 05:29 PM|LINK

  string newDate = txt1.Text + " " + txt2.Text;
  DateTime formatDate = DateTime.Parse(newDate);
  cmd1.Parameters.AddWithValue("@parDateTime1", formatDate);

  Please post your code for us to help!!
  Mark Answered if it helps - Good luck!
  Cheers!
  Design And Align
  - Rob
  
• • Reply
  

  Pavel_147

  Member

  360 Points

  189 Posts

  Re: DateTime format problem when adding record (MySQL)

  Dec 21, 2011 05:42 PM|LINK

  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.

• • Reply
  

  smcoxon

  Contributor

  5455 Points

  948 Posts

  Re: DateTime format problem when adding record (MySQL)

  Dec 21, 2011 05:45 PM|LINK

  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);

  Regards
  Smcoxon
  
  No Gem is ever polished without some friction.
  • Edited by smcoxon on Dec 21, 2011 05:48 PM
• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: DateTime format problem when adding record (MySQL)

  Dec 21, 2011 05:51 PM|LINK

  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.

• • Reply
  

  Pavel_147

  Member

  360 Points

  189 Posts

  Re: DateTime format problem when adding record (MySQL)

  Dec 21, 2011 05:58 PM|LINK

  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

• • Reply
  

  Pavel_147

  Member

  360 Points

  189 Posts

  Re: DateTime format problem when adding record (MySQL)

  Dec 21, 2011 06:04 PM|LINK

  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.

• • Reply
  

  Pavel_147

  Member

  360 Points

  189 Posts

  Re: DateTime format problem when adding record (MySQL)

  Dec 21, 2011 06:20 PM|LINK

  smcoxon

  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.

• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: DateTime format problem when adding record (MySQL)

  Dec 21, 2011 06:31 PM|LINK

  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?

• • Reply
  

  Pavel_147

  Member

  360 Points

  189 Posts

  Re: DateTime format problem when adding record (MySQL)

  Dec 21, 2011 06:40 PM|LINK

  The error message is the same in all cases:

  String was not recognized as a valid DateTime.

  Regards,

  Pavel.