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

Question

Dear Linq experts,

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().

When 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.

Answer 1

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.

Answer 2

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.