MySql.数据.MySqlClient.MySqlException : Incorrect datetime value

Question

嗨，我必须将一个表中的详细信息添加到另一个表中，这些信息应该在日期内。这些日期是从文本框中读取的。

但是我得到了错误:

"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 注入(inject)攻击和转换问题，并提高代码可读性。

(顺便说一句，我会敦促您放弃当前的连接共享，而是始终创建并打开一个新的 MySqlDbConnection 并在结束时处理它您的操作 - 依靠连接池来提高效率。)