Hello,

I am running into an intermitent issue when saving entities using UnitOfWork.

Sometimes, when attempting to save an entity it seems that some changes to that entity are not detected and are not saved (which violates some NOT NULL constraints and throws an exception). However, this does not happen all the time; mostly (~95%) it is OK. Restarting my application fixes the error when attempting to perform the exact same operation on the exact same data. Once the error occurs once in the application, it fails pretty consistantly after than on the same or different data. In general, a debug build with PDB images fails less often then a release build with no PDBs.

The key part of the code and exception is below (link to more code, and tracing logs: ftp: //60.240.44.174/AdjustmentBug.txt):

Public Shared Sub UnPick(ByVal lineItem As DAL.EntityClasses.SaleLineItemEntity, ByVal item As DAL.EntityClasses.ItemEntity, ByVal appState As BLL.CurrentState)
    Dim adapter As DAL.DatabaseSpecific.DataAccessAdapter = Utilities.GetDataAdapter()
    Dim UoW As New SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2
    Dim lineItemLogic As New BLL.SaleLineItemLowLevel(adapter, UoW, appState)
    lineItemLogic.Entity = lineItem
    lineItemLogic.Sale = lineItem.Sale

    adapter.StartTransaction(Data.IsolationLevel.RepeatableRead, "UnPickItem")
    Try
        ' Queue all DB operations.
        lineItemLogic.UnPick(item)

        ' Commit all DB operations.
        UoW.Commit(adapter)
        adapter.Commit()
    Finally
        adapter.Dispose()
    End Try
End Sub


Friend Sub UnPick(ByVal item As DAL.EntityClasses.ItemEntity)
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": SaleLineItemLowLevel.UnPick() entering.")

    ' Add adjustment to the picked item.
    Dim adjustmentLogic As New ItemAdjustment(Me.Adapter, Me.OpQueue, Me.GUIState)
    adjustmentLogic.Entity = New DAL.EntityClasses.ItemAdjustmentEntity
    adjustmentLogic.Entity.Item = item
    adjustmentLogic.Entity.UserAdjusted = Me.GUIState.User
    adjustmentLogic.Entity.AdjustmentTypeCode = Constants.AdjustmentStatus.InStock
    adjustmentLogic.Entity.DateEntered = DateTime.Now
    adjustmentLogic.Entity.Comment = UnPickAdjustmentComment + " " + Me._Sale.SiteCode + Me._Sale.InvoiceNumber.ToString()
    adjustmentLogic.CurrentAdjustment = item.ItemAdjustmentCurrent
    adjustmentLogic.Adjust()

    ' Unpick the item.
    item.SetNewFieldValue(DAL.ItemFieldIndex.SaleLineItemID, Nothing)
    item.SetNewFieldValue(DAL.ItemFieldIndex.SaleLineItemPickedOrder, Nothing)
    Me.OpQueue.AddForSave(item)
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": SaleLineItemLowLevel.UnPick() exiting.")
End Sub   


Friend Sub Adjust()
    ' Validation.
    If IsNothing(Me._Entity) OrElse IsNothing(Me._CurrentAdjustment) OrElse IsNothing(Me.GUIState) Then
        Throw New ArgumentException("You must set the Entity, CurrentAdjustment and GUIState properties before adjusting an item.")
    End If
    If Not Me.IsValid Then
        Throw New ValidationException(Me.ValidationMessage)
    End If
    If Me._Entity.ShipmentDate < Constants.SQLServerMinDateTime Then
        Me._Entity.SetNewFieldValue(DAL.ItemAdjustmentFieldIndex.ShipmentDate, Nothing)
    End If

    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": ItemAdjustment.Adjust() entering.")

    ' Now that the adjustment is valid, save it!
    Me._Entity.IsNew = True
    Me._Entity.PreviousAdjustment = Me._CurrentAdjustment
    Me._Entity.OrderOfAdjustments = Me._CurrentAdjustment.OrderOfAdjustments + 1
    Me.OpQueue.AddForSave(Me._Entity, True)

    ' Update tblItem to set the current adjustment ID.
    Dim item As New DAL.EntityClasses.ItemEntity
    item.IsNew = False
    item.ID = Me._CurrentAdjustment.ItemID
    item.ItemAdjustmentCurrent = Me._Entity
    Me.OpQueue.AddForSave(item)

    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": ItemAdjustment.Adjust() exiting.")
End Sub


An exception was thrown and published from the Microsoft .NET Framework.

#1) Exception Information
-----------------------------------------------------------------------
Exception Type: IBS.Utilities.Exceptions.DefaultHandlerException
MachineName: INVT-WRK-001
CreatedDateTime: 30/08/2005 2:19:07 PM
AppDomainName: CrossTracker.exe
ThreadIdentityName: 
WindowsIdentityName: INNOVENT\sean
Message: Exception from IBS Default Exception Handler.
TargetSite: NULL
HelpLink: NULL
Source: NULL
#2) Exception Information
-----------------------------------------------------------------------
Exception Type: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException
QueryExecuted: 
    Query: INSERT INTO [dbo].[tblItemAdjustment] ([ItemID],[AdjustmentTypeCode],[UserAdjustedID],[DateEntered],[Comment]) VALUES (@ItemID,@AdjustmentTypeCode,@UserAdjustedID,@DateEntered,@Comment);SELECT @ID=SCOPE_IDENTITY()
    Parameter: @ID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>.
    Parameter: @ItemID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 22443.
    Parameter: @AdjustmentTypeCode : AnsiString. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: IS.
    Parameter: @UserAdjustedID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1004.
    Parameter: @DateEntered : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 30/08/2005 2:17:05 PM.
    Parameter: @Comment : String. Length: 1000. Precision: 0. Scale: 0. Direction: Input. Value: Line item deleted from sale S795.

Parameters: System.Data.SqlClient.SqlParameterCollection
Message: An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'OrderOfAdjustments', table 'CrossTracker.dbo.tblItemAdjustment'; 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.
TargetSite: Int32 Execute()
HelpLink: NULL
Source: SD.LLBLGen.Pro.ORMSupportClasses.NET11

StackTrace Information
-----------------------------------------------------------------------
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntityCollection(IEntityCollection2 collectionToSave, Boolean refetchSavedEntitiesAfterSave, Boolean recurse)
   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, IPredicateExpression updateRestriction, Boolean recurse)
   at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit)
   at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse)
   at IBS.CrossTracker.BLL.LineItem.UnPick(SaleLineItemEntity lineItem, ItemEntity item, CurrentState appState) in C:\projects\Crossover IT\DotNet\Code\BLL\Sales.vb:line 528
   at IBS.CrossTracker.GUI.frmSale.btnUnpickItem_Click(Object sender, EventArgs e) in C:\projects\Crossover IT\DotNet\Code\GUI\Forms\frmSale.vb:line 2138
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Basically, in the Adjust() method, the following two lines of code seem to have no affect when the error occurs (but 95% of the time work just fine):

            Me._Entity.PreviousAdjustment = Me._CurrentAdjustment
            Me._Entity.OrderOfAdjustments = Me._CurrentAdjustment.OrderOfAdjustments + 1

Hmmm... Having had a closer look at the tracing output it looks like the reference to the item is being changed, because the ItemID being inserted into tblItemAdjustment is different from the one in the refetch select query.

