SqlDateTime overflow on INSERT when date is correct using a Linq to SQL DataContext

I get an SqlDateTime overflow error (Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.) when doing an INSERT using an Linq DataContext connected to SQL Server database when I do the SubmitChanges().

Wh开发者_如何学编程en I use the debugger the date value is correct. Even if I temporary update the code to set the date value to DateTime.Now it will not do the insert.

Did anybody found a work-around for this behaviour? Maybe there is a way to check what SQL the datacontext submits to the database.

---

Do you have the field set as autogenerated in the designer? If that's not the problem, I'd suggest setting up logging of the data context actions to the console and checking the actual SQL generated to make sure that it's inserting that column, then trace backward to find the problem.

 context.Log = Console.Out;

FWIW, I often set my "CreatedTime" and "LastUpdatedTime" columns up as autogenerated (and readonly) in the designer and give them a suitable default or use a DB trigger to set the value on insert or update. When you set it up as autogenerated, it won't include it in the insert/update even if modified. If the column doesn't allow nulls, then you need to supply an alternate means of setting the value, thus the default constraint and/or trigger.

---

Are you sure you're looking at the right Date column? Happened to me once, and the error turned out to be caused by another non-nullable Date column that wasn't set before submitting.

---

I came across this recently. The error may as well say "something's preventing the save!". Because in my case, it was not the DateTime value that was the problem.

I thought I was passing a value in for the primary key, and what was arriving was "null". Being the key, it can't be null - and so my problem was completely somewhere else. By resolving the null, the problem disappeared.

We all hate misleading errors - and this is one of them.

Lastly, as a suggestion... If you do find conversion of dates a problem, then don't use dates at all! .NET's DateTime class supports the "Ticks" value. It can also instantiate a new DateTime(ticks); too. The only Gotcha with that one, is the implementation of ticks in Javascript has a different starting point in history. So you might want a conversion between ticks if you ever tried getting DateTimes from c# to Javascript.

---

I suggest you change your project's Target Framework. Maybe SQL Server is newer than .Net Framework. I see the same your issue:
My project's Target Framework is 3.5.
SQL Server is 2012

And then I change to 4.0. The issue is solved.

---

Bottom line: watch the order of your calls to SubmitChanges() and ensure that all objects that would be "submitted" are actually ready to be submitted. This often happens to me when I'm in the middle of setting the attributes of new LINQ object (e.g, the ".FirstName" of new "tblContact"), and then some conditional logic requires the creation of a separate, related record (e.g., a new "tblAddress" record), so the code goes to create the "tblAddress" and tries to SubmitChanges() on saving that record, but that SubmitChanges() then also tries to insert the unfinished "tblContact" record, which maybe doesn't yet have a required "BirthDate" field value set. Thus, the exception looks to occur when I'm inserting the "tblAddress" object/record, but actually refers to the lack of "BirthDate" for the "tblContact" object/record.