SqlDateTime overflow using Fluent NHibernate and MVC 3

Question

I'm using FNH to generate the mappings of about 30 classes of a library used in an ASP.NET MVC3 application. I'm working against MSSQL Express 10. I created some code allowing me to populate the database with some data for development purposes. However when I try to save an Account instance, I get this error message:

System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

This happens because of the Account.CreationDate attribute, which is a DateTime. I've looked around and the DateTime in MSSQL and in .NET are not actually the same. I tried, via the FNH mappings force the column to be of "datetime2" but that didn't seem to help.

This is my Account class (simplified):

public class Account
{
    public virtual int Id { get; set; }
    public virtual string Comment { get; set;}
    public virtual DateTime CreationDate { get; set;}
    public virtual string Email { get; set;}
    public virtual string Password {get ; set;}
    public virtual string Locale {get; set;}

    public Account(string password, string email)
    {
        this.Email = email;
        SetNewPassord(password);
        this.CreationDate = DateTime.Now;
        this.Locale = "en-US";
    }
}

And the FNH mapping:

public class AccountMap : ClassMap<Account>
{
    public AccountMap()
    {
        Id(x => x.Id);
        Map(x => x.Comment);
        Map(x => x.CreationDate);
        Map(x => x.Email);
        Map(x => x.Password);
        Map(x => x.Locale);
    }
}

And the code where I call this:

        var compteAdmin = new Account("test", "[email protected]");
        compteAdmin.Locale = "fr-FR";
        var toto = compteAdmin.CreationDate;
        try
        {
            session.Save(compteAdmin);
        }
        catch (Exception ex)
        {
            throw new ConfigurationException("Account.CreationDate: " + compteAdmin.CreationDate.ToString() + " ; Exception: " + ex);
        }

Note that for the purpose of debugging I'm throwing some exception. The output of that is kind of surreal!

Account.Creation date: 2/6/2013 5:32:29 PM ; Exception: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

have you checked the Database to see what the DatType of the DateTime Field is..? I wonder if you are Formatting it incorrectly — MethodMan
– MethodMan, Commented Feb 6, 2013 at 16:59
Yes I have. The field is a "datetime" in MSSQL - but for ASP.NET this would be a SqlDateTime. I was expecting NHibernate to convert itself and the point of the question is to ask how to make sure it does. — Astaar
– Astaar, Commented Feb 7, 2013 at 8:47

Astaar · Accepted Answer · 2013-03-31 11:22:53Z

5

Problem was that there the DateTime attributes was not set to 'nullable', so instead of setting this property to "Null", it was constantly set to 01/01/0001 and therefore triggered the out of range error.

So setting the attribute as Nullable in the class (ie: public virtual DateTime? CreationDate { get; set;}) as well as in the Mappings (Map(x => x.CreationDate).Nullable;) is required.

Failling that, even if you set the attribute to null, it would say as 01/01/0001. Once the attribute is made nullable, its value really is null and the query is successful.

edited Mar 31, 2013 at 11:22

answered Feb 8, 2013 at 9:03

Astaar

6,7288 gold badges44 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

SqlDateTime overflow using Fluent NHibernate and MVC 3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related