I'm using LLBlGen Pro 2.5 Final

I want to know how to pass null to a SmallDateTime field in SQL.

This is the code I'm using

Dim obj As Contractor_User = New Contractor_User
        obj.FetchUsingPK(UserId)
        If DATE_APPROVED <> Nothing Then
            obj.DATE_APPROVED = DATE_APPROVED
        Else
            obj.DATE_APPROVED = SqlDateTime.Null
        End If
        If DATE_DISAPPROVED <> Nothing Then
            obj.DATE_DISAPPROVED = DATE_DISAPPROVED
        Else
            obj.DATE_DISAPPROVED = SqlDateTime.Null
        End If
        obj.Save()

I don't want 1/1/1900 which is what it's saving as. Thank you very much for any help!

Is the field generated as a nullable dateTime?

Walaa wrote:

Is the field generated as a nullable dateTime?

Yes it is...

Then here is what you need to do:

obj.DATE_APPROVED = Nothing

Walaa wrote:

Then here is what you need to do:

obj.DATE_APPROVED = Nothing

I tried that and get

An exception was caught during the execution of an action query: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Is there a setting in LLBLGen I'm not setting? In the SQL DB the field is set a nullable

PLease enable DQE tracing (see manual: troubleshooting and debugging) and post the query that's executed. Also post the runtime lib build nr (see 'guidelines thread')

Otis wrote:

PLease enable DQE tracing (see manual: troubleshooting and debugging) and post the query that's executed. Also post the runtime lib build nr (see 'guidelines thread')

I added <system.diagnostics> <switches> <add name="SqlServerDQE" value="3" /> </switches> </system.diagnostics>

to the llblgenpro.exe.config. Now what do I do? The manual says to run LLBLGen Pro in visual studio? Which I did but nothing happened.

Walaa wrote:

Then here is what you need to do:

obj.DATE_APPROVED = Nothing

Do I need to set in the properties ConvertNullReferneceTypesToDefualtValue to false?

<system.diagnostics> <switches> <add name="SqlServerDQE" value="4" /> </switches> </system.diagnostics>

and in visual studio open the output window, there you can see all the query's executed, set a breakpoint before the one you want to examine and that's it.

ConvertNullReferneceTypesToDefualtValue

i think this is for returning values, not for setting values.

When set to false (default is true), an entity field wich has a reference type (e.g. string) will return null / Nothing if the value for the field is null / Nothing. When set to true (default), the default value belonging to that reference type is returned. The default value for a type is produced by the generated class TypeDefaultValue.

goose wrote:

<system.diagnostics> <switches> <add name="SqlServerDQE" value="4" /> </switches> </system.diagnostics>

and in visual studio open the output window, there you can see all the query's executed, set a breakpoint before the one you want to examine and that's it.

Am I adding this to my project or llblgen pro?

to the .config file of your application.

Otis wrote:

PLease enable DQE tracing (see manual: troubleshooting and debugging) and post the query that's executed. Also post the runtime lib build nr (see 'guidelines thread')

Method Exit: CreateSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [WPIMSV2].[dbo].[tbl_Contractor].[CONTRACTOR_ID], [WPIMSV2].[dbo].[tbl_Contractor].[COMPANY_NAME], [WPIMSV2].[dbo].[tbl_Contractor].[STREET_ADDRESS], [WPIMSV2].[dbo].[tbl_Contractor].[STATE_ID], [WPIMSV2].[dbo].[tbl_Contractor].[ZIP_CODE], [WPIMSV2].[dbo].[tbl_Contractor].[DATE_CREATED], [WPIMSV2].[dbo].[tbl_Contractor].[PHONE], [WPIMSV2].[dbo].[tbl_Contractor].[FAX], [WPIMSV2].[dbo].[tbl_Contractor].[isAPPROVED] AS [IsAPPROVED], [WPIMSV2].[dbo].[tbl_Contractor].[isSub] AS [IsSub], [WPIMSV2].[dbo].[tbl_Contractor].[CITY], [WPIMSV2].[dbo].[tbl_Contractor].[DATE_APPROVED], [WPIMSV2].[dbo].[tbl_Contractor].[DATE_DISAPPROVED] FROM [WPIMSV2].[dbo].[tbl_Contractor] WHERE ( [WPIMSV2].[dbo].[tbl_Contractor].[CONTRACTOR_ID] = @CONTRACTOR_ID1) Parameter: @CONTRACTOR_ID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ The thread 0x1560 has exited with code 0 (0x0). Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [WPIMSV2].[dbo].[tbl_Contractor_User].[UserId], [WPIMSV2].[dbo].[tbl_Contractor_User].[CONTRACTOR_ID], [WPIMSV2].[dbo].[tbl_Contractor_User].[isADMIN] AS [IsADMIN], [WPIMSV2].[dbo].[tbl_Contractor_User].[PHONE], [WPIMSV2].[dbo].[tbl_Contractor_User].[FAX], [WPIMSV2].[dbo].[tbl_Contractor_User].[CELL_PHONE], [WPIMSV2].[dbo].[tbl_Contractor_User].[FIRST_NAME], [WPIMSV2].[dbo].[tbl_Contractor_User].[LAST_NAME], [WPIMSV2].[dbo].[tbl_Contractor_User].[SSN], [WPIMSV2].[dbo].[tbl_Contractor_User].[POSITION_TYPE_ID], [WPIMSV2].[dbo].[tbl_Contractor_User].[DATE_APPROVED], [WPIMSV2].[dbo].[tbl_Contractor_User].[DATE_DISAPPROVED], [WPIMSV2].[dbo].[tbl_Contractor_User].[BADGE_ID], [WPIMSV2].[dbo].[tbl_Contractor_User].[REASON] FROM [WPIMSV2].[dbo].[tbl_Contractor_User] WHERE ( [WPIMSV2].[dbo].[tbl_Contractor_User].[UserId] = @UserId1) Parameter: @UserId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2c40f638-e738-4c00-9ad4-0707879e38cb.

