ASP.NET: Inserting Null DateTime values in SQL Server
A problem I run into from time to time is inserting Null values into a SQL Server DateTime field. We typically use Stored Procedures for everything and catch all statements to handle errors during insert. Usually, when you don't set a default on a control its value is Null. Likewise, if you explicitly set a control to Nothing (vb.net) It sends a Null. However, DateTime fields like to send the time with no date, which creates a non-null and invalid date for SQL server leading to an overflow exception.
After a lot of frustration with setting values to nothing, DbNull, etc... I found a solution. I check to make sure it is a valid SQL date (not VB.net) by testing if it is greater than 01/01/1900 and if not, setting it to System.SqlTypes.SqlDateTime.Null. You could place this in a BLL, or directly before you pass the date in.
I hope this helps someone as it has become standard practice for me now.
After a lot of frustration with setting values to nothing, DbNull, etc... I found a solution. I check to make sure it is a valid SQL date (not VB.net) by testing if it is greater than 01/01/1900 and if not, setting it to System.SqlTypes.SqlDateTime.Null. You could place this in a BLL, or directly before you pass the date in.
If Not EventDate > "01/01/1900" Then
EventDate = System.Data.SqlTypes.SqlDateTime.Null
End IfI hope this helps someone as it has become standard practice for me now.
Comments
Post a Comment