Successful:

Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [dbo].[tblItem].[ID] AS [ID],[dbo].[tblItem].[LeaseID] AS [LeaseID],[dbo].[tblItem].[LocationID] AS [LocationID],[dbo].[tblItem].[SubLocationID] AS [SubLocationID],[dbo].[tblItem].[RMA] AS [RMA],[dbo].[tblItem].[BarcodeNumber] AS [BarcodeNumber],[dbo].[tblItem].[SiteCode] AS [SiteCode],[dbo].[tblItem].[ItemTypeID] AS [ItemTypeID],[dbo].[tblItem].[ItemMakeID] AS [ItemMakeID],[dbo].[tblItem].[Model] AS [Model],[dbo].[tblItem].[SerialNumber] AS [SerialNumber],[dbo].[tblItem].[AssetNumber] AS [AssetNumber],[dbo].[tblItem].[CustomerAssetNumber] AS [CustomerAssetNumber],[dbo].[tblItem].[AlternateAssetNumber] AS [AlternateAssetNumber],[dbo].[tblItem].[MFGPartNo] AS [MFGPartNo],[dbo].[tblItem].[YearOfMFG] AS [YearOfMFG],[dbo].[tblItem].[HDDSize] AS [HDDSize],[dbo].[tblItem].[ProcessorID] AS [ProcessorID],[dbo].[tblItem].[Memory] AS [Memory],[dbo].[tblItem].[OpticalDriveID] AS [OpticalDriveID],[dbo].[tblItem].[Modem] AS [Modem],[dbo].[tblItem].[NetworkCard] AS [NetworkCard],[dbo].[tblItem].[FDD] AS [FDD],[dbo].[tblItem].[MonitorSizeID] AS [MonitorSizeID],[dbo].[tblItem].[ConditionCode] AS [ConditionCode],[dbo].[tblItem].[Comments] AS [Comments],[dbo].[tblItem].[SoftwareLicense] AS [SoftwareLicense],[dbo].[tblItem].[OperatingSystemID] AS [OperatingSystemID],[dbo].[tblItem].[ParentItemID] AS [ParentItemID],[dbo].[tblItem].[IsProcessed] AS [IsProcessed],[dbo].[tblItem].[DateProcessed] AS [DateProcessed],[dbo].[tblItem].[DateReceived] AS [DateReceived],[dbo].[tblItem].[SaleLineItemID] AS [SaleLineItemID],[dbo].[tblItem].[SaleLineItemPickedOrder] AS [SaleLineItemPickedOrder],[dbo].[tblItem].[StockAdjusted] AS [StockAdjusted],[dbo].[tblItem].[CurrentAdjustmentID] AS [CurrentAdjustmentID],[dbo].[tblItem].[DateAdjusted] AS [DateAdjusted],[dbo].[tblItem].[AdjustmentComment] AS [AdjustmentComment],[dbo].[tblItem].[GoodsReceivedDetailsID] AS [GoodsReceivedDetailsID],[dbo].[tblItem].[AssetMatchedAttempted] AS [AssetMatchedAttempted],[dbo].[tblItem].[Quarantined] AS [Quarantined],[dbo].[tblItem].[QuarantineDate] AS [QuarantineDate],[dbo].[tblItem].[QuarantineComment] AS [QuarantineComment],[dbo].[tblItem].[SpecialStockID] AS [SpecialStockID],[dbo].[tblItem].[ConvID] AS [ConvID],[dbo].[tblItem].[ConvSaleLineItemID] AS [ConvSaleLineItemID],[dbo].[tblItem].[ConvID_Syd] AS [ConvID_Syd],[dbo].[tblItem].[ConvID_Mel] AS [ConvID_Mel],[dbo].[tblItem].[ConvID_May] AS [ConvID_May],[dbo].[tblItem].[Rowguid] AS [Rowguid],[dbo].[tblItem].[TimestampSQLSrv] AS [TimestampSQLSrv],[dbo].[tblItem].[TimestampCreated] AS [TimestampCreated],[dbo].[tblItem].[TimestampModified] AS [TimestampModified] FROM [dbo].[tblItem]  WHERE ( [dbo].[tblItem].[ID] = @ID1)
    Parameter: @ID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 22463.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: CreateInsertDQ
Generated Sql query: 
    Query: INSERT INTO [dbo].[tblItemAdjustment] ([ItemID],[AdjustmentTypeCode],[PreviousAdjustmentID],[OrderOfAdjustments], [UserAdjustedID],[DateEntered],[Comment],[ShipmentDate]) VALUES (@ItemID,@AdjustmentTypeCode,@PreviousAdjustmentID,@OrderOfAdjustments,@UserAdjustedID, @DateEntered,@Comment,@ShipmentDate);SELECT @ID=SCOPE_IDENTITY()
    Parameter: @ID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 0.
    Parameter: @ItemID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 22463.
    Parameter: @AdjustmentTypeCode : AnsiString. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: IS.
    Parameter: @PreviousAdjustmentID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 23096.
    Parameter: @OrderOfAdjustments : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.
    Parameter: @UserAdjustedID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1001.
    Parameter: @DateEntered : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 26/09/2005 11:55:00 AM.
    Parameter: @Comment : String. Length: 1000. Precision: 0. Scale: 0. Direction: Input. Value: Line item deleted from sale S795.
    Parameter: @ShipmentDate : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: <undefined value>.

Unsucessful:

Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [dbo].[tblItem].[ID] AS [ID],[dbo].[tblItem].[LeaseID] AS [LeaseID],[dbo].[tblItem].[LocationID] AS [LocationID],[dbo].[tblItem].[SubLocationID] AS [SubLocationID],[dbo].[tblItem].[RMA] AS [RMA],[dbo].[tblItem].[BarcodeNumber] AS [BarcodeNumber],[dbo].[tblItem].[SiteCode] AS [SiteCode],[dbo].[tblItem].[ItemTypeID] AS [ItemTypeID],[dbo].[tblItem].[ItemMakeID] AS [ItemMakeID],[dbo].[tblItem].[Model] AS [Model],[dbo].[tblItem].[SerialNumber] AS [SerialNumber],[dbo].[tblItem].[AssetNumber] AS [AssetNumber],[dbo].[tblItem].[CustomerAssetNumber] AS [CustomerAssetNumber],[dbo].[tblItem].[AlternateAssetNumber] AS [AlternateAssetNumber],[dbo].[tblItem].[MFGPartNo] AS [MFGPartNo],[dbo].[tblItem].[YearOfMFG] AS [YearOfMFG],[dbo].[tblItem].[HDDSize] AS [HDDSize],[dbo].[tblItem].[ProcessorID] AS [ProcessorID],[dbo].[tblItem].[Memory] AS [Memory],[dbo].[tblItem].[OpticalDriveID] AS [OpticalDriveID],[dbo].[tblItem].[Modem] AS [Modem],[dbo].[tblItem].[NetworkCard] AS [NetworkCard],[dbo].[tblItem].[FDD] AS [FDD],[dbo].[tblItem].[MonitorSizeID] AS [MonitorSizeID],[dbo].[tblItem].[ConditionCode] AS [ConditionCode],[dbo].[tblItem].[Comments] AS [Comments],[dbo].[tblItem].[SoftwareLicense] AS [SoftwareLicense],[dbo].[tblItem].[OperatingSystemID] AS [OperatingSystemID],[dbo].[tblItem].[ParentItemID] AS [ParentItemID],[dbo].[tblItem].[IsProcessed] AS [IsProcessed],[dbo].[tblItem].[DateProcessed] AS [DateProcessed],[dbo].[tblItem].[DateReceived] AS [DateReceived],[dbo].[tblItem].[SaleLineItemID] AS [SaleLineItemID],[dbo].[tblItem].[SaleLineItemPickedOrder] AS [SaleLineItemPickedOrder],[dbo].[tblItem].[StockAdjusted] AS [StockAdjusted],[dbo].[tblItem].[CurrentAdjustmentID] AS [CurrentAdjustmentID],[dbo].[tblItem].[DateAdjusted] AS [DateAdjusted],[dbo].[tblItem].[AdjustmentComment] AS [AdjustmentComment],[dbo].[tblItem].[GoodsReceivedDetailsID] AS [GoodsReceivedDetailsID],[dbo].[tblItem].[AssetMatchedAttempted] AS [AssetMatchedAttempted],[dbo].[tblItem].[Quarantined] AS [Quarantined],[dbo].[tblItem].[QuarantineDate] AS [QuarantineDate],[dbo].[tblItem].[QuarantineComment] AS [QuarantineComment],[dbo].[tblItem].[SpecialStockCode] AS [SpecialStockCode],[dbo].[tblItem].[ConvID] AS [ConvID],[dbo].[tblItem].[ConvSaleLineItemID] AS [ConvSaleLineItemID],[dbo].[tblItem].[ConvID_Syd] AS [ConvID_Syd],[dbo].[tblItem].[ConvID_Mel] AS [ConvID_Mel],[dbo].[tblItem].[ConvID_May] AS [ConvID_May],[dbo].[tblItem].[Rowguid] AS [Rowguid],[dbo].[tblItem].[TimestampSQLSrv] AS [TimestampSQLSrv],[dbo].[tblItem].[TimestampCreated] AS [TimestampCreated],[dbo].[tblItem].[TimestampModified] AS [TimestampModified] FROM [dbo].[tblItem]  WHERE ( [dbo].[tblItem].[ID] = @ID1)
    Parameter: @ID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 22463.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: CreateInsertDQ
Generated Sql query: 
    Query: INSERT INTO [dbo].[tblItemAdjustment] ([ItemID],[AdjustmentTypeCode],[UserAdjustedID],[DateEntered],[Comment]) VALUES (@ItemID,@AdjustmentTypeCode,@UserAdjustedID,@DateEntered,@Comment);SELECT @ID=SCOPE_IDENTITY()
    Parameter: @ID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 0.
    Parameter: @ItemID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 22443.
    Parameter: @AdjustmentTypeCode : AnsiString. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: IS.
    Parameter: @UserAdjustedID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1004.
    Parameter: @DateEntered : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 30/08/2005 2:17:05 PM.
    Parameter: @Comment : String. Length: 1000. Precision: 0. Scale: 0. Direction: Input. Value: Line item deleted from sale S795.

Method Exit: CreateInsertDQ

Any idea what's going on here?

Regards, Murray

You get a NULL in an FK field and a second time it does work. This is likely caused by the fact the entity on the PK side isn't re-fetchable and therefore synchronization fails. An entity isn't refetchable after a save in those cases where the PK should be generated inside the db, but isn't because the field is assumed to be an identity field or sequenced field, but isn't marked as such in the db (identity). The PK is therefore 0, can't be refetched and the sync isn't performed.

So that's the first thing you should check. Could you check that for me please? Also, if you first do: order.Customer = myCustomer; and then: order.CustomerID = newCustomerID; it will make order.Customer to become Nothing, because the fk field is changed and makes the entity to be dereferenced. Perhaps that's a cause as well.

Otis wrote:

