MySQL Bugs: #53768: VS 2008 Server Explorer Table data command is not showing dates

Bug #53768	VS 2008 Server Explorer Table data command is not showing dates
Submitted:	18 May 2010 23:00	Modified:	21 May 2010 3:05
Reporter:	King Coffee	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.2.2	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Hi MySQL Connector/NET developer,

I'm new to MySQL NET programming, but I think - this is a bug - and not an usage error.

Class DBAccess connects and run MySQL 6.2 ADO.NET commands.   

    protected void Page_Load(object sender, EventArgs e)
    {
        DBAccess db = new DBAccess(false);

        db.CommandText = "INSERT INTO testdatetable " +
            "(testdate)VALUES (" +
            DateTime.Now.ToOADate() +
            ")";

        db.ExecuteNonQuery();

        db.CommandText = "INSERT INTO testdatetable " +
            "(testdate)VALUES (" +
            DateTime.Now.ToShortDateString() +
            ")";

        db.ExecuteNonQuery();

        db.CommandText = "INSERT INTO testdatetable " +
            "(testdate)VALUES (" +
            DateTime.Now +
            ")";

        db.ExecuteNonQuery();

     }

Viewing the above code snippet from within VS 2008, Server Explorer, the data inserted is summarized as follows: 

 3/16/2004 12:00:00 AM   (First Insert Query)
 <Unable to read data>   (Second Insert Query)
 Throw Exception Error   (Third Insert Query)
 
 Current “Now” date is 5/18/2010.

I want to store dates so as to compare them later. Experimenting, I tried both “date” and “datetime” as data type for field testdate and the results are the same.

Any help is greatly appreciated.

King

How to repeat:
Create the table "testdatetable"  with a auto key field and a datetime field named "testdate".

Write a generic ADO.NET routine to connect to the database and effectively perform the code snippet above. The essence of the problem is inserting and reading the proper date.

Right-Click on the table and select Retrieve Data, in the VS 2008 Server Explorer Table panel.

Hi "King" and thanks for your report.

I have a feeling you're using me instead of manual but here...

    MySqlConnection conn = new MySqlConnection();
    conn.ConnectionString = "DataSource=xx;Database=test;UserID=xx;Password=xx;PORT=xx;Allow Zero Datetime=False;allow user variables = true;
    conn.Open();

    MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug53768", conn);
    cmdCreateTable.ExecuteNonQuery();

    cmdCreateTable.CommandText = "CREATE TABLE bug53768 (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, dt DATE NOT NULL)";
    cmdCreateTable.ExecuteNonQuery();

    System.DateTime dt = DateTime.Now;
    Console.Out.WriteLine("DT was " + dt.Date);
    String ins = "INSERT INTO bug53768 VALUES (NULL, '" + dt.Date + "')";

    cmdCreateTable.CommandText = ins;
    cmdCreateTable.ExecuteNonQuery();

produces correct result:
...
mysql Information: 10 : 1: Set Database: test
mysql Information: 3 : 1: Query Opened: DROP TABLE IF EXISTS bug53768
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: CREATE TABLE bug53768 (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, dt DATE NOT NULL)
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
DT was 19.05.10 0:00:00 <<<< date format on my box
mysql Information: 3 : 1: Query Opened: INSERT INTO bug53768 VALUES (NULL, '19.05.10 0:00:00')
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 1
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 2 : 1: Connection Closed

mysql> select * from bug53768;
+----+------------+
| Id | dt         |
+----+------------+
|  1 | 2019-05-10 |
+----+------------+
1 row in set (0.00 sec)

There is at least a few dozen reports regarding DATE(TIME) and c/NET in bugsdb. Feel free to check them out. There are also links in manual like:
http://dev.mysql.com/doc/refman/5.4/en/connector-net-programming-datetime.html
http://dev.mysql.com/doc/refman/5.4/en/connector-net-programming-tracing.html

Further more, your test case is incomplete as well as the info provided. Do check my test case which requires just a changes in credentials to run on any box. Tracing is very important too. I provided you with link to page explaining how to enable tracing in c/NET. You can also start MySQL server with --log option and check the general query log to see what's happening. MySQL server error log might, sometimes, prove useful too.

Sorry, I made a mistake in my sample, visible from results...

Code should go:
    System.DateTime dt = DateTime.Now;
    Console.Out.WriteLine("DT was " + dt.Date);
    String ins = "INSERT INTO bug53768 VALUES (NULL, '" + dt.Date.ToString("yyyy-MM-dd") + "')";

and the result:
mysql> select * from bug53768;
+----+------------+
| Id | dt         |
+----+------------+
|  1 | 2010-05-19 |
+----+------------+
1 row in set (0.00 sec)

Same table in ServerExplorer

Attachment: Bug53768.png (image/png, text), 38.46 KiB.

Hi Tonci Grgin

The MySQL manual usually give a complete syntax description of commands and not examples of common usage. Usage is very important to newbies. The code snippet you gave me help me narrow the error down to the connection string. I've copied my original connection string from an anonymous code demo. I thought it was a generic string.... and less restrictive. Your connection string worked great. The follow code also include the string that failed.

Thank you,
King 

    protected void Page_Load(object sender, EventArgs e)
    {
        /*
        DBAccess db = new DBAccess(false);

        db.CommandText = "INSERT INTO testdatetable " +
            "(testdate)VALUES ('" +
            DateTime.Now.Date +
            "')";
        db.ExecuteNonQuery();
        */

        MySqlConnection conn = new MySqlConnection();
        conn.ConnectionString =
         //"server=localhost;User Id=root;password=xxx;database=deserre;Persist Security Info=True;" +
         //"Allow User Variables=True;Functions Return String=True;Ignore Prepare=False;" +
         //"Respect Binary Flags=False;Use Affected Rows=True;pooling=False;";
        "server=localhost;User Id=root;password=xxx;database=deserre;" +
        "Allow User Variables=True;Allow Zero Datetime=False;";
        conn.Open();

        MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug53768", conn);
        cmdCreateTable.ExecuteNonQuery();

        cmdCreateTable.CommandText = "CREATE TABLE bug53768 (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, dt DATE NOT NULL)";
        cmdCreateTable.ExecuteNonQuery();

        System.DateTime dt = DateTime.Now;
        Console.Out.WriteLine("DT was " + dt.Date);
        //String ins = "INSERT INTO bug53768 VALUES (NULL, '" + dt.Date + "')";
        String ins = "INSERT INTO bug53768 VALUES (NULL, '" + dt.Date.ToString("yyyy-MM-dd") + "')";

        cmdCreateTable.CommandText = ins;
        cmdCreateTable.ExecuteNonQuery();
    }

Yes I completely agree with you and have already notified docs team to add this sample to manual.

Sent to Tony again.