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?

Additional followup: Part of the problem here is I can't debug this. As a work-around, I copied the object and made my own. Since typed datasets create partial classes for their tableadapters, I tapped into it and added a duplicate of the Insert object. That allowed me to put a breakpoint on it and see what the values is. Part II: If you look at the FormViewInsertEventArgs, the date is being sent as null. However, when you compare it to the Insert method, you'll find that's changed to {1/1/0001 12:00:00 AM}... More work needed... — end-user, Apr 18 '10 at 19:50
Ok, there has to be a bug in the way MS has implemented their reflection routines. If I change the parameter type in my method to string, it comes over as a blank. If I evaluate Nullable nix = ""; I *do* get a null. There *must* be something wrong in ObjectDataSourceMethod GetResolvedMethodData() call since it's creating a list of parameters to invoke the method with. Somewhere, I think it's just doing a new DateTime(), not a new Nullable(). I just don't have more time to look for it. — end-user, Apr 18 '10 at 21:16

score 1 · Accepted Answer · answered Apr 18 '10 at 22:59

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.

SqlDateTime overflow thrown by Typed DataSet Insert

1 Answers1