You get a NULL in an FK field and a second time it does work. This is likely caused by the fact the entity on the PK side isn't re-fetchable and therefore synchronization fails. An entity isn't refetchable after a save in those cases where the PK should be generated inside the db, but isn't because the field is assumed to be an identity field or sequenced field, but isn't marked as such in the db (identity). The PK is therefore 0, can't be refetched and the sync isn't performed.

So that's the first thing you should check. Could you check that for me please?

The PK id field in both tblItem and tblItemAdjustment are both set as SQL Server identity fields and LLBLGen is reading that OK.

I might not have been clear when I made the initial post, but 99% of the time UnitOfWork2 is working fine but sometimes it fails. Reports from the field indicate that it is pretty rare for it to fail (one failure in a day is normal). There isn't any pattern to when it fails (ie: it appears random to me), but once it fails the first time it fails consistantly until you restart the application.

So for example, it will fail for a cetain entity (lets call it E1) once and then E1 will fail every time until you quit the application. Once you restart the application and do the exact same operation to E1 (even when the database has not been modified at all), it works OK.

Otis wrote:

Also, if you first do: order.Customer = myCustomer; and then: order.CustomerID = newCustomerID; it will make order.Customer to become Nothing, because the fk field is changed and makes the entity to be dereferenced. Perhaps that's a cause as well.

I remember running into this problem when I originally coded this section of the program (doing adjustment.ItemID = item.ID), but I quickly worked out that you were supposed to set the references rather than the actual database values (adjustment.Item = item). And I was most impressed to see LLBLGen figure out what to do on its own!

Anyway, do you have any other suggestions I can look into?

I've spoken with the client and this issue is happening regularly (between 2 and 4 times per day).

Anyone on the support team able to give me any further ideas as to what is going wrong?

Regards Murray

ibs wrote:

I've spoken with the client and this issue is happening regularly (between 2 and 4 times per day).

Will look into it. Sorry for the delay. Yesterday another scenario came to our attention which had a similar result. I'll check with the information you provided if your scenario is the same. Typically it comes down to:

Entities Order, Supplier and OrderItem. Order and OrderItem have a reference to Supplier, OrderItem references Order.

You add Order to UoW. You set Order.Supplier to given supplier instance (changed), you add new OrderItem to Order.OrderItems, set its Supplier reference to the same Supplier instance as Order, you then add ANOTHER new OrderItem to the Order.OrderItems, also with the same Supplier instance set.

You then REMOVE the second OrderItem from the OrderItems list. Then you commit the Uow.

It will fail. The reason for that is that the second orderitem is still referencing Supplier. And because that Supplier is saved when the Order is saved, (recursively) the second OrderItem is saved, though because it's not referencing an Order, it gets a NULL for that FK and that makes the save fail.

Not sure if that's the scenario you run into, but it might.

    Me._Entity.IsNew = True
    Me._Entity.PreviousAdjustment = Me._CurrentAdjustment
    Me._Entity.OrderOfAdjustments = Me._CurrentAdjustment.OrderOfAdjustments + 1

This code raises some eyebrows , especially the 'IsNew' flag modification. Why do you do that? I'm still in the guessing phase here, but it might be that some field's value isn't updated because it's 'the same' and therefore not flagged as 'changed' and will with an insert result in NULL. The lines above suggest that might be the case. There was a bug found recently with field value setting and new entities, where the fields weren't set to a new value in some situations. This might be one such situation.

Either way: AFTER the lines above, Me._Entity.Fields("PreviousAdjustmentID").IsChanged has to be true. If not, you'll get NULL in the db.

Thanks for the reply, Otis.

I noticed the other thread after I posted my last message. There are two relationships between Adjustments and Items, one is called ItemAdjustmentCurrent (Item - ItemAdjustment (m:1)) which links an item to the most recent adjustment (which should be the adjustment with maximum OrderOfAdjustments field for that itemID). The other is ItemAdjustmentPrevious (Item - ItemAdjustment (1:n)) which is a collection of all adjustments for an item.

Adjustments are never deleted or updated once added to the database (they are an audit trail of sorts), which is why I set the IsNew property to true (to force the issue).

I was talking this problem through with a colleague and he thought it might be because I'm setting the OrderOfAdjustments property to the same value (as you've mentioned). OrderOfAdjustments and PreviousAdjustmentID are both set as NOT Null in the database, and when the code fails, LLBLGen definately tries to insert a null which is why I get an exception (yay for DB constraints!).

This could be the case if the _CurrentAdjustment property is not the adjustment entity I'm expecting. I'll have to look into that more, but I'm starting to suspect this could be the case.

And (as usual) there are other, more pressing issues with this project at the moment. So I'll be taking a while to get that info for you .

Here's some extra info when the code succeeds (I'm going to have to build an automated test program to catch it when the code fails, but the more pressing issues have priority until next week ). The only change is adding some more tracing output statements:

2005-10-06 13:34:59Z: ItemAdjustment.Adjust() entering.
2005-10-06 13:34:59Z: Me._Entity.IsNew = True
2005-10-06 13:34:59Z: Me._Entity.PreviousAdjustmentID = 0
2005-10-06 13:34:59Z: Me._Entity.OrderOfAdjustments = 0
2005-10-06 13:34:59Z: Me._Entity.Fields("PreviousAdjustmentID").IsChanged = False
2005-10-06 13:34:59Z: Set Me._Entity properties.
2005-10-06 13:34:59Z: After Me.OpQueue.AddForSave(Me._Entity, True)
2005-10-06 13:34:59Z: Me._Entity.IsNew = True
2005-10-06 13:34:59Z: Me._Entity.PreviousAdjustmentID = 23275
2005-10-06 13:34:59Z: Me._Entity.PreviousAdjustment.ID = 23275
2005-10-06 13:34:59Z: Me._Entity.OrderOfAdjustments = 2
2005-10-06 13:34:59Z: Me._Entity.Fields("PreviousAdjustmentID").IsChanged = True
2005-10-06 13:34:59Z: item.ID = 17900
2005-10-06 13:34:59Z: item.ItemAdjustmentCurrent.ID = 0
2005-10-06 13:34:59Z: ItemAdjustment.Adjust() exiting.
2005-10-06 13:34:59Z: SaleLineItemLowLevel.UnPick() exiting.
Method Enter: CreateUpdateDQ(4)
Generated Sql query: 
    Query: UPDATE [dbo].[tblItem] SET [SaleLineItemID]=@SaleLineItemID,[SaleLineItemPickedOrder]=@SaleLineItemPickedOrder WHERE ( [dbo].[tblItem].[ID] = @ID1)
    Parameter: @SaleLineItemID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: <undefined value>.
    Parameter: @SaleLineItemPickedOrder : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: <undefined value>.
    Parameter: @ID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 17900.

Method Exit: CreateUpdateDQ(4)
Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [dbo].[tblItem].[ID] AS [ID],[dbo].[tblItem].[LeaseID] AS [LeaseID],[dbo].[tblItem].[LocationID] AS [LocationID],[dbo].[tblItem].[SubLocationID] AS [SubLocationID],[dbo].[tblItem].[RMA] AS [RMA],[dbo].[tblItem].[BarcodeNumber] AS [BarcodeNumber],[dbo].[tblItem].[SiteCode] AS [SiteCode],[dbo].[tblItem].[ItemTypeID] AS [ItemTypeID],[dbo].[tblItem].[ItemMakeID] AS [ItemMakeID],[dbo].[tblItem].[Model] AS [Model],[dbo].[tblItem].[SerialNumber] AS [SerialNumber],[dbo].[tblItem].[AssetNumber] AS [AssetNumber],[dbo].[tblItem].[CustomerAssetNumber] AS [CustomerAssetNumber],[dbo].[tblItem].[AlternateAssetNumber] AS [AlternateAssetNumber],[dbo].[tblItem].[MFGPartNo] AS [MFGPartNo],[dbo].[tblItem].[YearOfMFG] AS [YearOfMFG],[dbo].[tblItem].[HDDSize] AS [HDDSize],[dbo].[tblItem].[ProcessorID] AS [ProcessorID],[dbo].[tblItem].[Memory] AS [Memory],[dbo].[tblItem].[OpticalDriveID] AS [OpticalDriveID],[dbo].[tblItem].[Modem] AS [Modem],[dbo].[tblItem].[NetworkCard] AS [NetworkCard],[dbo].[tblItem].[FDD] AS [FDD],[dbo].[tblItem].[MonitorSizeID] AS [MonitorSizeID],[dbo].[tblItem].[ConditionCode] AS [ConditionCode],[dbo].[tblItem].[Comments] AS [Comments],[dbo].[tblItem].[SoftwareLicense] AS [SoftwareLicense],[dbo].[tblItem].[OperatingSystemID] AS [OperatingSystemID],[dbo].[tblItem].[ParentItemID] AS [ParentItemID],[dbo].[tblItem].[IsProcessed] AS [IsProcessed],[dbo].[tblItem].[DateProcessed] AS [DateProcessed],[dbo].[tblItem].[DateReceived] AS [DateReceived],[dbo].[tblItem].[SaleLineItemID] AS [SaleLineItemID],[dbo].[tblItem].[SaleLineItemPickedOrder] AS [SaleLineItemPickedOrder],[dbo].[tblItem].[StockAdjusted] AS [StockAdjusted],[dbo].[tblItem].[CurrentAdjustmentID] AS [CurrentAdjustmentID],[dbo].[tblItem].[DateAdjusted] AS [DateAdjusted],[dbo].[tblItem].[AdjustmentComment] AS [AdjustmentComment],[dbo].[tblItem].[GoodsReceivedDetailsID] AS [GoodsReceivedDetailsID],[dbo].[tblItem].[AssetMatchedAttempted] AS [AssetMatchedAttempted],[dbo].[tblItem].[Quarantined] AS [Quarantined],[dbo].[tblItem].[QuarantineDate] AS [QuarantineDate],[dbo].[tblItem].[QuarantineComment] AS [QuarantineComment],[dbo].[tblItem].[SpecialStockID] AS [SpecialStockID],[dbo].[tblItem].[ConvID] AS [ConvID],[dbo].[tblItem].[ConvSaleLineItemID] AS [ConvSaleLineItemID],[dbo].[tblItem].[ConvID_Syd] AS [ConvID_Syd],[dbo].[tblItem].[ConvID_Mel] AS [ConvID_Mel],[dbo].[tblItem].[ConvID_May] AS [ConvID_May],[dbo].[tblItem].[Rowguid] AS [Rowguid],[dbo].[tblItem].[TimestampSQLSrv] AS [TimestampSQLSrv],[dbo].[tblItem].[TimestampCreated] AS [TimestampCreated],[dbo].[tblItem].[TimestampModified] AS [TimestampModified] FROM [dbo].[tblItem]  WHERE ( [dbo].[tblItem].[ID] = @ID1)
    Parameter: @ID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 17900.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: CreateInsertDQ
