hi

i am using LBLGen2.5. I have a table structure

1. Employee (PK=EmployeeID)
2. EmployeeAssistant

EmployeeAssistant has 2 columns which are linked with Employee.EmployeeId column using (PK-FK) and these columns make a composite key. please see tables below

-------------------------------------------------------1------------------------------------------------ CREATE TABLE [dbo].[Employee]( [EmployeeId] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [EmployeeId] ASC )

) ON [PRIMARY]

-------------------------------------------------------2--------------------------------------------------- CREATE TABLE [dbo].[EmployeeAssistant]( [EmployeeId] [int] NOT NULL, [EmployeeAssistantId] [int] NOT NULL,

CONSTRAINT [PK_EmployeeAssistant] PRIMARY KEY CLUSTERED ( [EmployeeId] ASC, [AssistantEmployeeId] ASC ) ) ON [PRIMARY]

GO ALTER TABLE [dbo].[EmployeeAssistant] WITH CHECK ADD CONSTRAINT [FK_EmployeeAssistant_Employee_For_Assistant] FOREIGN KEY([AssistantEmployeeId]) REFERENCES [dbo].[Employee] ([EmployeeId]) GO ALTER TABLE [dbo].[EmployeeAssistant] CHECK CONSTRAINT [FK_EmployeeAssistant_Employee_For_Assistant] GO ALTER TABLE [dbo].[EmployeeAssistant] WITH CHECK ADD CONSTRAINT [FK_EmployeeAssistant_Employee_For_Employee] FOREIGN KEY([EmployeeId]) REFERENCES [dbo].[Employee] ([EmployeeId]) GO

ALTER TABLE [dbo].[EmployeeAssistant] CHECK CONSTRAINT [FK_EmployeeAssistant_Employee_For_Employee]

I want to add an employee assistant and this assitant assits multiple employees.

First i create a collection of EmployeeAssistant Enitity and add this collection in Employee Entity

My LLBLGEN Code:

EmployeeEntity Employee = new EmployeeEntity(); Employee.IsNew = true; Employee.Name=”XYZ”;

foreach (ListItem item in lstEmployees.Items) { if (item.Selected) {

                EmployeeAssistantEntity EmployeeAssistant = new EmployeeAssistantEntity();
                EmployeeAssistant.IsNew = true;
                EmployeeAssistant.EmployeeId = int.Parse(item.Value);
                Employee.EmployeeAssistant.Add(EmployeeAssistant);
            }
        }

when i call adapter.SaveEntity(Employee), System throws an exception Cannot insert the value NULL into column 'AssistantEmployeeId', table 'dbo.EmployeeAssistant'; column does not allow nulls. INSERT fails. The statement has been terminated

How to save an enitity with collection with such a composite keys relation?

Thanks

1- You should pass true as the recurse parameter to the Adapter Save method.

2- You don't have to set IsNew = true, it's set by default.

                EmployeeAssistant.EmployeeId = int.Parse(item.Value);

3- Are you sure the above line of code sets the EMployeeId to a valid value?

                Employee.EmployeeAssistant.Add(EmployeeAssistant);

4- Which relation is used by: Employee.EmployeeAssistant?

Employee.EmployeeId : EmployeeAssistant.EmployeeId OR Employee.EmployeeId : EmployeeAssistant.AssistantEmployeeId

?

Hi

I am using this Employee.EmployeeId : EmployeeAssistant.EmployeeId relationship in the above scenario.

Because i want to select the Employees from the Listbox for the new Assistant and add those employees into a EmployeeAssistantEntity Collection and then save.

If i do the other operation then error doesn't exist. For example foreach (ListItem item in lstEmployeeAssistants.Items) { if (item.Selected) { EmployeeAssistantEntity EmployeeAssistant = new EmployeeAssistantEntity(); ** EmployeeAssistant.AssistantEmployeeId = int.Parse(item.Value);** Employee.EmployeeAssistant.Add(EmployeeAssistant);

        }
    }

In the above case, we are assigning Assitants to a new Employee. Thats why we set this AssistantEmployeeId field. But if set AssociateId as i mentioned in the previous post, then an error occured.

There are two Employee Roles 1. Employee (General) (FK-EmployeeAssistant.EmployeeId) 2. Assistant - (FK-EmployeeAssistant.AssistantEmployeeId)

If we want to assign Assitants to an Employee (General), then it works fine using **Employee.EmployeeId : EmployeeAssistant.AssistantEmployeeId **

But if we want to assign Employees (General) to an Assistant, then an error occured during save using this relation Employee.EmployeeId : EmployeeAssistant.EmployeeId

I am using adapter.saveEntity(Employee, true) now.

I want to execute both cases successfully but only one case is running fine. Please advise.

Thanks

Hi I am waiting for the solution about the above mentioned issue.

Thanks

Walaa wrote:

4- Which relation is used by: Employee.EmployeeAssistant?

Employee.EmployeeId : EmployeeAssistant.EmployeeId OR Employee.EmployeeId : EmployeeAssistant.AssistantEmployeeId

You didn't answer my above question. And I think that's the core of the problem.

Since EmployeeAssistant entity has 2 relations with the Employee entity, then most probably there are 2 fields (collections) generated inside the Employee Entity to represent the 2 relations.

Most probably they are called: 1- Employee.EmployeeAssistant: This seems to be mapping the following relation Employee.EmployeeId : EmployeeAssistant.EmployeeId Which for an employee, it returns all his assistants.

2- Employee.EmployeeAssistant_ (If you haven't renamed it in the designer) This seems to be mapping the following relation Employee.EmployeeId : EmployeeAssistant.AssistantEmployeeId Which for an Assistant employee, it returns all the employees he assists.

The first piece of code doesn't work since you set the EmployeeId then you use the wrong collection (which sets the same FK) too, not the other one.

So you should be using one of the following pieces of code:

1-

EmployeeEntity Employee = new EmployeeEntity();
Employee.Name=”XYZ”;

foreach (ListItem item in lstEmployees.Items)
            {
                if (item.Selected)
                {
                    
                    EmployeeAssistantEntity EmployeeAssistant = new EmployeeAssistantEntity();
                    EmployeeAssistant.EmployeeId = int.Parse(item.Value);
                    
                    Employee.EmployeeAssistant_.Add(EmployeeAssistant);
                }
            }

2-

EmployeeEntity Employee = new EmployeeEntity();
Employee.Name=”XYZ”;

foreach (ListItem item in lstEmployees.Items)
            {
                if (item.Selected)
                {
                    
                    EmployeeAssistantEntity EmployeeAssistant = new EmployeeAssistantEntity();
                    EmployeeAssistant.AssistantEmployeeId = int.Parse(item.Value);

                    Employee.EmployeeAssistant.Add(EmployeeAssistant);
                }
            }

Hi

Thanks for your reply. Now it works fine by using this

EmployeeEntity Employee = new EmployeeEntity(); Employee.Name=”XYZ”;

foreach (ListItem item in lstEmployees.Items) { if (item.Selected) {

                EmployeeAssistantEntity EmployeeAssistant = new EmployeeAssistantEntity();
                EmployeeAssistant.EmployeeId = int.Parse(item.Value);

                Employee.EmployeeAssistant_.Add(EmployeeAssistant);
            }
        }

Thanks Again