DateTime problem with MySql5

HI,

I am first time using lightspeed with mysql5 provider and used Allow Zero Datetime=true in connection string. I got following exception when querying the database.

Unable to materialize field [Date] on type [navidoc.core.entities.User]: field is type 'System.DateTime' but database returned type 'MySql.Data.Types.MySqlDateTime'. Check your table has an Id column and that your mappings are correct. See inner exception for details.

I have also tried field converter like this;

public class DbDateConverter : FieldConverter {

    protected override System.DateTime ConvertFromDatabase(MySqlDateTime databaseValue)
    {
        return (DateTime)databaseValue;
    }
    protected override MySqlDateTime ConvertToDatabase(System.DateTime systemValue)
    {
        return new MySqlDateTime(systemValue);
    }

}

Now I can query the database but got another exception. I am getting error when update entity "Incorrect datetime value". what am I doing wrong?

Are you getting this error when updating or loading? If it is updating check that the datetime is within the valid range to be stored in the database (e.g. as per http://dev.mysql.com/doc/refman/5.7/en/datetime.html), if its on the loading side and you have set a "zero" datetime then make sure you convert it to an appropriate value for a DateTime (e.g. DateTime.MinValue).

Jeremy

With FieldConverter I can load data successfully. Now I am getting following exception when updating record. my DateTime value is within valid range. "Incorrect datetime value: '2/12/2013 14:09:12' for column 'date' at row 1"

MySql5 create table command is

CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, username varchar(145) NOT NULL, password varchar(45) NOT NULL, full_name varchar(145) NOT NULL, date datetime NOT NULL, logout_time int(11) NOT NULL DEFAULT '30', phone varchar(20) DEFAULT NULL, department_name varchar(145) DEFAULT NULL, initials varchar(45) NOT NULL, comp_id int(11) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY username_UNIQUE (username) )

To use LS with Mysql is it must to implement field converter for datetime coloumn. I am abit confused because I think mysql connector/net client do conversion by default?

strange! when I dont use field converter for datetime, I can upate/insert record but then I could not load data from database the error is "Unable to materialize field [Date] on type [User]: field is type 'System.DateTime' but database returned type 'MySql.Data.Types.MySqlDateTime'. "

When I used filed converter for datetime, I can load data but could not update or insert record the error is "Incorrect datetime value: '2/12/2013 14:09:12' for column 'date' at row 1"

I have implemented datetime field like this;

protected override System.DateTime ConvertFromDatabase(MySqlDateTime databaseValue)
{
    return (DateTime)databaseValue;
}
protected override MySqlDateTime ConvertToDatabase(System.DateTime systemValue)
{
    return new MySqlDateTime(systemValue);
}

The issue is with the translation of the MySqlDateTime value back to the database. It appears we have to specifically set this up differently than we would ordinarily expect to. Ive added an update to cater for this and this will be available in the next nightly build for you.

Jeremy