Generated Sql query: 
    Query: INSERT INTO [dbo].[tblItemAdjustment] ([ItemID],[AdjustmentTypeCode],[PreviousAdjustmentID],[OrderOfAdjustments], [UserAdjustedID],[DateEntered],[Comment],[ShipmentDate]) VALUES (@ItemID,@AdjustmentTypeCode,@PreviousAdjustmentID,@OrderOfAdjustments, @UserAdjustedID,@DateEntered,@Comment,@ShipmentDate);SELECT @ID=SCOPE_IDENTITY()
    Parameter: @ID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 0.
    Parameter: @ItemID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 17900.
    Parameter: @AdjustmentTypeCode : AnsiString. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: IS.
    Parameter: @PreviousAdjustmentID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 23275.
    Parameter: @OrderOfAdjustments : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.
    Parameter: @UserAdjustedID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1002.
    Parameter: @DateEntered : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 6/10/2005 1:34:59 PM.
    Parameter: @Comment : String. Length: 1000. Precision: 0. Scale: 0. Direction: Input. Value: Line item deleted from sale S80.
    Parameter: @ShipmentDate : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: <undefined value>.

Method Exit: CreateInsertDQ
Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [dbo].[tblItemAdjustment].[ID] AS [ID],[dbo].[tblItemAdjustment].[ItemID] AS [ItemID],[dbo].[tblItemAdjustment].[AdjustmentTypeCode] AS [AdjustmentTypeCode],[dbo].[tblItemAdjustment].[PreviousAdjustmentID] AS [PreviousAdjustmentID],[dbo].[tblItemAdjustment].[OrderOfAdjustments] AS [OrderOfAdjustments],[dbo].[tblItemAdjustment].[UserAdjustedID] AS [UserAdjustedID],[dbo].[tblItemAdjustment].[DateEntered] AS [DateEntered],[dbo].[tblItemAdjustment].[WasProcessed] AS [WasProcessed],[dbo].[tblItemAdjustment].[Comment] AS [Comment],[dbo].[tblItemAdjustment].[ShipmentDate] AS [ShipmentDate] FROM [dbo].[tblItemAdjustment]  WHERE ( [dbo].[tblItemAdjustment].[ID] = @ID1)
    Parameter: @ID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 23276.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: CreateUpdateDQ(4)
Generated Sql query: 
    Query: UPDATE [dbo].[tblItem] SET [CurrentAdjustmentID]=@CurrentAdjustmentID WHERE ( [dbo].[tblItem].[ID] = @ID1)
    Parameter: @CurrentAdjustmentID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 23276.
    Parameter: @ID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 17900.

Method Exit: CreateUpdateDQ(4)

Ok, just post a new posting in this thread if you've more info. To make sure the fields always get inserted, you could set the IsChanged flags to true. So even if the field isn't 'changed' because it has the same value, you still get the fields to be inserted by manually setting the IsChanged flags. This is a workaround, but it's necessary if you manually change the IsNew flag to make an non-new entity act like a new entity.

I run an automated test on a development machine that did a pick then unpick sequence until i killed the process. It generated ~20000 adjustments without an issue. Which is good, because I know the code at least works, but bad because I wanted some tracing output of when it failed

I'm going to get some live tracing output from the client over the next few days. Hopefully that will nail down if the issue relates to the IsNew property or not.

Murray.

To keep you up to date, Otis, I've been waiting for a few days to get tracing output from the client. Seems that when tracing is enabled, the bug mysteriously becomes even less common. After getting the problem repeatedly for a week, once tracing was turned on they have not gotten it since.

All in all, this is most frustraiting.

Jackpot!!!

Here's (rather extended) tracing code of the bug in action:

[code] 2005-10-12 15:39:52Z: frmPickItem.btnPick_Click() entering.

<snip lots of PrefetchPath related selects>

2005-10-12 15:39:52Z: SaleLineItemLowLevel.Pick() entering. Method Enter: CreateSelectDQ (Paging) Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT COUNT(*) AS [CountOfPickedItem] FROM [dbo].[tblItem] WHERE ( [dbo].[tblItem].[SaleLineItemID] = @SaleLineItemID1) Parameter: @SaleLineItemID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3487. Method Exit: CreateSelectDQ Method Exit: CreateSelectDQ (Paging): no paging. Method Enter: CreateSelectDQ (Paging) Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[tblAdjustmentType].[Code] AS [Code],[dbo].[tblAdjustmentType].[AdjustmentTypeText] AS [AdjustmentTypeText],[dbo].[tblAdjustmentType].[Component] AS [Component],[dbo].[tblAdjustmentType].[SortOrder] AS [SortOrder],[dbo].[tblAdjustmentType].[RequiresComment] AS [RequiresComment],[dbo].[tblAdjustmentType].[RequiresShipmentDate] AS [RequiresShipmentDate],[dbo].[tblAdjustmentType].[IsInStock] AS [IsInStock],[dbo].[tblAdjustmentType].[MustBeTelstraAsset] AS [MustBeTelstraAsset],[dbo].[tblAdjustmentType].[AvailiableForSale] AS [AvailiableForSale],[dbo].[tblAdjustmentType].[IsSelectable] AS [IsSelectable],[dbo].[tblAdjustmentType].[Rowguid] AS [Rowguid] FROM [dbo].[tblAdjustmentType] WHERE ( [dbo].[tblAdjustmentType].[Code] = @Code1) Parameter: @Code1 : AnsiString. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: P. Method Exit: CreateSelectDQ Method Exit: CreateSelectDQ (Paging): no paging. Method Enter: CreateSelectDQ (Paging) Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[tblAdjustmentType].[Code] AS [Code],[dbo].[tblAdjustmentType].[AdjustmentTypeText] AS [AdjustmentTypeText],[dbo].[tblAdjustmentType].[Component] AS [Component],[dbo].[tblAdjustmentType].[SortOrder] AS [SortOrder],[dbo].[tblAdjustmentType].[RequiresComment] AS [RequiresComment],[dbo].[tblAdjustmentType].[RequiresShipmentDate] AS [RequiresShipmentDate],[dbo].[tblAdjustmentType].[IsInStock] AS [IsInStock],[dbo].[tblAdjustmentType].[MustBeTelstraAsset] AS [MustBeTelstraAsset],[dbo].[tblAdjustmentType].[AvailiableForSale] AS [AvailiableForSale],[dbo].[tblAdjustmentType].[IsSelectable] AS [IsSelectable],[dbo].[tblAdjustmentType].[Rowguid] AS [Rowguid] FROM [dbo].[tblAdjustmentType] WHERE ( [dbo].[tblAdjustmentType].[Code] = @Code1) Parameter: @Code1 : AnsiString. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: IS. Method Exit: CreateSelectDQ Method Exit: CreateSelectDQ (Paging): no paging. 2005-10-12 15:39:52Z: ItemAdjustment.Adjust() entering. 2005-10-12 15:39:52Z: Me._Entity.IsNew = True 2005-10-12 15:39:52Z: Me._Entity.ItemID = 23598 2005-10-12 15:39:52Z: Me._Entity.PreviousAdjustmentID = 0 2005-10-12 15:39:52Z: Me._Entity.OrderOfAdjustments = 0 2005-10-12 15:39:52Z: Me._Entity.Fields("PreviousAdjustmentID").IsChanged = False 2005-10-12 15:39:52Z: Me._Entity.Fields("OrderOfAdjustments").IsChanged = False 2005-10-12 15:39:52Z: Set Me._Entity properties. 2005-10-12 15:39:52Z: After Me.OpQueue.AddForSave(Me._Entity, True) 2005-10-12 15:39:52Z: Me._Entity.IsNew = True 2005-10-12 15:39:52Z: Me._Entity.ItemID = 23598 2005-10-12 15:39:52Z: Me._Entity.PreviousAdjustmentID = 3832 2005-10-12 15:39:52Z: Me._Entity.PreviousAdjustment.ID = 3832 2005-10-12 15:39:52Z: Me._Entity.OrderOfAdjustments = 1 2005-10-12 15:39:52Z: Me._Entity.Fields("PreviousAdjustmentID").IsChanged = True 2005-10-12 15:39:52Z: Me._Entity.Fields("OrderOfAdjustments").IsChanged = True 2005-10-12 15:39:52Z: item.ID = 23598 2005-10-12 15:39:52Z: item.ItemAdjustmentCurrent.ID = 0 2005-10-12 15:39:52Z: ItemAdjustment.Adjust() exiting. Method Enter: CreateSelectDQ (Paging) Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT MAX([dbo].[tblItem].[SaleLineItemPickedOrder]) AS [MaxOfPickedItemOrder] FROM [dbo].[tblItem] WHERE ( [dbo].[tblItem].[SaleLineItemID] = @SaleLineItemID1) Parameter: @SaleLineItemID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3487. Method Exit: CreateSelectDQ Method Exit: CreateSelectDQ (Paging): no paging. 2005-10-12 15:39:52Z: SaleLineItemLowLevel.Pick() exiting. Method Enter: CreateUpdateDQ(4) Generated Sql query: Query: UPDATE [dbo].[tblItem] SET [SaleLineItemID]=@SaleLineItemID,[SaleLineItemPickedOrder]=@SaleLineItemPickedOrder WHERE ( [dbo].[tblItem].[ID] = @ID1) Parameter: @SaleLineItemID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3487. Parameter: @SaleLineItemPickedOrder : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1. Parameter: @ID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 23598.

