ASP.NET: Inserting NULL Values Into SQL Server Database

I literally almost destroyed a laptop today when trying to get around the fact that empty dates in my application were being stored in SQL Server as the completely useless '1900-01-01 00:00:00.000'.

Note that SQL Server is not the issue here, it WILL insert a NULL value if it's told to. If you're seeing this 1900 date then it's coming from your application.

I'm using an XSD within my Visual Studio project, and therefore it auto-generates methods for each of the methods I define on my TableAdapters. I needed to get null dates into these methods and then into the database without the application passing the 1900 date to SQL Server.

I tried loads of different methods of checking the values, by trying to pass Nothing, SqlDateTime.Null or System.DBNull.Value where the date was empty, but to no avail, I either got cast errors or the database did the insert but continued to think we were at the dawn of the 20th century.

Finally, I figured it out, and really there are two ways of achieving this. One is to use a Typed DataSet, which allows you to use the autogenerated 'SetMyColumnNameNull()' method.

But I'm not using a typed DataSet for my database insert methods, rather I call the stored procedure methods in my BLL and pass in the values rather than a row object.

So anyway, the answer was this:

When passing your date to your method which calls your stored procedure, pass your dates as strings. Then in your code you can check them like this:

            (System.ComponentModel.DataObjectMethodType.Insert, True)> _
    Public Function InsertOrder(ByVal date_in_question as String)

Dim dateInQuestion As Global.System.Nullable(Of Date)

        ' Bust any empty dates
        If date_in_question <> "" Then
            dateInQuestion = CDate(date_in_question)
        End If

        ' Do the Insert
        Dim orderId As Integer = Adapter.db_Insert_Update_Order(dateInQuestion)

Return orderId

Now when the date is passed to the stored procedure's method, if no date is set then an empty date is passed and the database inserts a NULL value.

You might want a more robust way of parsing your dates etc etc, but this worked for me.

Hope I've provided enough detail.