c# - 将 DATETIME 数据类型保存到 MySQL

Question

我在asp.net中创建了一个gridview，它有两列DATETIME数据类型，当我连接到mysql数据库将其保存在其中时，它显示一个错误“错误的日期时间值:'01/01/2017 00:07: cmd.ExecuteNonQuery() 中第 1 行的“LogInDate_Time”列的“26”；线

如何解决？

C#

protected void Page_Load(object sender, EventArgs e)
{
}
protected void LoggedIn(object sender, EventArgs e)
{
    CheckBox checkedCheckBox = (sender as CheckBox);
    GridViewRow checkedRow = (checkedCheckBox.NamingContainer as GridViewRow);
    Label loggedInDateTime = checkedRow.FindControl("lblLoggedInDateTime") as Label;
    if (checkedCheckBox.Checked)
    {
        loggedInDateTime.Text = DateTime.Now.ToString();
    }
    else
    {
        loggedInDateTime.Text = "";
    }
}

protected void LoggedOut(object sender, EventArgs e)
{
    CheckBox checkedCheckBox = (sender as CheckBox);
    GridViewRow checkedRow = (checkedCheckBox.NamingContainer as GridViewRow);
    Label loggedOutDateTime = checkedRow.FindControl("lblLoggedOutDateTime") as Label;
    if (checkedCheckBox.Checked)
    {
        loggedOutDateTime.Text = DateTime.Now.ToString();
    }
    else
    {
        loggedOutDateTime.Text = "";
    }
}

protected void btnSave_Click(object sender, EventArgs e)
{
    foreach (GridViewRow row in GridView1.Rows)
    {
        string Attendance_ID = (row.FindControl("lblAttendanceID") as Label).Text;
        string Attendance_Name = (row.FindControl("lblAttendanceName") as Label).Text;
        string LogInDate_Time = (row.FindControl("lblLoggedInDateTime") as Label).Text;
        string LogOutDate_Time = (row.FindControl("lblLoggedOutDateTime") as Label).Text;
        InsertData(Attendance_ID, Attendance_Name, LogInDate_Time, LogOutDate_Time);
    }
    lblMessage.Text = "All Records Saved Successfully!!";
}

public void InsertData(string Attendance_ID, string Attendance_Name, string LogInDate_Time, string LogOutDate_Time)
{

    //Your saving code.
    string A = "server=localhost; userid=; password=; database=admindb; allowuservariables=True; Convert Zero Datetime=True; Allow Zero Datetime=True ";
    using (MySqlConnection connection = new MySqlConnection(A))
    {
        string UpdateQuery = " INSERT INTO Attendance_Table (Attendance_ID, Attendance_Name,LogInDate_Time, LogOutDate_Time)" + " VALUES (@Attendance_ID,@Attendance_Name,@LogInDate_Time,@LogOutDate_Time)";
        MySqlCommand cmd = new MySqlCommand(UpdateQuery, connection);
        MySqlParameter paramAttendance_ID = new MySqlParameter("@Attendance_ID", Attendance_ID);
        cmd.Parameters.Add(paramAttendance_ID);
        MySqlParameter paramAttendance_Name = new MySqlParameter("@Attendance_Name", Attendance_Name);
        cmd.Parameters.Add(paramAttendance_Name);
        MySqlParameter paramLogInDate_Time = new MySqlParameter("@LogInDate_Time", LogInDate_Time);
        cmd.Parameters.Add(paramLogInDate_Time);
        MySqlParameter paramLogOutDate_Time = new MySqlParameter("@LogOutDate_Time", LogOutDate_Time);
        cmd.Parameters.Add(paramLogOutDate_Time);
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();
    }
}




protected void lbInsert_Click(object sender, EventArgs e)
{

    ObjectDataSource1.InsertParameters["Attendance_ID"].DefaultValue = ((TextBox)GridView1.FooterRow.FindControl("TxtID")).Text;
    ObjectDataSource1.InsertParameters["Attendance_Name"].DefaultValue = ((TextBox)GridView1.FooterRow.FindControl("TxtName")).Text;
    ObjectDataSource1.InsertParameters["Attendance_Con"].DefaultValue = ((CheckBox)GridView1.FooterRow.FindControl("cbAttendanceCon")).Text;
    ObjectDataSource1.InsertParameters["LogInDate_Time"].DefaultValue = ((TextBox)GridView1.FooterRow.FindControl("txtLogIn")).Text;
    ObjectDataSource1.InsertParameters["Leaving_Con"].DefaultValue = ((CheckBox)GridView1.FooterRow.FindControl("cbLeavingCon")).Text;
    ObjectDataSource1.InsertParameters["LogOutDate_Time"].DefaultValue = ((TextBox)GridView1.FooterRow.FindControl("txtLogOut")).Text;
    ObjectDataSource1.Insert();



}

}

Answer 1

MySQL 希望日期采用 YYYY-MM-DD 格式。

引用:

• https://dev.mysql.com/doc/refman/5.5/en/datetime.html