Method Exit: CreateUpdateDQ(4) Method Enter: CreateSelectDQ (Paging) Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[tblItem].[ID] AS [ID],[dbo].[tblItem].[LeaseID] AS [LeaseID],[dbo].[tblItem].[LocationID] AS [LocationID],[dbo].[tblItem].[SubLocationID] AS [SubLocationID],[dbo].[tblItem].[RMA] AS [RMA],[dbo].[tblItem].[BarcodeNumber] AS [BarcodeNumber],[dbo].[tblItem].[SiteCode] AS [SiteCode],[dbo].[tblItem].[ItemTypeID] AS [ItemTypeID],[dbo].[tblItem].[ItemMakeID] AS [ItemMakeID],[dbo].[tblItem].[Model] AS [Model],[dbo].[tblItem].[SerialNumber] AS [SerialNumber],[dbo].[tblItem].[AssetNumber] AS [AssetNumber],[dbo].[tblItem].[CustomerAssetNumber] AS [CustomerAssetNumber],[dbo].[tblItem].[AlternateAssetNumber] AS [AlternateAssetNumber],[dbo].[tblItem].[MFGPartNo] AS [MFGPartNo],[dbo].[tblItem].[YearOfMFG] AS [YearOfMFG],[dbo].[tblItem].[HDDSize] AS [HDDSize],[dbo].[tblItem].[ProcessorID] AS [ProcessorID],[dbo].[tblItem].[Memory] AS [Memory],[dbo].[tblItem].[OpticalDriveID] AS [OpticalDriveID],[dbo].[tblItem].[Modem] AS [Modem],[dbo].[tblItem].[NetworkCard] AS [NetworkCard],[dbo].[tblItem].[FDD] AS [FDD],[dbo].[tblItem].[MonitorSizeID] AS [MonitorSizeID],[dbo].[tblItem].[ConditionCode] AS [ConditionCode],[dbo].[tblItem].[Comments] AS [Comments],[dbo].[tblItem].[SoftwareLicense] AS [SoftwareLicense],[dbo].[tblItem].[OperatingSystemID] AS [OperatingSystemID],[dbo].[tblItem].[ParentItemID] AS [ParentItemID],[dbo].[tblItem].[IsProcessed] AS [IsProcessed],[dbo].[tblItem].[DateProcessed] AS [DateProcessed],[dbo].[tblItem].[DateReceived] AS [DateReceived],[dbo].[tblItem].[SaleLineItemID] AS [SaleLineItemID],[dbo].[tblItem].[SaleLineItemPickedOrder] AS [SaleLineItemPickedOrder],[dbo].[tblItem].[StockAdjusted] AS [StockAdjusted],[dbo].[tblItem].[CurrentAdjustmentID] AS [CurrentAdjustmentID],[dbo].[tblItem].[DateAdjusted] AS [DateAdjusted],[dbo].[tblItem].[AdjustmentComment] AS [AdjustmentComment],[dbo].[tblItem].[GoodsReceivedDetailsID] AS [GoodsReceivedDetailsID],[dbo].[tblItem].[AssetMatchedAttempted] AS [AssetMatchedAttempted],[dbo].[tblItem].[Quarantined] AS [Quarantined],[dbo].[tblItem].[QuarantineDate] AS [QuarantineDate],[dbo].[tblItem].[QuarantineComment] AS [QuarantineComment],[dbo].[tblItem].[SpecialStockID] AS [SpecialStockID],[dbo].[tblItem].[ConvID] AS [ConvID],[dbo].[tblItem].[ConvSaleLineItemID] AS [ConvSaleLineItemID],[dbo].[tblItem].[ConvID_Syd] AS [ConvID_Syd],[dbo].[tblItem].[ConvID_Mel] AS [ConvID_Mel],[dbo].[tblItem].[ConvID_May] AS [ConvID_May],[dbo].[tblItem].[Rowguid] AS [Rowguid],[dbo].[tblItem].[TimestampSQLSrv] AS [TimestampSQLSrv],[dbo].[tblItem].[TimestampCreated] AS [TimestampCreated],[dbo].[tblItem].[TimestampModified] AS [TimestampModified] FROM [dbo].[tblItem] WHERE ( [dbo].[tblItem].[ID] = @ID1) Parameter: @ID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 23598. Method Exit: CreateSelectDQ Method Exit: CreateSelectDQ (Paging): no paging. Method Enter: CreateInsertDQ Generated Sql query: Query: INSERT INTO [dbo].[tblItemAdjustment] ([AdjustmentTypeCode],[UserAdjustedID],[DateEntered],[Comment],[ShipmentDate]) VALUES (@AdjustmentTypeCode,@UserAdjustedID,@DateEntered,@Comment,@ShipmentDate);SELECT @ID=SCOPE_IDENTITY() Parameter: @ID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 0. Parameter: @AdjustmentTypeCode : AnsiString. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: P. Parameter: @UserAdjustedID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1004. Parameter: @DateEntered : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/10/2005 2:20:29 PM. Parameter: @Comment : String. Length: 1000. Precision: 0. Scale: 0. Direction: Input. Value: Sale status on invoice S952 changed to Picked. Parameter: @ShipmentDate : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: <undefined value>.

Method Exit: CreateInsertDQ An exception was thrown and published from the Microsoft .NET Framework.

1) Exception Information

Exception Type: IBS.Utilities.Exceptions.DefaultHandlerException MachineName: CROSSOVER4 CreatedDateTime: 12/10/2005 3:39:52 PM AppDomainName: CrossTracker.exe ThreadIdentityName: WindowsIdentityName: CROSSOVER\accounts Message: Exception from IBS Default Exception Handler. TargetSite: NULL HelpLink: NULL Source: NULL

2) Exception Information

Exception Type: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException QueryExecuted: Query: INSERT INTO [dbo].[tblItemAdjustment] ([AdjustmentTypeCode],[UserAdjustedID],[DateEntered],[Comment],[ShipmentDate]) VALUES (@AdjustmentTypeCode,@UserAdjustedID,@DateEntered,@Comment,@ShipmentDate);SELECT @ID=SCOPE_IDENTITY() Parameter: @ID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>. Parameter: @AdjustmentTypeCode : AnsiString. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: P. Parameter: @UserAdjustedID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1004. Parameter: @DateEntered : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/10/2005 2:20:29 PM. Parameter: @Comment : String. Length: 1000. Precision: 0. Scale: 0. Direction: Input. Value: Sale status on invoice S952 changed to Picked. Parameter: @ShipmentDate : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: <undefined value>.

Parameters: System.Data.SqlClient.SqlParameterCollection Message: An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'OrderOfAdjustments', table 'CrossTracker.dbo.tblItemAdjustment'; 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. TargetSite: Int32 Execute() HelpLink: NULL Source: SD.LLBLGen.Pro.ORMSupportClasses.NET11

StackTrace Information