Method Exit: CreateSelectDQ Method Enter: CreateUpdateDQ(4) Method Enter: CreateSingleTargetUpdateDQ(4) Generated Sql query: Query: UPDATE [WPIMSV2].[dbo].[tbl_Contractor_User] SET [DATE_APPROVED]=@DATE_APPROVED,[DATE_DISAPPROVED]=@DATE_DISAPPROVED WHERE ( [WPIMSV2].[dbo].[tbl_Contractor_User].[UserId] = @UserId1) Parameter: @DATE_APPROVED : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1/1/0001 12:00:00 AM. Parameter: @DATE_DISAPPROVED : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 4/29/2008 10:08:02 AM. Parameter: @UserId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2c40f638-e738-4c00-9ad4-0707879e38cb.

Method Exit: CreateSingleTargetUpdateDQ(4) Method Exit: CreateUpdateDQ(4) A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.DLL

Otis wrote:

PLease enable DQE tracing (see manual: troubleshooting and debugging) and post the query that's executed. Also post the runtime lib build nr (see 'guidelines thread')

Version 2.5 Final Released December 5, 2007

Thanks for your help on this!

seems to me that either the field is not setted null correctly either in the database or the catalog, please double check your database and try refreshing your catalog in the llbl designer.

goose wrote:

seems to me that either the field is not setted null correctly either in the database or the catalog, please double check your database and try refreshing your catalog in the llbl designer.

Both fields are SmallDateTime and are set as Allow nulls. I refreshed the catalog once again and still the same error. Here are some screen shots.

hankddog wrote:

goose wrote:

seems to me that either the field is not setted null correctly either in the database or the catalog, please double check your database and try refreshing your catalog in the llbl designer.

Both fields are SmallDateTime and are set as Allow nulls. I refreshed the catalog once again and still the same error. Here are some screen shots.

Another screen shot

Please attach your lgp project file.

goose wrote:

Please attach your lgp project file.

The file is too big.

Do I need to set in the properties ConvertNullReferneceTypesToDefualtValue to false?

.NET DateTime is a value type not a reference type.

I wish the second screen shot could have been much longer to show the (.NET 2.0+) Generate as Nullable type checkbox.

Please make sure the above checkbox is checked in the Designer and re-generate the code.

(EDIT)

And make sure you are using the following code:

        Dim obj As Contractor_User = New Contractor_User
        obj.FetchUsingPK(UserId)
        If DATE_APPROVED <> Nothing Then
            obj.DATE_APPROVED = DATE_APPROVED
        Else
            obj.DATE_APPROVED = **Nothing**
        End If
        If DATE_DISAPPROVED <> Nothing Then
            obj.DATE_DISAPPROVED = DATE_DISAPPROVED
        Else
            obj.DATE_DISAPPROVED = **Nothing**
        End If
        obj.Save()

If the field is not generated as Nullable type and you still want to keep it as is, then use the following code to a set a field to Nothing/null:

obj.SetNewFieldValue(CInt(Contractor_UserFieldIndex.DATE_APPROVED), Nothing)

For more info please read Entities, NULL values and defaults in the LLBLGen Pro manual under: Using the generated code -> Adapter/SelfServicing -> Using the entity classes

Walaa wrote:

Do I need to set in the properties ConvertNullReferneceTypesToDefualtValue to false?

.NET DateTime is a value type not a reference type.

I wish the second screen shot could have been much longer to show the (.NET 2.0+) Generate as Nullable type checkbox.

Please make sure the above checkbox is checked in the Designer and re-generate the code.

(EDIT)

And make sure you are using the following code:

        Dim obj As Contractor_User = New Contractor_User
        obj.FetchUsingPK(UserId)
        If DATE_APPROVED <> Nothing Then
            obj.DATE_APPROVED = DATE_APPROVED
        Else
            obj.DATE_APPROVED = **Nothing**
        End If
        If DATE_DISAPPROVED <> Nothing Then
            obj.DATE_DISAPPROVED = DATE_DISAPPROVED
        Else
            obj.DATE_DISAPPROVED = **Nothing**
        End If
        obj.Save()

If the field is not generated as Nullable type and you still want to keep it as is, then use the following code to a set a field to Nothing/null:

obj.SetNewFieldValue(CInt(Contractor_UserFieldIndex.DATE_APPROVED), Nothing)

For more info please read Entities, NULL values and defaults in the LLBLGen Pro manual under: Using the generated code -> Adapter/SelfServicing -> Using the entity classes

(.NET 2.0+) Generate as Nullable type was what I needed to have checked. Now it works perfectly!!!!!!!

Thanks Walaa!!!!!!!!!