I'm getting a runtime error when trying to save an entity in a m:n relationship and I can't work out what the issue is. My structure is as follows:

Project 1:m Subproject Subproject 1:m SubprojectCustomer SubprojectCustomer n:1 Customer

I'm trying to add a new Project and Subproject and then associate the new subproject with an existing customer. I do this as follows:

                         currentProject = New MyProjectEntity
                        currentProject.Name = "Testing"

                        defaultSubproject = New MySubprojectEntity
                        defaultSubproject.Name = "Default Subproject"

                        currentProject.Subprojects.Add(defaultSubproject)

                    Dim currentCustomer As New MyCustomerEntity(currentCustomerNumber)
                    Dim newSubprojectCustomer As New MySubprojectCustomerEntity

                    newSubprojectCustomer.Customer = currentCustomer
                    newSubprojectCustomer.Subproject = defaultSubproject

                            currentProject.Save()

The Save method of the ProjectEntity recursively saves and autoCommits. The project and subproject inserts are generated fine but the subprojectCustomer insert errors in CreateInsertDQ.

Can you give me any guidance as to what I'm doing wrong? Thanks.

Your code looks good. I created a test database like you have stated and can't reproduce the problem. Are there any fields that should be defined for the SubprojectCustomer that are not given a value?

The exception is thrown because no fields are changed, and it has a sequenced/autonumber PK which then results in a no-field insert query, which will fail, so the DQE throws an exception.

As the entity should have changed fields (the FK fields), these aren't set properly, most likely because the related entities couldn't be read back due to a PK value that was expected to be a sequenced field, but it doesn't have a sequence assigned to the PK field. Could you please check if the PK fields have sequences assigned to them?

Otis wrote:

As the entity should have changed fields (the FK fields), these aren't set properly, most likely because the related entities couldn't be read back due to a PK value that was expected to be a sequenced field, but it doesn't have a sequence assigned to the PK field. Could you please check if the PK fields have sequences assigned to them?

You were correct in your assumption that the PK field is based on a sequence, and this sequence was missing on the SubProjectCustomer Entity. Subproject PK is also based on a sequence, however this one was in place and working. Customer PK is set elsewhere and is a String.

I set the SubprojectCustomer PK to the sequence, however this did not fix the problem. As far as I can tell the Subproject and Customer parents are being set correctly (although the Customer and Subproject Id fields are not populated after setting) but I'm still getting the error.

ChicagoKiwi wrote:

Otis wrote:

As the entity should have changed fields (the FK fields), these aren't set properly, most likely because the related entities couldn't be read back due to a PK value that was expected to be a sequenced field, but it doesn't have a sequence assigned to the PK field. Could you please check if the PK fields have sequences assigned to them?

You were correct in your assumption that the PK field is based on a sequence, and this sequence was missing on the SubProjectCustomer Entity. Subproject PK is also based on a sequence, however this one was in place and working. Customer PK is set elsewhere and is a String.

I set the SubprojectCustomer PK to the sequence, however this did not fix the problem. As far as I can tell the Subproject and Customer parents are being set correctly (although the Customer and Subproject Id fields are not populated after setting) but I'm still getting the error.

The SubprojectCustomerEntity.OnEntityAfterSave routine will be called when Customer and when subproject are saved. In there, the FK's in SubprojectCustomerEntity are set with the PK values of Customer and subproject.

Could you set a breakpoint at the first line in that routine and see if it actually gets called and if the FK fields are indeed set to a value?

Otis wrote:

The SubprojectCustomerEntity.OnEntityAfterSave routine will be called when Customer and when subproject are saved. In there, the FK's in SubprojectCustomerEntity are set with the PK values of Customer and subproject.

Could you set a breakpoint at the first line in that routine and see if it actually gets called and if the FK fields are indeed set to a value?

The customer is not being saved as there are no changes to the customer. Not sure if this makes a difference, but I thought I'd mention it.

OnEntitySave is being hit, with the sender being the SubprojectEntity (i.e the immediate parent). entitySyncInfos has only one item, which strikes me a strange, although I can't inspect the contents to find out what they are.

I thought, because the function uses a name compare that setting the SubprojectCustomer.Subproject to a _My_SubprojectEntity parent may have been causing the problem, but that doesn't appear to be the case, as I changed it to a SubprojectEntity and I still have the same issue.

After my previous post I got a brain flash. Your reference to saving the customer sparked the thought that the error may not be in the save of SubProjectCustomer (which I'd wrongly assumed it was). To get the Customer entity for the SubprojectCustomer I had the following code:

                     Dim currentCustomer As New CustomerEntity(currentCustomerNumber)

What I was missing was

                     currentCustomer.IsNew = False

so the customer was trying to be saved.

Thanks for your help and sorry for the wasted time.

Glad it's solved!