For all datetime data fields I have set up in the BOMapper "Return Alternate on Null/Set Null...." and NULL Replacement Value = #1/1/1900# (for smalldatetime data type)

Therefore as I understand this feature, when the field value is NULL when saving the record, it writes a NULL value back to the database and not 1/1/1900.

However, the BO object is writing 1/1/1900 back to the database and not the NULL value. 

What I found when stepping through the BO logic using the debugger that if I don't set the value of the BO field to #1/1/1900# then the Set property within the BO code is never executed.  Therefore, when saving the record, the Get property logic retrieves the 1/1/1900 value of the BO.field and writes it to the database.

So what I have to do is in the BO_BeforeSave event I have placed the following code:

if BO.DateTimeFieldName = #1/1/1900# then BO.DateTimeFieldName = #1/1/1900#

This piece of code forces the Set property of the field in the BO to execute.  Now when the BO writes the value to the database, the Get property logic returns the NULL value and writes the NULL value back to the database.

This seems like a bug to me or am I doing something wrong in the BO Mapper configuration?

Thanks!

I am also new to SF.

Why would you need to set an Alternate value for a datetime fields?  I am working on a form with 2 datetimefields and they work just fine, if I don't set any value they will be DBNULL in the database when saving.

That is interesting.  So what you are saying is the datetime fields in your SQL Server database are set to allow NULLs.  When you save the record through the SF BO, the BO is writing the NULL value back to the table for the datetime fields instead of 1/1/1900 or 1/1/1800.    If this is correct, then how do you have the BO Mapper configured for these datetime fields? Because, as you read, I am getting the opposite.  Have you added any code to the BO to handle the date/null information before reading and writing it from the user form?   I have only been working with SF on and off for a about 3 months so any information on how to gracefully handle NULL values in the database using SF would be helpful.

Thanks,

jjp

I was not exactly correct on this one.  This is what it is happening on my project:

1. I add a new record with an empty date.
2. SF will replace the empty or null date with 1/1/1800 12:00:00 AM value.

But when I search for that record, SF will treat those dates as empty/null and will not be shown in my date object in the form.

In the BO Mapper, I did not modified the empty fields, I left it as default, so all this is done for us under the hood by SF.  So just try removing the customization in the BO mapper and test it, you should get the same results.

I told you I was knew to SF   Sorry if I mislead you!

Being new, I decided to do more test and change my DateTime fields to SmallDateTime and surprisingly, I am not able to save any record, since SF is adding a default datetime of '01/01/1800 00:00:00'  and this is not valid range for a SmallDateTime field.  I guess this may be a bug with a SmallDateTime field, setting my fields to DateTime, I don't have any problems. 

Hope this clear, confirm your issues.

I think I got this figured out

The DatetimePicker control has a property named NullDateValue, set its value to 1/1/1900 and problem should be fixed, also make sure the property ClearOnNuLL is set to True (Default) to display a blank date in the DateTimePicker control.

Looking at the SF source code I noticed that they are setting this property to 1/1/1800 by default and that is why I got the range error before.

So, you'll want to either set the AllowNullValuesOnNewRow property to False so that all fields are left as DBNull.Value, or in your SetDefaultValues(), you'll want do this to that field:

Me.CurrentRow("fieldname") = DBNull.Value

Otherwise, the field will have 01/01/1900 in it from the get-go.

In essence, as I stated in the first thread, I was doing this by forcing the Set property event to fired by placing logic in the BeforeSave event of the BO.  

Your way is a much cleaner way of ensuring NULLs are written back to the database for Add/Edit functions. 

I hope I finally understand how to handle fields in the database allowing nulls using SF BOs.

Thanks Ben for the clarification!

Glad you got it working.