I have a strange problem and I will try to give as much detail as possible. I am using LLBLGen 2.6 with file version 2.6.09.0616 with Sybase 11. Here is my scenario -

I have an employee form and on that form, there is a field called "Badge Number". Badge Number must be unique and there is a unique constraint on the database to support this. I have an error handler factory to handle a unique constraint error. When I try to save an entity that violates this unique constraint, I get an error message indicating that the violation has occurred. My application traps this error and hands a nice error message back to the user. This all works fine. The user is alerted that they need to change the badge number. I then navigate away from this employee (not saving their data) and navigate to another employee which loads the form with another employee data. I know that the fetch of the employee entity works because the form displays the correct employee data. I then make a change not related to the badge number and try to save it and the same unique constraint violation fires for this employee. Looking at the SQL detail generated, it says that it is trying to do an update for the previous employee and update their badge number to the failed one from before. I know that the correct employee entity is the one being saved because I can stop it in the debugger right before it saves and see it by hovering over the entity it is the new employee.

I can't figure out how it is "hanging on" to the invalid data from the prior attempt. I have tried the following:

adding this line of code to the employeeLoad method

Employee = new EmployeeEntity();

to insure that I am starting with a clean slate, but that doesn't help. It's like I have the new employee with some "hangers on" of the old data. To "prove" that I am in fact executing sql from the failed transaction I did the following:

I added code to my project that sets Employee.BadgeNumber = 0 when the attempt to save fails with a unique constraint violation. Then I tried it out:

Navigate to employee one Attempt to change their badge number to 1 Receive Unique Constraint error message Navigate to employee two (fetch new EmployeeEntity) Make change to Employee two Save Employee Two

I can retrieve the first entity where the unique constraint error occurred and their badge number is now zero (it was previously six and I was trying to change it to one). Also, the 2nd employee also reflects the change that I made. It is like it is "combining" the sql.

I initially didn't have my save entity logic encapsulated in a transaction. That has been done now. I have insured that I am disposing of my adapter after the failed save attempt (it is enclosed in a "using" block).

This is the SQL generated from the save event in my app

Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [DBA].[EmployeeCustomFields] ([CustomFieldID], [EmployeeID], [FieldValue], [CompanyID])  VALUES (?, ?, ?, ?)
    Parameter: @CustomFieldId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @EmployeeId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 60.
    Parameter: @FieldValue : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".
    Parameter: @CompanyId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [DBA].[EmployeeCustomFields] ([CustomFieldID], [EmployeeID], [FieldValue], [CompanyID])  VALUES (?, ?, ?, ?)
    Parameter: @CustomFieldId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 2.
    Parameter: @EmployeeId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 60.
    Parameter: @FieldValue : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".
    Parameter: @CompanyId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [DBA].[EmployeeCustomFields] ([CustomFieldID], [EmployeeID], [FieldValue], [CompanyID])  VALUES (?, ?, ?, ?)
    Parameter: @CustomFieldId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 3.
    Parameter: @EmployeeId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 60.
    Parameter: @FieldValue : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".
    Parameter: @CompanyId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [DBA].[EmployeeCustomFields] ([CustomFieldID], [EmployeeID], [FieldValue], [CompanyID])  VALUES (?, ?, ?, ?)
    Parameter: @CustomFieldId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 4.
    Parameter: @EmployeeId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 60.
    Parameter: @FieldValue : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".
    Parameter: @CompanyId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [DBA].[EmployeeCustomFields] ([CustomFieldID], [EmployeeID], [FieldValue], [CompanyID])  VALUES (?, ?, ?, ?)
    Parameter: @CustomFieldId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @EmployeeId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 59.
    Parameter: @FieldValue : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".
    Parameter: @CompanyId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [DBA].[EmployeeCustomFields] ([CustomFieldID], [EmployeeID], [FieldValue], [CompanyID])  VALUES (?, ?, ?, ?)
    Parameter: @CustomFieldId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 2.
    Parameter: @EmployeeId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 59.
    Parameter: @FieldValue : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".
    Parameter: @CompanyId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [DBA].[EmployeeCustomFields] ([CustomFieldID], [EmployeeID], [FieldValue], [CompanyID])  VALUES (?, ?, ?, ?)
    Parameter: @CustomFieldId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 3.
    Parameter: @EmployeeId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 59.
    Parameter: @FieldValue : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".
    Parameter: @CompanyId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [DBA].[EmployeeCustomFields] ([CustomFieldID], [EmployeeID], [FieldValue], [CompanyID])  VALUES (?, ?, ?, ?)
    Parameter: @CustomFieldId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 4.
    Parameter: @EmployeeId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 59.
    Parameter: @FieldValue : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".
    Parameter: @CompanyId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: CreateUpdateDQ(4)
Method Enter: CreateSingleTargetUpdateDQ(4)
Generated Sql query: 
    Query: UPDATE [DBA].[Employee] SET [DepartmentID]=?,[PayclassID]=? WHERE ( [DBA].[Employee].[CompanyID] = ? AND [DBA].[Employee].[EmployeeID] = ?)
    Parameter: @DepartmentId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 232.
    Parameter: @PayclassId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @CompanyId1 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @EmployeeId2 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 59.


Method Exit: CreateSingleTargetUpdateDQ(4)
Method Exit: CreateUpdateDQ(4)
Method Enter: CreateUpdateDQ(4)
Method Enter: CreateSingleTargetUpdateDQ(4)
Generated Sql query: 
    Query: UPDATE [DBA].[Employee] SET [BadgeNumber]=?,[PayclassID]=? WHERE ( [DBA].[Employee].[CompanyID] = ? AND [DBA].[Employee].[EmployeeID] = ?)
    Parameter: @BadgeNumber : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @PayclassId : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @CompanyId1 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @EmployeeId2 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 60.


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
A first chance exception of type 'Lathem.Payclock.DataAccess.UniquenessException' occurred in Lathem.Payclock.DataAccess.dll

As you can see, there are two EmplloyeeID's in this and there should only be one. 59 is the correct ID and 60 is the ID from another employee (the one that the save failed on because of the unique constraint error.

I am completely baffled as to why this is happening and I am hoping that you can shed some light.

Thanks,

Allen

Hi Allen,

It must be something related to some references on the collection or the way you changed the values or remove/add entities to the collection. Please post the relevant code snippet where you are doing such things (fetch, changes, save...)