SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM
When performing the update operation from the website, I kept getting an error, and I thought it was the XML configuration or something went wrong when the SQL statement was executed
I tried to use SQL query analyzer to grab the generated SQL statements, and found that what is the problem with the correct execution? The SQL statement is as follows:
exec sp_executesql N'INSERT INTO dbo. LeanLogs (Version, OrganizationId, CustomInfoId, LastLeanAlarmType, NowLeanAlarmType, InsertTime, TreatedTime) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6); select SCOPE_IDENTITY()',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 datetime,@p6 datetime',@p0=1,@p1=35,@p2=12,@p3=10,@p4=10,@p5='2017-09-13 13:09:41',@p6=NULL
Error cause
If the value of the time type passed to the database is NULL, or when inserting or updating the database for DateTime.MinValue, the datetime field value is null by default.
NULL here refers to null in the program code, and most of the scenarios where this happens are: a time-type variable is defined in the program, and it is passed to the database (or stored procedure) without assigning a value. At this time, the value of this variable is assigned 01/01/01 by default.
Since the DateTime type field in the database, the minimum value is 1/1/1753 12:00:00, while the . NET Framework, the DateTime type, the minimum value is 1/1/0001 0:00:00, obviously, it is out of the minimum value range of the sql value, resulting in a data overflow error.
Let's take a look at how to see .net and sql max min time through C#:
Output effect:
In the end, it was tinkered for more than ten minutes,
The final error causes are as follows:
One of the attribute types of the object is also an object, that is, the table has a foreign key, and a new datetime attribute is added to the foreign key table, which is not assigned a value and is not set to a nullable type
When adding or updating an object, the datetime of the foreign key object is automatically attached to the default value, because the time of the .net default value is less than the default minimum time of the database
Solution: Set the property of a datetime type of the foreign key object to nullable type, help colleagues solve it perfectly!
|
Posted on 9/13/2017 2:34:58 PM
|
|
|
|
