Hi

I have a form based on an entity. I can load and edit exisiting entites and load and create related entities, but, I am confused about creating new entities from scatch.

As I see it, my options are: (LLBLGenPro 2.0.0.0 Final, not real code below)

1)

e = MyEntity.New(); entityBindingSource.DataSource=e; ... success = adapter.SaveEnity(e,true);

This, however, fails to add the new record to the table and returns no error.

2)

e = (MyEntity)bindingSource.AddNew(); entityBindingSource.DataSource=e; ... success = adapter.SaveEntity(e,true);

This, saves the entity to the table correctly, but, returns false.

3)

entityCollection = new EntityCollection<MyEntity>(); e = entityCollection.AddNew(); entityBindingSource.DataSource=e; ... success = adapter.SaveEntity(e,true);

This, saves the entity to the table correctly, but, returns false.

4)

entityCollection = new EntityCollection<MyEntity>(); entityBindingSource.DataSource = entityCollection; e = entityCollection.AddNew(); ... records = adapter.SaveEntityCollection(entityCollection,true,true);

This, saves the entity to the table correctly and returns records>0.

2), 3), and 4) all generate ORMConcurrency exceptions on subsequent saves, presumably because the record has not been re-fetected correctly.

Which is considered best practice?

Also, in 2) and 3) how do I determine the error condition from SaveEntity returning false?

thanks Andrew

Hi AndrewH,

1) e = MyEntity.New(); ...

I don't know that method, maybe you mean: new MyEntityFactory().Create();?

2), 3), and 4) all generate ORMConcurrency exceptions on subsequent saves, presumably because the record has not been re-fetected correctly.

Subsequent saves? Could you confirm that just after the save the entity is readable without ORMConcurrency exception?

Also, in 2) and 3) how do I determine the error condition from SaveEntity returning false?

2) and 3) should work. Could you please paste the more realistic code you can? That would be helpful to determine the issue (you can use Helpdesk forum to post real code you don't want other people see [only Support team will]).

Hi David

Thanks for replying so promptly :-)

1) e = MyEntity.New(); ...

I don't know that method, maybe you mean: new MyEntityFactory().Create();?

Ah, what I meant was e = new MyEntity();

Thank you for pointing out 2) and 3) were the way to go.

Here is my current code:

ctor

            
            items = new EntityCollection<ItemEntity>();
            item = items.AddNew();
            itemEntityBindingSource.DataSource = item;

save - saves record correctly, returns true, gets ORMEntityOutOfSyncException on subsequent read

            if (adapter.SaveEntity(item) == false )
            {
                MessageBox.Show("Failed to save new item");
            }

alternate save - saves record correctly, returns false, ORMEntityOutOfSyncException on subsequent read

            if (adapter.SaveEntity(item,true) == false )
            {
                MessageBox.Show("Failed to save new item");
            }

2nd alternate save - saves record correctly, returns false, ORMEntityOutOfSyncException on subsequent read

            if (adapter.SaveEntity(item,true,true) == false )
            {
                MessageBox.Show("Failed to save new item");
            }

thanks Andrew

I should add that my database is Oracle 9i and MSOra

The PK of the ITEM table is generated from a SEQUENCE by a TRIGGER on INSERT

Try adding the following to your application config file:

<add key="OracleTriggerSequences" value="true" />

For more info check the manual "Using the generated code -> Application configuration through .config files" Goto the Trigger based sequence values (Oracle/Firebird) section.

Try adding the following to your application config file:

<add key="OracleTriggerSequences" value="true" /> 

Thanks Walaa, but, that didn't help. What extra error information can I extract after SaveEntity returns false?

I have turned tracing on and found that the refetch appears to not be using an id

SELECT "USER"."ITEM"."ID" AS "Id", "USER"."ITEM"."ITEM_COMMENT" AS "ItemComment", "USER"."ITEM"."ITEM_DESCRIPTION" AS "ItemDescription", "USER"."ITEM"."CHANGE_USER" AS "ChangeUser", "USER"."ITEM"."CHANGE_DATE" AS "ChangeDate" FROM "USER"."ITEM" WHERE ( ( "USER"."ITEM"."ID" = :Id1)) Parameter: :Id1 : VarNumeric. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>

I just tried checking "Is Identity / Sequence field" in the LLBLGen designer and specified the sequence used (SEQ_ITEM_ID).

This stops the errors, but, creates multiple records on subsequent saves.

This could be the same issue as this thread:

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10250&HighLight=1

Should I update LLBLGen to 2.5, or, convert my project to the ODP.net drivers?

I just tried checking "Is Identity / Sequence field" in the LLBLGen designer and specified the sequence used (SEQ_ITEM_ID).

If you use the above, then you should disable the trigger. And remove that line from the config file.

Should I update LLBLGen to 2.5, or, convert my project to the ODP.net drivers?

In general I recommend people to use v.2.5 and to use ODP.NET over the MS driver.

I use ODP.NET with sequences (either using triggers or not) and I haven't had any issue using them.

I converted the project to 2.5 and that didn't help, so, I have also moved to the ODP.Net drivers.

After the conversion, however, I am getting the following exception on my first access:

Any ideas?

thanks Andrew

System.InvalidCastException was unhandled Message="Unable to cast object of type 'Oracle.DataAccess.Client.OracleConnection' to type 'System.Data.Common.DbConnection'." Source="System.Data"

System.InvalidCastException was unhandled Message="Unable to cast object of type 'Oracle.DataAccess.Client.OracleConnection' to type 'System.Data.Common.DbConnection'." Source="System.Data"

Changing the references fixed that exception, but, after switching to 2.5 and ODP.Net I still have my initial problem

The SQL generatated for the refetch now looks like this:

Query: SELECT "USER"."ITEM"."ID" AS "Id", "USER"."ITEM"."ITEM_COMMENT" AS "ItemComment", "USER"."ITEM"."ITEM_DESCRIPTION" AS "ItemDescription", "USER"."ITEM"."CHANGE_USER" AS "ChangeUser", "USER"."ITEM"."CHANGE_DATE" AS "ChangeDate" FROM "USER"."ITEM" WHERE ( ( "USER"."ITEM"."ID" IS NULL)

Which doesn't make sense as the TRIGGER will have set ID to the next SEQUENCE value.

Well, I have it working now with following config:

llblgen 2.5 ODP.Net 9i by

Designer: "is identity/sequence" true "read only" true "sequence name" = SEQ_ITEM_ID

app.config file

<add key="OracleTriggerSequences" value="true" /> 

Oracle TRIGGER on

This solution seems contrary to advice given previously in this and other threads

ie

Andrew:

I just tried checking "Is Identity / Sequence field" in the LLBLGen designer and specified the sequence used (SEQ_ITEM_ID).

Walaa: If you use the above, then you should disable the trigger. And remove that line from the config file.

Is my solution acceptable?

This solution seems contrary to advice given previously in this and other threads

I guess I made a mistake. The idea is: You have 2 options if a field is using a sequence in Oracle.

1- Either you let LLBLGen Pro use that Sequence when it's inserting a new row. It gets a new value from the sequence which is then used in the INSERT statment.

OR

2- You depend on a trigger to insert the Sequence value. In this later case, you should let LLBLGen Pro know that you use a trigger. I was wrong to think that LLBLGen Pro queries the value from the inserted row, so it won't need to know about the sequence at all. But as the documentation states, it execute a sequence retrieval query which checks for the current value (using CURRVAL). So it should know about which sequence to query.

Using the Configuration file, you tell LLBLGen Pro to use the second option rather than the default first option.

Is my solution acceptable?

If it ain't broke, don't fix it.

Thanks for you help :-)