SqlDateTime overflow thrown by Typed DataSet Insert

Question

I'm using a Typed DataSet with an Insert statement; I have a table that has a smalldatetime field defined to accept null values. When I insert from a .NET 2.0 FormView, I get a "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."

Now, I've read this post, and the parameter as sent to the class constructor is defined as

global::System.Nullable<global::System.DateTime> DoB

So, it looks like it should accept a Nullable obj. Additionally, the generated code is testing the value sent.

        if ((DoB.HasValue == true)) {
            command.Parameters[6].Value = ((System.DateTime)(DoB.Value));
        }
        else {
            command.Parameters[6].Value = global::System.DBNull.Value;
        }

Specifically, the error is occurring when generated SqlClient.SqlCommand.ExecuteScalar() runs:

        try {
            returnValue = command.ExecuteScalar();
        }

So, I guess my question is: how do I use a Typed DataSet to set a blank value (passed from a FormView on CommandName=Insert) to a null in a database?

Answer 1

Ok, so here's what worked for me. First, to reiterate, I've got a Typed DataSet with DataAdapters that's generating the ADO objects. So, on my page, I can create a ObjectDataSource with the type that points to my adapter, and then name the different access methods housed there-in.

No, I have an Insert to a table where basically all the columns are nullable; some varchar, some smalldatetime.

When I submit an empty form, I'd like nulls to be entered. They're not and lots of various errors are thrown. What I ended up doing is subclassing the ObjectDataSource to gain access to the Inserting event. (subclassed for reusability) In the Inserting event, I looped through the InputParameters, and if it was a string and == "", I set it to null. Also, you cannot set ConvertNullToDBNull to true; that causes the strings to fail. This successfully allowed the Nullable to remain null.