Adapter, SQL 2k

I have an asset supertype and inherited collateral subtype, built off of 2 separate tables (tbl_Asset, tbl_Collateral).

tbl_Asset has an Identity Int field (AssetId). Collateral is related on this field. (Not an identity field).

I'm having trouble saving a collateral though, because I'm told that the AssetId is not optional. Of course that is true, but I was assuming the system would save the Asset data, get the @@IDENTITY, and use it to save the Collateral. Is this not how it works, or is there something wrong here?

Sorry I'd provide code, but there'd be a LOT of it since there are a lot of other related entities. The stack trace is:

at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, Boolean recurse) at sap.valuecollateral.bl.CollateralManager.Save(CollateralEntity collateral, Boolean refetchAfterSave, Boolean recurse) in G:\My Documents\code\Work\sap\valuecollateral\website2005\trunk\sap.valuecollateral.bl\CollateralManager.cs:line 137 at sap.valuecollateral.tests.CollateralManagerTest.CreateCollateral() in G:\My Documents\code\Work\sap\valuecollateral\website2005\trunk\tests\sap.valuecollateral.tests\CollateralManagerTest.cs:line 163

Error Message:

sap.valuecollateral.tests.CollateralManagerTest.CreateCollateral : SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'AssetID', table 'VC_to_Galaxy.dbo.tbl_Collateral'; column does not allow nulls. INSERT fails. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Enabling the trace, I get the following excerpt (edited because I don't think the rest is of value):

Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [VC_to_Galaxy].[dbo].[tbl_Asset] ([AssetTypeID], [CustomerID], [StatusID], [PublishDate], [ExpirationDate], [ExternalSystemID], [UserADName], [Name], [DocumentLocation], [Description], [CommentsInternal]) VALUES (@AssetTypeId, @CustomerId, @StatusId, @PublishDate, @ExpirationDate, @ExternalSystemId, @UserAdname, @Name, @DocumentLocation, @Description, @CommentsInternal);SELECT @AssetId_AssetEntity=SCOPE_IDENTITY() Parameter: @AssetId_AssetEntity : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>. Parameter: @AssetTypeId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2044. Parameter: @CustomerId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 312. Parameter: @StatusId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 234. Parameter: @PublishDate : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 6/14/2006 12:00:00 AM. Parameter: @ExpirationDate : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 6/14/2006 12:00:00 AM. Parameter: @ExternalSystemId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 43. Parameter: @UserAdname : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: SAP_ALL\C5067163. Parameter: @Name : String. Length: 250. Precision: 0. Scale: 0. Direction: Input. Value: NameText. Parameter: @DocumentLocation : String. Length: 600. Precision: 0. Scale: 0. Direction: Input. Value: DocumentLocationText. Parameter: @Description : String. Length: 4000. Precision: 0. Scale: 0. Direction: Input. Value: DescriptionText. Parameter: @CommentsInternal : String. Length: 1073741823. Precision: 0. Scale: 0. Direction: Input. Value: CommentsInternalText.

Method Exit: CreateSingleTargetInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [VC_to_Galaxy].[dbo].[tbl_Collateral] ([CollateralTypeID], [NotifyDate], [MediaRequestNumber], [MaterialNumber], [LegacyLocation], [HasERPContract], [IsValueReference], [LetExpire], [LastEmailedDate], [ApplicationText], [BenefitsText]) VALUES (@CollateralTypeId, @NotifyDate, @MediaRequestNumber, @MaterialNumber, @LegacyLocation, @HasErpcontract, @IsValueReference, @LetExpire, @LastEmailedDate, @ApplicationText, @BenefitsText);SELECT @AssetId=SCOPE_IDENTITY() Parameter: @AssetId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>. Parameter: @CollateralTypeId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 182. Parameter: @NotifyDate : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 6/14/2006 12:00:00 AM. Parameter: @MediaRequestNumber : AnsiString. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: MediaRequestNumberTe. Parameter: @MaterialNumber : AnsiString. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: MaterialNumberText. Parameter: @LegacyLocation : String. Length: 10. Precision: 0. Scale: 0. Direction: Input. Value: LegacyLoca. Parameter: @HasErpcontract : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: True. Parameter: @IsValueReference : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: True. Parameter: @LetExpire : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: True. Parameter: @LastEmailedDate : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 6/14/2006 12:00:00 AM. Parameter: @ApplicationText : String. Length: 1000. Precision: 0. Scale: 0. Direction: Input. Value: ApplicationTextText. Parameter: @BenefitsText : String. Length: 1500. Precision: 0. Scale: 0. Direction: Input. Value: BenefitsTextText.

Method Exit: CreateSingleTargetInsertDQ

Method Exit: CreateInsertDQ

Method Enter: DataAccessAdapterBase.ExecuteActionQuery

Method Enter: DataAccessAdapterBase.OpenConnection

Method Exit: DataAccessAdapterBase.OpenConnection

Method Enter: Query.ReflectOutputValuesInRelatedFields

Syncing field AssetId with parameter @AssetId_AssetEntity. Method Exit: Query.ReflectOutputValuesInRelatedFields Method Exit: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.Rollback Method Enter: DataAccessAdapterBase.Reset Method Exit: DataAccessAdapterBase.Reset Method Exit: DataAccessAdapterBase.Rollback A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.DLL Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.SaveEntity(4) Method Enter: TransactionResourceManager.Rollback

So it appears that the AssetEntity was saved successfully, and the output value was returned, but it wasn't used by the Collateral entity. The only unusual thing I see here is that the parameter was called AssetId_AssetEntity when inserting into the asset, and just AssetId for the collateral. At the end of the trace, I see 'Syncing field AssetId with parameter @AssetId_AssetEntity.' It seems like this needed to happend before the CollateralEntity was saved.

Anyway, I'm stumped. Any thoughts? Thanks again. -j

How is collateral related to asset? As I see that a scope_identity() call is appended to the query for collateral.

The PK of collateral has to be a NON-identity field and has to be the FK to Asset. That should be the case, otherwise you wouldn't be able to define the hierarchy.

The sync is done at the right time, see the trace at the bottom. The query logs aren't always printed in the right order as the rest, as logs from different tracelisteners can end up in the tracelog in a different order:

Method Enter: Query.ReflectOutputValuesInRelatedFields

Syncing field AssetId with parameter @AssetId_AssetEntity. Method Exit: Query.ReflectOutputValuesInRelatedFields Method Exit: DataAccessAdapterBase.ExecuteActionQuery

I've the feeling the entity definition of Collateral has AssetId defined as an identity field, is that correct? (you can easily check that in the designer)

Yup. The PK of collateral was a non-identity field and is indeed the FK to Asset. I don't know why the designer set it as an identity field. Changing that took care of the problem. Thanks!

jeffdeville wrote:

Yup. The PK of collateral was a non-identity field and is indeed the FK to Asset. I don't know why the designer set it as an identity field. Changing that took care of the problem. Thanks!

Could be set manually by accident, or it could be it WAS an identity field but it was changed in the db, but the project wasn't refreshed. Anyway, glad it works now