Hi,

I am working with two tables which have a 1:1 relationship. The tables are 'Employee' - stores personal details - and 'LoginDetails' - stores user name, password and system role details.

A bit of technical background - I am using LLBLGen Pro v1.0.2004.1 with an Access 2000 (format) database. The generator template used is the "Two class scenario (Full) Vs.Net 2003 (1.0.2004.1.053104)"

This is the code that I am using to update the two tables:

' Employee entity
Dim employee As EmployeeEntity = New EmployeeEntity(_userK)

employee.FullName = _fullName
employee.RoleFk = _roleFK

employee.Save()


' Login details entity
Dim logindetails As LoginDetailsEntity = employee.LoginDetails

logindetails.LoginName = _userName
logindetails.Password = _password

' Save
logindetails.Save()

To me this looks pretty straight forward, however no matter what I do it doesn't update the login details. The employee details do get updated correctly whenever there is a change made.

When I set a breakpoint and step through the code I can see the values being assigned, I can even check that the data 'isDirty()', but it still doesn't update.

If anyone can offer any advice or sympathise with similar situations or possible workarounds that would be more than appreciated.

In advance, thanks!!

Tim

techiehippy wrote:

' Employee entity
Dim employee As EmployeeEntity = New EmployeeEntity(_userK)

employee.FullName = _fullName
employee.RoleFk = _roleFK

employee.Save()

' Login details entity
Dim logindetails As LoginDetailsEntity = employee.LoginDetails

logindetails.LoginName = _userName
logindetails.Password = _password

' Save
logindetails.Save()

You're relying on the new entity returned by employee.LoginDetails, but that entity isn't set as the reference. Do this instead:

Dim employee As New EmployeeEntity(_userK)
employee.FullName = _fullName
employee.RoleFk = _roleFK
Dim logindetails As New LoginDetailsEntity()
logindetails.LoginName = _userName
logindetails.Password = _password
employee.LoginDetails = loginDetails
employee.Save(True)

It also saves both in a transaction, which makes it more failsafe

Frans,

Thanks for your help and quick reply. Unfortunately I am now getting an error message from this. The error occurs when the employee.Save(True) function is called.

The message is:

"An exception was caught during the execution of an action query: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

One thing I noticed is that logindetails.isNew = True. Should this equal False so that an update can occur? Although even when I change this property to False it doesn't get the error but it also doesn't update the data.

In the database there is a relationship between employee.employeeK and logindetails.employeeFK. Employee.employeeK is defined as a primary key and logindetails.employeeFK is defined with a unique constraint -- I assume this is how you set up a true 1:1 relationship in Access?

Any thoughts from here?

Thanks again.

techiehippy wrote:

Frans,

Thanks for your help and quick reply. Unfortunately I am now getting an error message from this. The error occurs when the employee.Save(True) function is called.

The message is:

"An exception was caught during the execution of an action query: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

One thing I noticed is that logindetails.isNew = True. Should this equal False so that an update can occur? Although even when I change this property to False it doesn't get the error but it also doesn't update the data.

In the database there is a relationship between employee.employeeK and logindetails.employeeFK. Employee.employeeK is defined as a primary key and logindetails.employeeFK is defined with a unique constraint -- I assume this is how you set up a true 1:1 relationship in Access?

Yes, though just 1 logindetails object per employee can be stored in that table. Your results show that you try to insert a row in the logindetails table which is already there, you violate the unique constraint as it seems.

My example creates 2 new objects and saves them both, which was my understanding of what you wanted to do. If you don't want that, please explain what you want to do so I can give you a better example

The error is occurring when trying to update the data in the logindetails table (like changing a password).

I made the assumption that having an instance of the employee entity and then making changes to the data in the related logindetails entity would in effect cause an update and not try to insert a new record.

This O/R stuff is really great but I am still trying to get my head around some of the concepts (simple as they may be!). Do you know of any good articles that could help explain this to me in greater detail? Frans, when did you first discover this model and how did you learn about it?

techiehippy wrote:

The error is occurring when trying to update the data in the logindetails table (like changing a password).

