CODEWITHSUNDEEP
c#mysqldatetimedatetimepicker
Hyder Abbas
Hyder Abbas

Reputation: 21

DateTime is not saving correct Date and Time in PHPMyadmin database field with"DateTime" data type

I have DateTimePicker object in a C# winform application, from which I am getting date and time in yyyy-MM-dd HH:mm tt custom format. But whenever I save the data in phpmyadmin, it gets saved as "0000-00-00 00:00:00.000000" in the specific field. My code is bellow:-

private void submit_Click(object sender, EventArgs e)
{
    DateTime scd = MydateTimePicker.Value;
    cn.Open();
    cmd.Connection = cn;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "insert into flight(flight_schedule) values ('" + scd + "')";
    cmd.ExecuteNonQuery();
    cn.Close();
}

Upvotes: 1

Views: 1897

Answers (2)

Soner Gönül
Soner Gönül

Reputation: 98750

No.

You have a bad habit to kick as choosing wrong data type. You should never store your DateTime values as a string.

Change your flight_schedule to DATETIME column type and pass your DateTime value directly with a parameterized query.

Also use using statement to dispose your connection and command automatically instead of calling Close or Dispose methods manually.

using(var cn = new MySqlConnection(conString))
using(var cmd = cn.CreateCommand())
{
   cmd.CommandText = "insert into flight(flight_schedule) values (@date)";
   cmd.Parameters.Add("@date", MySqlDbType.Datetime).Value = MydateTimePicker.Value;
   // I assume you change your column type to Datetime
   cn.Open();
   cmd.ExecuteNonQuery();
}

EDIT:

Looks like you already have DATETIME column in your database, so my answer seems irrelevant but I still believe saving your DateTime as their values, not their string representations.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108651

In c# when you use a DateTime variable in a string context, it is treated as if it were appended with .ToString(). Unfortunately that delivers a locale-specific rendering of the date. On my USA locale it looks like this:

 2/26/2016 7:12:19 PM

MySQL, when you present datetime values to it as strings, needs this format.

2016-02-26 19:12:19

In some versions of MySQL, a failure to interpret a date results in a zero date. Read this for more information on that topic. http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_allow_invalid_dates

Your insert query constructs a query string, so your DateTime value gets formatted in a way that MySQL cannot use. That's why you get that strange zero date.

If you changed your insert query to this, things would probably start to work.

cmd.CommandText = "insert into flight(flight_schedule) values ('" +
     scd.ToString("yyyy-MM-dd HH:mm:ss") + "')";

Better yet, use parameters. Read this for an example and discussion. C# SqlParameters Short Hand

Upvotes: 2

Related Questions