SqlDateTime Overflow Thrown by Typed Data Insertion Input
I am using Typed DataSet with Insert statement; I have a table that has a smalldatetime field defined to accept null values. When I paste from a .NET 2.0 FormView, I get "SqlDateTime overflow". Must be between 1/1/1753 12:00:00 and 12/31/9999 11:59:59 PM. "
Now I have read this post and the parameter sent to the class constructor is defined as
global::System.Nullable<global::System.DateTime> DoB
So it looks like it should accept Nullable obj. In addition, the generated code checks the submitted value.
if ((DoB.HasValue == true)) {
command.Parameters[6].Value = ((System.DateTime)(DoB.Value));
}
else {
command.Parameters[6].Value = global::System.DBNull.Value;
}
Specifically, the error occurs when generating the SqlClient.SqlCommand.ExecuteScalar () works:
try {
returnValue = command.ExecuteScalar();
}
So I guess my question is, how can I use a Typed DataSet to set the empty value (passed from FormView on CommandName = Insert) to null in the database?
a source to share
Ok so this is what worked for me. First, again, I have a typed DataSet with DataAdapters that generates ADO objects. So, in my page, I can create an ObjectDataSource with a type that points to my adapter and then call the various accessors hosted there.
No, I have an Insert to a table where basically all the columns are NULL; some varchar, some smalldatetime.
When I submit an empty form, I would like to enter zeros. They are not there, and many different errors are thrown away. What I ended up with is subclassing ObjectDataSource to access the Inserting event. (subclassed for reuse) In the Inserting event I looped over the InputParameters and if it was a string and == "" I set it to null. Also, you cannot set ConvertNullToDBNull to true; which causes the lines to fail. This successfully allowed Nullable to remain null.
a source to share