I made the assumption that having an instance of the employee entity and then making changes to the data in the related logindetails entity would in effect cause an update and not try to insert a new record.

Back to square one. I now indeed see you try to update 2 existing entities, not create 2 new ones. My mistake, my example did that.

Your line here: Dim logindetails As LoginDetailsEntity = employee.LoginDetails will fetch the LoginDetails for the entity with the passed in PK for employee. You stated that loginDetails is new. This means that there isn't a LoginDetails object in the database for the employee with pk _userK. Which is a bit odd, as when you add a new one, it fails due to a unique constraint error...

When you fetch logindetails using the unique constraint (FetchUsingUCEmployeeK or something) and pass in _userK, does it fetch an entity or not?

1:1 relations did have some problems in the past, where the code generator made mistakes what to do when a related entity was referenced: call by unique constraint or call by PK. In your case, the EmployeeEntity.GetSingleLogindetails method should have a _loginDetails.FetchUsingUC... call, not a PK call. Could you check that for me, please?

This O/R stuff is really great but I am still trying to get my head around some of the concepts (simple as they may be!). Do you know of any good articles that could help explain this to me in greater detail? Frans, when did you first discover this model and how did you learn about it?

LLBLGen Pro uses O/R mapping, though doesn't implement all facets of O/R mapping (yet), inheritance isn't implemented yet (will be in april).

The 'standard' work on this is this essay: http://www.agiledata.org/essays/mappingObjects.html

and I also learned a lot from the O/R mapper called SimpleORM: http://www.simpleorm.org/ as it comes with a good explanation of what it does (technically). Though a lot is redesigned by me, as I wanted a more practical paradigm build in, thus not 'pure O/R mapping' but more a practical implementation of o/r mapping, so users don't have to learn deep details about o/r mapping before understanding that using an entity in code is like using a table in sql.

When you fetch logindetails using the unique constraint (FetchUsingUCEmployeeK or something) and pass in _userK, does it fetch an entity or not?

Yes, the entity is returned with data.

1:1 relations did have some problems in the past, where the code generator made mistakes what to do when a related entity was referenced: call by unique constraint or call by PK. In your case, the EmployeeEntity.GetSingleLogindetails method should have a _loginDetails.FetchUsingUC... call, not a PK call. Could you check that for me, please?

In the underlying EmployeeEntityBase class the .FetchUsingUCEmployeeFk(Me.EmployeeK) is called.

Dim fetchResult As Boolean = newEntity.FetchUsingUCEmployeeFk(Me.EmployeeK)

...so users don't have to learn deep details about o/r mapping before understanding that using an entity in code is like using a table in sql.

I definately agree with youon this point. There may be a lot of functions (to learn) in the generated code but I am finding that there is far less overhead now than managing stored procedures with LLBLGen v1 (which I also did like very much).

If you are ever in Australia let me know (you have my email) and I will be sure to shout you lots of beers!! Thanks again mate!!

techiehippy wrote:

When you fetch logindetails using the unique constraint (FetchUsingUCEmployeeK or something) and pass in _userK, does it fetch an entity or not?

Yes, the entity is returned with data.

1:1 relations did have some problems in the past, where the code generator made mistakes what to do when a related entity was referenced: call by unique constraint or call by PK. In your case, the EmployeeEntity.GetSingleLogindetails method should have a _loginDetails.FetchUsingUC... call, not a PK call. Could you check that for me, please?

In the underlying EmployeeEntityBase class the .FetchUsingUCEmployeeFk(Me.EmployeeK) is called.

Dim fetchResult As Boolean = newEntity.FetchUsingUCEmployeeFk(Me.EmployeeK)

