I'm using a Calendar control in a formview, and having problems only when no date is selected. The SQL allows nulls for the datetime field. When submitting the form without the date field(it's not required), I get this message:

 SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.]
   System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) +177
   System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) +167
   System.Data.SqlTypes.SqlDateTime..ctor(DateTime value) +49
   System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) +89
   System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj) +1257
   System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) +5492
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
(etc)

On SQL Profiler, I see no update query is tried, I guess some kind of validation is failing on the ASP.NET 2.0 end.

 Here is some relevant code

                         &nbsp;<asp:Calendar ID="Calendar1" runat="server"  SelectedDate='<%# Bind("date_to_call") %>'></asp:Calendar>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ICOMSformsConnectionString %>"
        DeleteCommand="DELETE FROM [payment] WHERE [id] = @id" InsertCommand="INSERT INTO [payment] ([customer_name], [customer_account_number], [customer_service_address], [customer_phone_number], [customer_phone_number_2], [existing_mop], [date_to_call], [time_to_call], [last_4_credit], [cc_expire_mo], [cc_expire_year], [mop], [amount], [customer_location], [CSR_SRS_name], [CSR_sales_id]) VALUES (@customer_name, @customer_account_number, @customer_service_address, @customer_phone_number, @customer_phone_number_2, @existing_mop, @date_to_call, @time_to_call, @last_4_credit, @cc_expire_mo, @cc_expire_year, @mop, @amount, @customer_location, @CSR_SRS_name, @CSR_sales_id) ; SELECT @payment_id = SCOPE_IDENTITY()"
        SelectCommand="SELECT * FROM [payment] WHERE id = @payment_id " UpdateCommand="UPDATE [payment] SET [customer_name] = @customer_name, [customer_account_number] = @customer_account_number, [customer_service_address] = @customer_service_address, [customer_phone_number] = @customer_phone_number, [customer_phone_number_2] = @customer_phone_number_2, [existing_mop] = @existing_mop, [date_to_call] = @date_to_call, [time_to_call] = @time_to_call, [last_4_credit] = @last_4_credit, [cc_expire_mo] = @cc_expire_mo, [cc_expire_year] = @cc_expire_year, [mop] = @mop, [amount] = @amount, [customer_location] = @customer_location, [CSR_SRS_name] = @CSR_SRS_name, [CSR_sales_id] = @CSR_sales_id, [inserted_timestamp] = GETDATE() WHERE [id] = @id" OnInserted="SqlDataSource1_Inserted">
snip
         <InsertParameters>
snip
            <asp:Parameter Name="date_to_call" Type="DateTime" ConvertEmptyStringToNull=true/>
snip
        </InsertParameters>
    </asp:SqlDataSource> 

I have little code in my SqlDataSource1_Inserted, it displays a confirmation that the item has been inserted. It works when a value is selected on the calendar.

Now I've read all I could on the web, and I've heard things about problems with sending null via the Calendar object, but I'm not entirely sure what to do. I've read something about the minimum date property to null, and also about checking the value of the (I'm not sure to check it on the formview, control itself, or sqldatasource parameter, and which event to use) property and if it's empty(or whatever), set it to DBNULL.Value.

 I'm hoping someone with some experience on this can help me out or give me suggestions.

Thanks

Hi jimmy.cartrette,

My advice would be to create a new control which inherits from asp:Calendar and then add a new Property called SelectedValue.

Within the new SelectedValue property you need to include logic for getting/setting null (DBNull) values. If a null value is passed from the DataBase, set the Calendar to DateTime.MinValue. If the SelectedDate property of the Calendar returns DateTime.MinValue, then you set the SelectedValue to null.

This is the approach we took with our date pickers. We offer a FREE date picker that you might be interested in; BDPLite. You can download the installer from http://www.basicdatepicker.com/download/

The following sample demonstrates using the SelectedValue property to bind to a SQLDataSource which may contain null values. See http://samples.basicdatepicker.com/autobinding.aspx 

Hope this helps. 

If anyone is interested, I have a solution based on his recommendation.  Here's the code.  http://pastebin.com/f2bb4e42

Thanks for the fix, I would like to try it, but I dont understand the instruction "drop the library into your bin folder" could youi give me a bit more detail please.

Thanks for your help

Sorry it took so long for me to reply.  All you do is put the code in a new class library project, then compile it.  Then place the dll that was created through compiling in the bin folder, then at the top of your aspx page type:

<%@ Register

Assembly="MAA.Web"

Namespace="MAA.Web.UI.WebControls"

TagPrefix="MAA" %>

Then in your aspx page, you call the <MAA:MAACalendar (instead of <asp:Calendar).  It inherits all the features of the regular Calendar control (such as databinding etc), with the ability to interpret null dates.

Hi BDP,

I tried your datepicker and I don't really get how it's supposed to handle the getting and setting of NULL values in DBs, seeing as how I still get an error when I load a "modify form" containing a date field that is linked to NULL in the DB.

My current configuration : the problematic fields are date fields in a Formview, that Formview is linked through an ObjectDataSource to tables in my DB (the whole thing is done with TableAdapters and BLL classes for each table)

I mean is there something i am forgetting, or is it indeed supposed to display a blank field when it "gets" a NULL from the DB, as well as supposed to "set" a NULL in the DB if I clear the field.

By the way, I am using the BasicDatePicker not the Lite version.