MySql.Data.MySqlClient.MySqlException：不正确的日期时间值

Question

Hai我必须从一个表添加细节到另一个应该在日期之内的细节。这些日期是从文本框中读取的。

但我发现了错误：

"An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code 
Additional information: Incorrect datetime value: '11/25/2015 12:00:00 AM' for column 'debissuedate' at row 1" 

第一个表是t_bondridapp与字段：ID，CANCODE，canname，debissuedate ...等 我必须从这个表复制到新的命名作为bondlocal与字段： bondid，cancode，canname，bonddate。 我使用的代码

public class DBConnection 
     { 
      private DBConnection() 
      { 

      } 
      private string dbname = string.Empty; 
      public string DBName 
      { 
       get { return dbname;} 
       set { dbname = value;} 

      } 
      public string Password { get; set; } 
      private MySqlConnection mycon = null; 
      public MySqlConnection Connection 
      { 
       get { return mycon; } 
      } 
      private static DBConnection _instance = null; 
      public static DBConnection Instance() 

      { 
       if(_instance==null) 
        _instance=new DBConnection(); 
       return _instance; 
      } 
      public bool IsConnect() 
      { 
       bool result = true; 
       if(mycon==null) 
       { 
        if (String.IsNullOrEmpty(dbname)) 
         result = false; 
        string constr = string.Format("server=localhost;user id=root;password=mysql;database=pnys;",dbname); 
        mycon = new MySqlConnection(constr); 
        mycon.Open(); 
        result = true; 
       } 
       return result; 
      } 
      public void Close() 
      { 
       mycon.Close(); 
      } 
     } 




     protected void Page_Load(object sender, EventArgs e) 
     { 

     } 



     protected void Button1_Click1(object sender, EventArgs e) 
     { 
      MySqlDateTime fdate =new MySqlDateTime(DateTime.Parse(TextBox3.Text)); 
      MySqlDateTime sdate = new MySqlDateTime(DateTime.Parse(TextBox4.Text)); 
      var dbCon = DBConnection.Instance(); 
      dbCon.DBName = "pnys"; 
      if (dbCon.IsConnect()) 
      { 
       string query = "INSERT INTO bondlocal (cancode,canname,bonddate) SELECT t_bondridapp.cancode,t_bondridapp.canname,t_bondridapp.debissuedate FROM t_bondridapp WHERE debissuedate>='" + fdate + "'AND debissuedate<='" + sdate + "'"; 
       MySqlCommand cmd = new MySqlCommand(query, dbCon.Connection); 

       cmd.ExecuteNonQuery(); 

      } 
      Server.Transfer("ReportBonds.aspx"); 
     } 

请帮助我......

Answer 1

基本上，问题是你是如何传递参数到数据库中。你不应该需要自己创建一个MySqlDateTime - 只需使用参数化的SQL，它应该是罚款：

// TODO: Use a date/time control instead of parsing text to start with 
DateTime fdate = DateTime.Parse(TextBox3.Text); 
DateTime sdate = DateTime.Parse(TextBox4.Text); 

string query = @"INSERT INTO bondlocal (cancode,canname,bonddate) 
     SELECT t_bondridapp.cancode,t_bondridapp.canname,t_bondridapp.debissuedate 
     FROM t_bondridapp 
     WHERE debissuedate >= @fdate AND debissuedate <= @sdate"; 
using (var command = new MySqlCommand(query, dbCon)) 
{ 
    command.Parameters.Add("@fdate", MySqlDbType.Datetime).Value = fdate; 
    command.Parameters.Add("@sdate", MySqlDbType.Datetime).Value = sdate; 
    command.ExecuteNonQuery(); 
} 

基本上，你应该只使用字符串连接SQL内从未特定值。参数化SQL可防止SQL注入攻击和转换问题，并提高代码的可读性。

（顺便说一句，我会劝你抛弃你的当前连接共享，而不是总是创建并打开一个新的MySqlDbConnection，并在你的操作结束时将其处理 - 依靠连接池，使其高效）。