Hmm. So the entity is fetched, it's not new, values are being assigned, you save it using Save() and it doesn't get saved: loginDetails.Save() returns true or false? If you set a breakpoint in UpdateEntity() in LoginDetailsEntityBase.cs, does it get called ? (you can follow the complete query in selfservicing through the DAO if you'd like, from there.)

...so users don't have to learn deep details about o/r mapping before understanding that using an entity in code is like using a table in sql.

I definately agree with youon this point. There may be a lot of functions (to learn) in the generated code but I am finding that there is far less overhead now than managing stored procedures with LLBLGen v1 (which I also did like very much).

If you are ever in Australia let me know (you have my email) and I will be sure to shout you lots of beers!! Thanks again mate!!

Free beer is always a big big plus . I don't know if I ever will visit Australia, but if I do, I'm sure to drop you a line

Hmm. So the entity is fetched, it's not new, values are being assigned, you save it using Save() and it doesn't get saved: loginDetails.Save() returns true or false? If you set a breakpoint in UpdateEntity() in LoginDetailsEntityBase.cs, does it get called ? (you can follow the complete query in selfservicing through the DAO if you'd like, from there.)

If I do this...

Dim logindetails As LoginDetailsEntity = New LoginDetailsEntity
logindetails.FetchUsingUCEmployeeFk(userK)
logindetails.Save()

...the entity is being returned by FetchUsingUCEmployeeFk(userK). The logindetails.Save() function returns True but the data isn't saved. The UpdateEntity() function in LoginDetailsEntityBase is never called during this process.

I couldn't step into the Save() function as this is inherited from the EntityBase in the compiled DLL.

I was able to step through the DAO class but this was only from FetchUsingUCEmployeeFk().

I hope this makes sense, but more importantly I hope it helps!!

techiehippy wrote:

If I do this...

Dim logindetails As LoginDetailsEntity = New LoginDetailsEntity
logindetails.FetchUsingUCEmployeeFk(userK)
logindetails.Save()

...the entity is being returned by FetchUsingUCEmployeeFk(userK). The logindetails.Save() function returns True but the data isn't saved. The UpdateEntity() function in LoginDetailsEntityBase is never called during this process.

Correct, as you don't change any fields.

What I want to know is, in the code snippet you posted in the topic start posting, you set two fields and save the entity, but it doesn't get saved. In that situation, could you please test to see if UpdateEntity is called? Save() calls UpdateEntity(restriction) if the entity is dirty and not new, so a breakpoint in UpdateEntity(restriction) (not the non restriction overload) will get caught.

I have run the original code (as posted in the first message in this thread) to test for update.

 ' Employee entity
Dim employee As EmployeeEntity = New EmployeeEntity(_userK)

employee.FullName = _fullName
employee.RoleFk = _roleFK

employee.Save()


' Login details entity
Dim logindetails As LoginDetailsEntity = employee.LoginDetails

logindetails.LoginName = _userName
logindetails.Password = _password

' Save
logindetails.Save()

• When logindetails.Save() is hit, logindetails.IsDirty() = True;
• I have set breakpoints in both UpdateEntity() and UpdateEntity(updateRestriction) and the breakpoint in UpdateEntity() is the only one hit.

And if you step into the dao.UpdateLoginDetails() routine, what happens there, does it stop at some point or does it run through to the CreateAndRunUpdateQuery() ? If so, if you step into that method, what's the SQL query produced? (type in the immediate window of vs.net: ?((ActionQuery)updateQuery).ToQueryText()

or go to the locals view in the debugger and open updateQuery.Command.CommandText.

I also wonder if loginDetails has a primary key defined? (because if it doesn't UpdateEntity() just returns true))

I'll start with your last statement first (this could resolve the problem).

I also wonder if loginDetails has a primary key defined? (because if it doesn't UpdateEntity() just returns true))

There is no primary key defined in the logindetails table. I am certainly no database design expert, but I didn't think that this would be necessary since the LoginDetails.EmployeeFK column relates to the Employee.EmployeeK and I have set a unique contraint on the EmployeeFK column. Does the generator not recognise this?

Both of the LoginDetailsBase.UpdateEntity methods just return true (no other calls).

And if you step into the dao.UpdateLoginDetails() routine, what happens there, does it stop at some point or does it run through to the CreateAndRunUpdateQuery() ? If so, if you step into that method, what's the SQL query produced? (type in the immediate window of vs.net: ?((ActionQuery)updateQuery).ToQueryText() or go to the locals view in the debugger and open updateQuery.Command.CommandText.

There is no LoginDetailsDAO.UpdateLoginDetails() routine.

I am starting to see where this is leading - set the LoginDetails.EmployeeFK as a primary key?

Can you please confirm that this is the action to take?

Thanks again -

techiehippy wrote:

I'll start with your last statement first (this could resolve the problem).

I also wonder if loginDetails has a primary key defined? (because if it doesn't UpdateEntity() just returns true))

There is no primary key defined in the logindetails table. I am certainly no database design expert, but I didn't think that this would be necessary since the LoginDetails.EmployeeFK column relates to the Employee.EmployeeK and I have set a unique contraint on the EmployeeFK column. Does the generator not recognise this?

You need a primary key on an entity to be able to update it. Otherwise the code can't create a unique filter to update the particular entity in the database. A unique constraint is not enough, because you can have multiple unique constraints on a table, they all uniquely identify an entity, but which one to pick is unclear, there is just 1 defined way: use the PK

Both of the LoginDetailsBase.UpdateEntity methods just return true (no other calls).

Then we finally found the cause of the issue! Took a while, but we got there Add a PK to the entity, refresh teh catalog, regenerate the code and it should work.

Again I am having problems here, however there are a few slight differences. This time I have a primary key defined in each table and the database is now a SQL 2000.

Unlike the problem above I am now having trouble INSERTING a new record into both tables.

The table structure is like so:

Employee EmployeeK uniqueidentifier PK FirstName varchar(50) LastName varchar(50)

LoginDetails EmployeeFK uniqueidentifier PK UserName varchar(50) Password varchar(50)

There is a relationship defined between the two tables - EmployeeK to EmployeeFK (1:1).

As mentioned I am trying to create record in both tables i.e. a new user account is being created with a record in each table. Can this be achieved with the code below?

EmployeeEntity employee = new EmployeeEntity();
employee.FirstName = _sFirstName;
employee.LastName = _sLastName;

LoginDetailsEntity loginDetails = new LoginDetailsEntity();
loginDetails.UserName = _sUserName;
loginDetails.Password = _sPassword;

employee.LoginDetails = loginDetails;

employee.Save(true);

When I execute this query I get an error because the LoginDetails entity can't be saved.

Should I instead be saving the EmployeeEntity first, get the EmployeeK value for the saved entity and then save the LoginDetails entity?

It should be saved ok. What's the exact error you get?

The error message is:

An exception was caught during the execution of an action query: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_LoginDetails_Employee'. The conflict occurred in database 'Vanman', table 'Employee', column 'EmployeeK'. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

The AddEmployee function is executed OK and without a problem in the EmployeeDAO class. The error occurs when the AddLoginDetails function is executed in the LoginDetailsDAO class. The exact line is:

bool wasSuccesful = (base.ExecuteActionQuery(insertQuery, containingTransaction) > 0);

Ah, I see what's wrong. Please do:

EmployeeEntity employee = new EmployeeEntity(); employee.FirstName = _sFirstName; employee.LastName = _sLastName; employee.EmployeeK = Guid.NewGuid();

LoginDetailsEntity loginDetails = new LoginDetailsEntity(); loginDetails.UserName = _sUserName; loginDetails.Password = _sPassword;

employee.LoginDetails = loginDetails; employee.Save(true);

i.o.w.: you forgot to set the PK value.

Prachtig!! Thankyou

I had a default value of newid() set for the column, this obviously isn't taken into account even with IsRowGuid set? Is this the same for identity columns, ints etc?

Thanks again!!

techiehippy wrote:

Prachtig!! Thankyou

I had a default value of newid() set for the column, this obviously isn't taken into account even with IsRowGuid set? Is this the same for identity columns, ints etc?

Thanks again!!

That default value is indeed not used, as SqlServer doesn't have a facility to read back the value set by such a default value.

So when the Employee is written, the PK is not set, it's set by the DB, but that value is unknown. So the entity in memory doesn't have the value for the PK field, and can't therefore synchronize that value with the FK field in related entities .