at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntityCollection(IEntityCollection2 collectionToSave, Boolean refetchSavedEntitiesAfterSave, Boolean recurse) 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, IPredicateExpression updateRestriction, Boolean recurse) at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit) at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse) at IBS.CrossTracker.BLL.LineItem.Pick(SaleLineItemEntity lineItem, ItemEntity item, CurrentState appState) at IBS.CrossTracker.GUI.frmPickItem.btnPick_Click(Object sender, EventArgs e) in C:\projects\Crossover IT\DotNet\Code\GUI\Forms\frmpickitem.vb:line 566 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.PerformClick() at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData) at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData) at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData) at System.Windows.Forms.TextBoxBase.ProcessDialogKey(Keys keyData) at System.Windows.Forms.Control.PreProcessMessage(Message& msg) at System.Windows.Forms.ThreadContext.System.Windows.Forms.UnsafeNativeMethods+IMsoComponent.FPreTranslateMessage(MSG& msg) Method Enter: CreateSelectDQ (Paging) Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[tblSaleLineItem].[ID] AS [LineID],[dbo].[tblSaleLineItem].[Description] AS [Description],[dbo].[tblSaleLineItem].[Quantity] AS [Quantity],[dbo].[tblSaleLineItem].[UnitPriceIncTax] AS [UnitPriceIncTax],[dbo].[tblSaleLineItem].[UnitPriceExTax] AS [UnitPriceExTax],[dbo].[tblSaleLineItem].[UnitTax] AS [UnitTax],[dbo].[tblSaleLineItem].[TotalPriceIncTax] AS [TotalPriceIncTax],[dbo].[tblSaleLineItem].[TotalPriceExTax] AS [TotalPriceExTax],[dbo].[tblSaleLineItem].[TotalTax] AS [TotalTax],[dbo].[tblSaleLineItem].[TaxCode] AS [TaxCode] FROM [dbo].[tblSaleLineItem] WHERE ( [dbo].[tblSaleLineItem].[SaleID] = @SaleID1) ORDER BY [dbo].[tblSaleLineItem].[ID] ASC Parameter: @SaleID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 942. Method Exit: CreateSelectDQ Method Exit: CreateSelectDQ (Paging): no paging. Method Enter: CreateSelectDQ (Paging) Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[tblItem].[ID] AS [ItemID],[dbo].[tblItem].[SiteCode] AS [SiteCode],[dbo].[tblItem].[BarcodeNumber] AS [BarcodeNumber],[dbo].[tblItemMake].[MakeText] AS [Make],[dbo].[tblItem].[Model] AS [Model],[dbo].[tblItemCondition].[ItemConditionText] AS [ItemCondition] FROM (( [dbo].[tblItemMake] RIGHT JOIN [dbo].[tblItem] ON [dbo].[tblItemMake].[ID]=[dbo].[tblItem].[ItemMakeID]) INNER JOIN [dbo].[tblItemCondition] ON [dbo].[tblItemCondition].[Code]=[dbo].[tblItem].[ConditionCode]) WHERE ( [dbo].[tblItem].[SaleLineItemID] = @SaleLineItemID1) ORDER BY [dbo].[tblItem].[SaleLineItemPickedOrder] ASC Parameter: @SaleLineItemID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3487. Method Exit: CreateSelectDQ Method Exit: CreateSelectDQ (Paging): no paging. Method Enter: CreateSelectDQ (Paging) Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[tblItem].[ID] AS [ItemID],[dbo].[tblItem].[SiteCode] AS [SiteCode],[dbo].[tblItem].[BarcodeNumber] AS [BarcodeNumber],[dbo].[tblItemMake].[MakeText] AS [Make],[dbo].[tblItem].[Model] AS [Model],[dbo].[tblItemCondition].[ItemConditionText] AS [ItemCondition] FROM (( [dbo].[tblItemMake] RIGHT JOIN [dbo].[tblItem] ON [dbo].[tblItemMake].[ID]=[dbo].[tblItem].[ItemMakeID]) INNER JOIN [dbo].[tblItemCondition] ON [dbo].[tblItemCondition].

=[dbo].[tblItem].[ConditionCode]) WHERE ( [dbo].[tblItem].[SaleLineItemID] = @SaleLineItemID1) ORDER BY [dbo].[tblItem].[SaleLineItemPickedOrder] ASC
    Parameter: @SaleLineItemID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3487.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [dbo].[tblPermission].[ID] AS [ID],[dbo].[tblPermission].[PermissionName] AS [PermissionName],[dbo].[tblPermission].[SortOrder] AS [SortOrder],[dbo].[tblPermission].[Rowguid] AS [Rowguid] FROM [dbo].[tblPermission]  WHERE ( [dbo].[tblPermission].[PermissionName] = @PermissionName1)
    Parameter: @PermissionName1 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: Sale Administration.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [dbo].[tblUserPermission].[ID] AS [ID],[dbo].[tblUserPermission].[PermissionID] AS [PermissionID],[dbo].[tblUserPermission].[UserID] AS [UserID],[dbo].[tblUserPermission].[IsVisible] AS [IsVisible],[dbo].[tblUserPermission].[IsEnabled] AS [IsEnabled],[dbo].[tblUserPermission].[Rowguid] AS [Rowguid] FROM [dbo].[tblUserPermission]  WHERE ( [dbo].[tblUserPermission].[PermissionID] = @PermissionID1 And [dbo].[tblUserPermission].[UserID] = @UserID2)
    Parameter: @PermissionID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11.
    Parameter: @UserID2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1004.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [dbo].[tblSaleLineItem].[ID] AS [ID],[dbo].[tblSaleLineItem].[SaleID] AS [SaleID],[dbo].[tblSaleLineItem].[Description] AS [Description],[dbo].[tblSaleLineItem].[TaxCode] AS [TaxCode],[dbo].[tblSaleLineItem].[UnitPriceIncTax] AS [UnitPriceIncTax],[dbo].[tblSaleLineItem].[UnitPriceExTax] AS [UnitPriceExTax],[dbo].[tblSaleLineItem].[UnitTax] AS [UnitTax],[dbo].[tblSaleLineItem].[Quantity] AS [Quantity],[dbo].[tblSaleLineItem].[TotalPriceIncTax] AS [TotalPriceIncTax],[dbo].[tblSaleLineItem].[TotalPriceExTax] AS [TotalPriceExTax],[dbo].[tblSaleLineItem].[TotalTax] AS [TotalTax],[dbo].[tblSaleLineItem].[ConvID] AS [ConvID],[dbo].[tblSaleLineItem].[Rowguid] AS [Rowguid] FROM [dbo].[tblSaleLineItem]  WHERE ( [dbo].[tblSaleLineItem].[SaleID] = @SaleID1)
    Parameter: @SaleID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 942.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT COUNT(*) AS [CountOfPickedItem] FROM [dbo].[tblItem]  WHERE ( [dbo].[tblItem].[SaleLineItemID] = @SaleLineItemID1)
    Parameter: @SaleLineItemID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3487.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
2005-10-12 15:41:40Z: CrossTracker.NET Closed.

And the two methods that are at the core of this operation (where the Pick() method is calling Adjust()):

Friend Sub Adjust()
    ' Validation.
    If IsNothing(Me._Entity) OrElse IsNothing(Me._CurrentAdjustment) OrElse IsNothing(Me.GUIState) Then
        Throw New ArgumentException("You must set the Entity, CurrentAdjustment and GUIState properties before adjusting an item.")
    End If
    If Not Me.IsValid Then
        Throw New ValidationException(Me.ValidationMessage)
    End If
    If Me._Entity.ShipmentDate < Constants.SQLServerMinDateTime Then
        Me._Entity.SetNewFieldValue(DAL.ItemAdjustmentFieldIndex.ShipmentDate, Nothing)
    End If

    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": ItemAdjustment.Adjust() entering.")

    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.IsNew = " + Me._Entity.IsNew.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.ItemID = " + Me._Entity.ItemID.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.PreviousAdjustmentID = " + Me._Entity.PreviousAdjustmentID.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.OrderOfAdjustments = " + Me._Entity.OrderOfAdjustments.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.Fields(""PreviousAdjustmentID"").IsChanged = " + Me._Entity.Fields("PreviousAdjustmentID").IsChanged.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.Fields(""OrderOfAdjustments"").IsChanged = " + Me._Entity.Fields("OrderOfAdjustments").IsChanged.ToString())

    ' Now that the adjustment is valid, save it!
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Set Me._Entity properties.")
    Me._Entity.IsNew = True
    Me._Entity.PreviousAdjustment = Me._CurrentAdjustment
    Me._Entity.OrderOfAdjustments = Me._CurrentAdjustment.OrderOfAdjustments + 1
    Me.OpQueue.AddForSave(Me._Entity, True)

    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": After Me.OpQueue.AddForSave(Me._Entity, True)")
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.IsNew = " + Me._Entity.IsNew.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.ItemID = " + Me._Entity.ItemID.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.PreviousAdjustmentID = " + Me._Entity.PreviousAdjustmentID.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.PreviousAdjustment.ID = " + Me._Entity.PreviousAdjustment.ID.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.OrderOfAdjustments = " + Me._Entity.OrderOfAdjustments.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.Fields(""PreviousAdjustmentID"").IsChanged = " + Me._Entity.Fields("PreviousAdjustmentID").IsChanged.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": Me._Entity.Fields(""OrderOfAdjustments"").IsChanged = " + Me._Entity.Fields("OrderOfAdjustments").IsChanged.ToString())


    ' Update tblItem to set the current adjustment ID.
    Dim item As New DAL.EntityClasses.ItemEntity
    item.IsNew = False
    item.ID = Me._CurrentAdjustment.ItemID
    item.ItemAdjustmentCurrent = Me._Entity

    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": item.ID = " + item.ID.ToString())
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": item.ItemAdjustmentCurrent.ID = " + item.ItemAdjustmentCurrent.ID.ToString())

    Me.OpQueue.AddForSave(item)

    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": ItemAdjustment.Adjust() exiting.")
