No problem

Glad you got it working.

I did not have the AllowNullValuesOnNewRow set to True if that is what you meant by me experiencing some problems.  I have never used that property.  However, I did not initialize the Null fields with DBNull in the SetDefaultValues.   At one point, I was trying to use the code in the SetDefaultValues event  "BO.FieldName = DBNull.Value" which generated an error while in the IDE.  I did not think of using the Me.CurrentRow property to get this done.   Now everything works correctly, as long as, I have the Null fields setup properly in the BO Mapper using the "Set Null on Alternate" property (Text fields = String.Empty and Date fields = #1/1/1900#).  

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!

I think the problem you're experiencing, jjp, has to do with the AllowNullValuesOnNewRow property.  The property defaults to False, so all fields within the business object are initialized for a new record.  For a SmallDateTime, this means 01/01/1900.  So, the value within the BO gets set as soon as you Add() a new record to the business object.  OK, now the "Set Null on Alternate" property only works through the Set() of the property, so if the field within the CurrentDataTable is initialized, it never goes through the Set(), so the field never gets set to NULL, it just stays as 01/01/1900. 

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.

Hi JJP,

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.

Edhy Rijo

JJP,

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.

Edhy Rijo

Hi 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!

Edhy Rijo

Hi Edhy,

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

Hi jjp,

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.

Edhy Rijo

Hi SF Team,

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!