CODEWITHSUNDEEP
c#asp.net-mvcentity-framework
Piotrek
Piotrek

Reputation: 11221

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value - no DateTime2 used

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

My application recently started to show this error, it is quite strange, because it worked earlier. I didn't change anything connected with DateTime in my "Word" Model. It started to happen when I added new Models to my project.

Server Error shows up when I try to Edit data. Creating and Deleting works fine.

Controller:

[HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Edit([Bind(Include = "ID,UsersLanguage,OtherLanguage,Notes")] Word word, int idOfCollection)
    {
        if (ModelState.IsValid)
        {
            db.Entry(word).State = EntityState.Modified;
            db.SaveChanges();
            return RedirectToAction("Index", new { idOfCollection = idOfCollection });
        }
        return View(word);
    }

Model:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace WebLanguageTeacher.Models.MyDatabase
{
    public class Word
    {
        public int ID { get; set; }
        [MinLength(2, ErrorMessage = "Wydaje mi się, że słowo powinno mieć przynajmniej 2 litery ;)")] 
        [DisplayName("Język Użytkownika")]
        [Required]
        public string UsersLanguage { get; set; }
        [MinLength(2, ErrorMessage = "Wydaje mi się, że słowo powinno mieć przynajmniej 2 litery ;)")] 
        [DisplayName("Inny język")]
        [Required]
        public string OtherLanguage { get; set; }
        [DisplayName("Notatki")]
        public string Notes { get; set; }
        [DisplayName("Ostatnia powtórka")]
        public DateTime LastReviewed { get; set; }
        [DisplayName("Następna powtórka")]
        public DateTime NextReview { get; set; }
        [DefaultValue(0)]
        [DisplayName("Przerwa między powtórkami")]
        public int ReviewInterval { get; set; } /*W miejsce Difficulty*/

        [DisplayName("Nazwa właściciela")]
        public string OwnerName { get; set; }
        public virtual Collection Collection { get; set; }

        [NotMapped]
        public bool ModifyReview { get; set; } /* Klient przesyła tylko za ile dni będzie następna powtórka, serwer sam generuje datę*/

        public Word(){
            ModifyReview = false;
        }
    }
}

What's wrong? I don't create any DateTime2 variables, so why my app tries to convert DateTime2 to DateTime?

I use ASP.net MVC with EntityFramework.

Upvotes: 3

Views: 2802

Answers (5)

typhon04
typhon04

Reputation: 2624

Make you property nullable by adding a question mark after DateTime.

    public DateTime? SentOn { get; set; }

Upvotes: 0

Łukasz W.
Łukasz W.

Reputation: 9755

You need to preserve the old value for the datetime fields (and all other fields you do not want to modify) if you do not like them to be changed. To do so retrieve old entity from DB and just change the fields that are updated in the edit form. For example:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "ID,UsersLanguage,OtherLanguage,Notes")] Word word, int idOfCollection)
{
    if (ModelState.IsValid)
    {
        var dbWord = db.Words.Find(word.ID);
        dbWord.UsersLanguage = word.UsersLanguage;
        dbWord.OtherLanguage = word.OtherLanguage;
        dbWord.Notes = word.Notes;

        db.SaveChanges(); // in this case dbWord is saved so datetime fields remains intact
        return RedirectToAction("Index", new { idOfCollection = idOfCollection });
    }
    return View(word);
}

Upvotes: 0

Slicksim
Slicksim

Reputation: 7172

The DateTime object defaults to DateTime.MinValue when it's value isn't explicitly set.

So you have a DateTime object in your model that isn't being set and is defaulting to this, which as noted above, is out of the range of a DateTime dbtype, so EntityFramework converts it to a DateTime2 dbtype, which then causes the cast error in your database.

To solve it, check out all your DateTime objects in your model, and make sure they are set anything other than DateTime.MinValue. If you don't want to set the value to anything, then make that field nullable in both your db and your model, and things will work

NB, setting a default on the db column doesn't solve this, the conversion happens too early, you must explicitly set the value

Upvotes: 2

azhar_SE_nextbridge
azhar_SE_nextbridge

Reputation: 304

Make datetime nullable you need to force them explicitly. Like if property "LastReviewed" is nullable you should declare it like below.

[DisplayName("Ostatnia powtórka")]
public DateTime? LastReviewed { get; set; }

Same with other datetime variables if they are nullable

Upvotes: 0

Ilya Sulimanov
Ilya Sulimanov

Reputation: 7836

  • DateTime range: January 1, 1753, - December 31, 9999

  • DateTime2 range: 0001-01-01 - 9999-12-31

Use below code in order to avoid conflict.

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
  modelBuilder.Entity<Word>().Property(o => o.NextReview).HasColumnType("datetime2");
 }

Upvotes: 1

Related Questions