End Sub


Friend Sub Pick(ByVal item As DAL.EntityClasses.ItemEntity)
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": SaleLineItemLowLevel.Pick() entering.")

    If Not Me.IsValidToPick(item) Then
        Throw New ValidationException(Me.ValidationMessageToPick(item))
    End If

    ' Add adjustment to the item.
    Dim adjustmentLogic As New ItemAdjustment(Me.Adapter, Me.OpQueue, Me.GUIState)
    adjustmentLogic.Entity = New DAL.EntityClasses.ItemAdjustmentEntity
    adjustmentLogic.Entity.Item = item
    adjustmentLogic.Entity.UserAdjustedID = Me.GUIState.User.ID
    adjustmentLogic.Entity.AdjustmentTypeCode = Constants.AdjustmentStatus.SalePicked
    adjustmentLogic.Entity.DateEntered = DateTime.Now
    adjustmentLogic.Entity.Comment = PickAdjustmentComment + " " + Me._Sale.SiteCode + Me._Sale.InvoiceNumber.ToString()
    adjustmentLogic.CurrentAdjustment = item.ItemAdjustmentCurrent
    adjustmentLogic.Adjust()

    ' Get the last picked order.
    Dim fields As New DAL.HelperClasses.ResultsetFields(1)
    fields.DefineField(DAL.ItemFieldIndex.SaleLineItemPickedOrder, 0, "MaxOfPickedItemOrder", "", SD.LLBLGen.Pro.ORMSupportClasses.AggregateFunction.Max)
    Dim bucket As SD.LLBLGen.Pro.ORMSupportClasses.IRelationPredicateBucket = New SD.LLBLGen.Pro.ORMSupportClasses.RelationPredicateBucket
    bucket.PredicateExpression.Add(DAL.FactoryClasses.PredicateFactory.CompareValue(DAL.ItemFieldIndex.SaleLineItemID, SD.LLBLGen.Pro.ORMSupportClasses.ComparisonOperator.Equal, Me._Entity.ID))
    Dim dynamicList As New System.Data.DataTable
    Adapter.FetchTypedList(fields, dynamicList, bucket, 0, Nothing, True)
    Dim lastPickedOrder As Integer
    If dynamicList.Rows(0).Item("MaxOfPickedItemOrder") Is DBNull.Value Then
        lastPickedOrder = 0
    Else
        lastPickedOrder = CInt(dynamicList.Rows(0).Item("MaxOfPickedItemOrder"))
    End If

    ' All suffix to sale line item description if the item is not in good condition.
    If item.ConditionCode <> "GD" Then
        If Me._Entity.Description.IndexOf(Me.NonGoodItemSuffix) = -1 Then
            Me._Entity.Description = Me._Entity.Description + " - " + Me.NonGoodItemSuffix
            Me.OpQueue.AddForSave(Me._Entity)
        End If
    End If

    ' Pick the item.
    item.SetNewFieldValue(DAL.ItemFieldIndex.SaleLineItemID, Me.Entity.ID)
    item.SetNewFieldValue(DAL.ItemFieldIndex.SaleLineItemPickedOrder, lastPickedOrder + 1)
    Me.OpQueue.AddForSave(item)
    Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": SaleLineItemLowLevel.Pick() exiting.")
End Sub

Discrepencies:

