MySql.Data.MySqlClient.MySqlException : Incorrect datetime value

Question

Hai I have to add details from one table to another which should be within to dates. These dates are read from text boxes.

But i'm getting Error:

"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"

The first table is t_bondridapp with fields : id,cancode,canname,debissuedate...etc And I have to copy from this table to new one named as bondlocal with fields : bondid,cancode,canname,bonddate. I've used the code

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

Pls Help Me...

As an aside, you should look into automatically implemented properties. Your code could be much, much shorter with no change in functionality. — Jon Skeet
– Jon Skeet, Commented Apr 22, 2016 at 5:56
What, with using automatically implemented properties? Just search to find out information about them, and it should become obvious. — Jon Skeet
– Jon Skeet, Commented Apr 22, 2016 at 9:35

Jon Skeet · Accepted Answer · 2016-04-22 05:57:25Z

3

Basically, the problem is how you're passing parameters into the database. You shouldn't need to create a MySqlDateTime yourself - just use parameterized SQL and it should be fine:

// 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();
}

Basically, you should never specific values within SQL by just using string concatenation. Parameterized SQL prevents SQL injection attacks and conversion issues, and improves code readability.

(As an aside, I would urge you to ditch your current connection sharing, and instead always create and open a new MySqlDbConnection and dispose of it at the end of your operation - rely on the connection pool to make it efficient.)

edited Apr 22, 2016 at 5:57

answered Apr 22, 2016 at 5:42

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sachith Parameswaran Over a year ago

Thank you very much sir. Let me try it

Sachith Parameswaran Over a year ago

Sir but what did yo mean by "conn" in the statement "using (var command = new MySqlCommand(query, conn))"

Jon Skeet Over a year ago

@SachithParameswaran: The database connection. conn is a more common name for a variable referring to a database connection than dbCon, but I've amended the answer.

Collectives™ on Stack Overflow

MySql.Data.MySqlClient.MySqlException : Incorrect datetime value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related