• The tracing output indicates that the ItemID, PreviousAdjustmentID, and OrderOfAdjustments are all set to a non-default value. However these three properties are not written to the database.
• The tracing output indicates that the PreviousAdjustmentID, and OrderOfAdjustments field IsChanged properties are correctly being updated. However these two properties are not be written to the database.
• I've not seen issues with the ItemID property before. It's being set by setting the Item property (by reference) rather than directly setting the ItemID property (I can't remember why I did it this way) (not that this should matter)

Any ideas?

Regards, Murray.

That's a lot of stuff, I'll look into it. I'd also like to ask you to answer these questions, I couldn't find an answer for these:

Otis wrote:

    Me._Entity.IsNew = True
    Me._Entity.PreviousAdjustment = Me._CurrentAdjustment
    Me._Entity.OrderOfAdjustments = Me._CurrentAdjustment.OrderOfAdjustments + 1

This code raises some eyebrows , especially the 'IsNew' flag modification. Why do you do that? I'm still in the guessing phase here, but it might be that some field's value isn't updated because it's 'the same' and therefore not flagged as 'changed' and will with an insert result in NULL. The lines above suggest that might be the case. There was a bug found recently with field value setting and new entities, where the fields weren't set to a new value in some situations. This might be one such situation.

Either way: AFTER the lines above, Me._Entity.Fields("PreviousAdjustmentID").IsChanged has to be true. If not, you'll get NULL in the db.

Otis wrote:

That's a lot of stuff, I'll look into it.

I prefer the more is better approach when debugging!

Otis wrote:

I'd also like to ask you to answer these questions, I couldn't find an answer for these:

Otis wrote:

    Me._Entity.IsNew = True
    Me._Entity.PreviousAdjustment = Me._CurrentAdjustment
    Me._Entity.OrderOfAdjustments = Me._CurrentAdjustment.OrderOfAdjustments + 1

This code raises some eyebrows , especially the 'IsNew' flag modification. Why do you do that? I'm still in the guessing phase here, but it might be that some field's value isn't updated because it's 'the same' and therefore not flagged as 'changed' and will with an insert result in NULL. The lines above suggest that might be the case. There was a bug found recently with field value setting and new entities, where the fields weren't set to a new value in some situations. This might be one such situation.

I'm setting IsNew because adding an adjustment is always an insert; I don't want any chance that I end up modifying an adjustment. The business situation is that the adjustments are an audit trail, so I don't want any chance of someone deleting or modifying them. Having said that, the tracing log shows that IsNew was True before hand anyway, so it's a bit redundant .

Otis wrote:

Either way: AFTER the lines above, Me._Entity.Fields("PreviousAdjustmentID").IsChanged has to be true. If not, you'll get NULL in the db.

This little bit from the above tracing log should answer those questions. Everything before the Set Me._Entity properties. line is before the code you mentioned, and everything after the After Me.OpQueue.AddForSave(Me._Entity, True) until the item.ID = 23598 line is after the code you mentioned. Have a look at the source code in my previous post for the exact details.

2005-10-12 15:39:52Z: ItemAdjustment.Adjust() entering.
2005-10-12 15:39:52Z: Me._Entity.IsNew = True
2005-10-12 15:39:52Z: Me._Entity.ItemID = 23598
2005-10-12 15:39:52Z: Me._Entity.PreviousAdjustmentID = 0
2005-10-12 15:39:52Z: Me._Entity.OrderOfAdjustments = 0
2005-10-12 15:39:52Z: Me._Entity.Fields("PreviousAdjustmentID").IsChanged = False
2005-10-12 15:39:52Z: Me._Entity.Fields("OrderOfAdjustments").IsChanged = False
2005-10-12 15:39:52Z: Set Me._Entity properties.
2005-10-12 15:39:52Z: After Me.OpQueue.AddForSave(Me._Entity, True)
2005-10-12 15:39:52Z: Me._Entity.IsNew = True
2005-10-12 15:39:52Z: Me._Entity.ItemID = 23598
2005-10-12 15:39:52Z: Me._Entity.PreviousAdjustmentID = 3832
2005-10-12 15:39:52Z: Me._Entity.PreviousAdjustment.ID = 3832
2005-10-12 15:39:52Z: Me._Entity.OrderOfAdjustments = 1
2005-10-12 15:39:52Z: Me._Entity.Fields("PreviousAdjustmentID").IsChanged = True
2005-10-12 15:39:52Z: Me._Entity.Fields("OrderOfAdjustments").IsChanged = True
2005-10-12 15:39:52Z: item.ID = 23598
2005-10-12 15:39:52Z: item.ItemAdjustmentCurrent.ID = 0
2005-10-12 15:39:52Z: ItemAdjustment.Adjust() exiting.

(Still looking, will put things here when I think it's important)

I now notice one thing:

Public Shared Sub UnPick(ByVal lineItem As DAL.EntityClasses.SaleLineItemEntity, ByVal item As DAL.EntityClasses.ItemEntity, ByVal appState As BLL.CurrentState)
    Dim adapter As DAL.DatabaseSpecific.DataAccessAdapter = Utilities.GetDataAdapter()
    Dim UoW As New SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2
    Dim lineItemLogic As New BLL.SaleLineItemLowLevel(adapter, UoW, appState)
    lineItemLogic.Entity = lineItem
    lineItemLogic.Sale = lineItem.Sale

    adapter.StartTransaction(Data.IsolationLevel.RepeatableRead, "UnPickItem")
    Try
        ' Queue all DB operations.
        lineItemLogic.UnPick(item)

        ' Commit all DB operations.
        UoW.Commit(adapter)
        adapter.Commit()
    Finally
        adapter.Dispose()
    End Try
End Sub

Is your app a multi-threaded app (like a website, or service) or does it create background worker threads? Does GetDataAdapter create a new instance or get an existing instance? lineItem is passed to this static method, it's important that lineItem isn't shared among threads. In shared/static methods, local variables are local to the thread, though if multiple threads point to the same object, it can be one alters an object another thread also points to.

Not sure if it's applicable to your app, just noticed it. This could also be the reason why it works on your devsystem without problem but in production it can give problems.

Furthermore, I have a bit of a problem with placing everything in context. I see Adjust, Pick and Unpick (in 2 variants). Apparently Adjust is called somewhere as well as Pick, but I don't see that call from Unpick which does the commit. I see Adjust being called from Pick, though the tracing shows more stuff than what these methods do.

Also, the crash happens in Pick where a commit is done, though in the Pick code I see I don't see a Commit.

Otis wrote:

Is your app a multi-threaded app (like a website, or service) or does it create background worker threads?

It's a VB.NET Windows Forms app, it's not explicitly flagged as either STA or MTA threaded (I've seen this in C# projects, but don't see it in my VB.NET ones and I'm not sure what the default is). I don't create any threads myself, though I'm using Infragistics controls and Crystal Reports, and I don't know what they are doing behind the scenes. All work is done in the main WinForms user interface thread.

Otis wrote:

Does GetDataAdapter create a new instance or get an existing instance?

New instance. See the FTP site I've linked to below for full code to it.

Otis wrote:

lineItem is passed to this static method, it's important that lineItem isn't shared among threads. In shared/static methods, local variables are local to the thread, though if multiple threads point to the same object, it can be one alters an object another thread also points to.

The reason I'm using the pattern of code you quoted is to share exactly one adapter and one unit of work object between a variety of business level objects for exactly one database transaction (i was experiencing database deadlocks when sharing adapter objects and trying to reuse code). I hadn't thought about threading, and I know it can cause all sorts of problems from experience in the past, but I don't think it applies in this case (unless infragistics is modifying databound entity objects in another thread, but I think that's unlikely).

Not sure if it's applicable to your app, just noticed it. This could also be the reason why it works on your devsystem without problem but in production it can give problems.

Otis wrote:

Furthermore, I have a bit of a problem with placing everything in context. I see Adjust, Pick and Unpick (in 2 variants). Apparently Adjust is called somewhere as well as Pick, but I don't see that call from Unpick which does the commit. I see Adjust being called from Pick, though the tracing shows more stuff than what these methods do.

Also, the crash happens in Pick where a commit is done, though in the Pick code I see I don't see a Commit.

My apologies for not keeping the full context of the app in view (that's what you get with three tiered architectures!), I was too excited for actually getting some tracing logs to realise exactly what I was posting! This FTP link should give you a text file that details all the Pick and Unpick related methods from the user interface classes (starting with "frm") down to the business level classes (in the IBS.CrossTracker.BLL namespace). The OpQueue attribute in the SaleLineItemLowLevel class is the UnitOfWork2 instance that is passed in from Sales.SaleLineItem.Pick() and Sales.SaleLineItem.Unpick().

ftp: //60.240.44.174/AdjustmentBug2.txt

Sorry for such a long thread. Regards Murray.

I dl-ed the code, you can close the ftp now

In this code you do:

public class Sales.SaleLineItem
    Public Shared Sub Pick(ByVal lineItem As DAL.EntityClasses.SaleLineItemEntity, ByVal item As DAL.EntityClasses.ItemEntity, ByVal appState As BLL.CurrentState)
        Dim adapter As DAL.DatabaseSpecific.DataAccessAdapter = Utilities.GetDataAdapter()
        Dim UoW As New SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2
        Dim lineItemLogic As New BLL.SaleLineItemLowLevel(adapter, UoW, appState)
        lineItemLogic.Entity = lineItem     
        lineItemLogic.Sale = lineItem.Sale

        adapter.StartTransaction(Data.IsolationLevel.RepeatableRead, "PickItem")
        Try
            ' Queue all DB operations.
            lineItemLogic.Pick(item)     '<----- pass in received item object

            ' Commit all DB operations.
            UoW.Commit(adapter)
            adapter.Commit()
        Finally
            adapter.Dispose()
        End Try
    End Sub
end class

and in Pick you do:

friend class SaleLineItemLowLevel   
    Friend Sub Pick(ByVal item As DAL.EntityClasses.ItemEntity)
        Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": SaleLineItemLowLevel.Pick() entering.")

        If Not Me.IsValidToPick(item) Then
            Throw New ValidationException(Me.ValidationMessageToPick(item))
        End If

        ' Add adjustment to the item.
        Dim adjustmentLogic As New ItemAdjustment(Me.Adapter, Me.OpQueue, Me.GUIState)
        adjustmentLogic.Entity = New DAL.EntityClasses.ItemAdjustmentEntity
        adjustmentLogic.Entity.Item = item     '<------------ A
        adjustmentLogic.Entity.UserAdjustedID = Me.GUIState.User.ID
        adjustmentLogic.Entity.AdjustmentTypeCode = Constants.AdjustmentStatus.SalePicked
        adjustmentLogic.Entity.DateEntered = DateTime.Now
        adjustmentLogic.Entity.Comment = PickAdjustmentComment + " " + Me._Sale.SiteCode + Me._Sale.InvoiceNumber.ToString()
        adjustmentLogic.CurrentAdjustment = item.ItemAdjustmentCurrent
        adjustmentLogic.Adjust()

Please watch line A.

Ok, in Adjust you do:

friend class ItemAdjustment
    Friend Sub Adjust()
        ' Validation.
        If IsNothing(Me._Entity) OrElse IsNothing(Me._CurrentAdjustment) OrElse IsNothing(Me.GUIState) Then
            Throw New ArgumentException("You must set the Entity, CurrentAdjustment and GUIState properties before adjusting an item.")
        End If
        If Not Me.IsValid Then
            Throw New ValidationException(Me.ValidationMessage)
        End If
        If Me._Entity.ShipmentDate < Constants.SQLServerMinDateTime Then
            Me._Entity.SetNewFieldValue(DAL.ItemAdjustmentFieldIndex.ShipmentDate, Nothing)
        End If

        Diagnostics.Trace.WriteLine(DateTime.Now.ToString("u") + ": ItemAdjustment.Adjust() entering.")

        ' Now that the adjustment is valid, save it!
        Me._Entity.IsNew = True
        Me._Entity.PreviousAdjustment = Me._CurrentAdjustment
        Me._Entity.OrderOfAdjustments = Me._CurrentAdjustment.OrderOfAdjustments + 1
        Me.OpQueue.AddForSave(Me._Entity, True)

        ' Update tblItem to set the current adjustment ID.
        Dim item As New DAL.EntityClasses.ItemEntity
        item.IsNew = False
        item.ID = Me._CurrentAdjustment.ItemID
        item.ItemAdjustmentCurrent = Me._Entity    '<--------- B
        Me.OpQueue.AddForSave(item)

After line A, the passed in Item object (in SalesLineItem.Pick())'s ItemAdjustmentCurrent = set to the new ItemAdjustment created in the line before.

Then, at line B, Me._Entity.ItemAdjustment.Item is reset to another Item.

IMHO the passed in item to SalesLineItem.Pick can lose its ItemAdjustment along the way, or receive a new one at line B.

Perhaps this is all bogus, but that's what I can make of it. As it's a very undeterministic error, its hard to pinpoint to a faulthy piece of code, though the crash trace shows clearly that it goes wrong when in Item the following fields are changed: SaleLineItemID and SaleLineItemPickedOrder, so somewhere along that codepath, the Adjustment gets reset to a new one apparently, for example through the construction I described earlier.

It will take for me a tremendous amount of time to weed through every line of code but it might help you find the issue and better: setup a test which does make these fields altered and see if that fails always, so you can setup a deterministic test, instead of this undeterministic test...

I'm going to change any assignments to setting basic entity values rather than the more complex reference property (eg: don't set adjustment.Item but adjustment.ItemID). I'm doing an update at the client's end tonight, so I'll let you know if any further exceptions occur.

Unfortunatly, I'm not going to be able to put together a deterministic test (as much as I'd like to) because of the time involved. The whole undeterministic nature of the error is frustraiting for me as well. But leave this until for a few days until I get feedback from the latest changes.

A quick update on the state of this issue:

Setting foreign keys by value (ie: setting ItemID instead of Item) has, at the very least, dramitically reduced the number of occurances of this problem. I have seen it happen once on my development computer (unfortunately, I didn't have tracing enabled at the time ), and no times in the field.

I'll be enabling automatic emailing of exceptions back to me in an update tonight, so if there are no further occurances of it through next week, I'll consider the problem solved.

Ok, well, apparently there is some issue somewhere, but as it's not deterministic, it's indeed too hard to track down, and if fk field values work, for now I agree it's the best I think.