Refreshing values after SubmitChanges

ross_james · Post by **ross_james** » Mon 27 Apr 2009 16:28

Hi,

I have a table, CUSTOMERS, which has a Primary Key ID column that is populated from a sequence on an insert trigger.

When I create an entry as per the below, the Customer_ID column is still 0 though the inserted row in the table has a value of 9:

Code: Select all

Dim cust As New CUSTOMER
cust.Consultant_ID = 1
cust.Customer_Name = tbCustName.Text
...
odb.CUSTOMERs.InsertOnSubmit(cust)
odb.SubmitChanges()

Is there something else I need to set to make it read the inserted values back from the table?

Thanks,

Ross

AndreyR · Post by **AndreyR** » Tue 28 Apr 2009 07:51

Set the Auto Generated Value property of the ID column to true and the AutoSync property of the ID column to OnInsert in your
Devart LINQ to SQL model and save the project.

ross_james · Post by **ross_james** » Tue 28 Apr 2009 14:40

Thanks, I knew it would be something like that!

However, if I create a customer object as follows:

Code: Select all

Dim cust As New CUSTOMER
cust.Consultant_ID = 1
cust.Customer_Name = tbCustName.Text
...
odb.CUSTOMERs.InsertOnSubmit(cust)
odb.SubmitChanges()

and then try to use that object in a subsequent insert e.g:

Code: Select all

Dim newDiv As New DIVISION
newDiv.CUSTOMER = cust
newDiv.Name = "APEX"
odb.DIVISIONs.InsertOnSubmit(newDiv)
odb.SubmitChanges()

It throws a "Value of member 'DivisionID' of an object of type 'DIVISION' changed.A member that is computed or generated by the database cannot be changed." error.

If I change it to:

Code: Select all

Dim newDiv As New DIVISION
newDiv.CustomerID = cust.Customer_ID
newDiv.Name = "APEX"
odb.DIVISIONs.InsertOnSubmit(newDiv)
odb.SubmitChanges()

it then works fine. The only way I can set CUSTOMER is if I turn off "Auto Generated Value" on the DivisionID column, it seems to still create the ID from the sequence and refresh the local object. Is this normal?

ross_james · Post by **ross_james** » Tue 28 Apr 2009 15:32

Further to the above, it actually creates 2 DIVISION rows when Auto Generated Value is turned off

ross_james · Post by **ross_james** » Tue 28 Apr 2009 16:15

Something very strange going on here! I've read http://www.devart.com/forums/viewtopic. ... 980ec2a290 which seems to be the same problem I'm having but the solution doesn't work for me.

The code is as follows:

Code: Select all

Dim cust As New CUSTOMER
...
odb.CUSTOMERs.InsertOnSubmit(cust)
odb.SubmitChanges()

Dim newDiv As New DIVISION
newDiv.CUSTOMER = cust
...
odb.DIVISIONs.InsertOnSubmit(newDiv)
odb.SubmitChanges()

If I keep "Auto Generated Value"="True" and "Auto-Sync"="On Insert" for the ID columns, I get Value of member 'DivisionID' of an object of type 'DIVISION' changed.A member that is computed or generated by the database cannot be changed.

If I remove the first "odb.SubmitChanges()" as the other post suggests, I then get Value of member 'Customer_ID' of an object of type 'CUSTOMER' changed.A member that is computed or generated by the database cannot be changed.

ross_james · Post by **ross_james** » Tue 28 Apr 2009 16:23

I realised I forgot to also remove the

Code: Select all

odb.CUSTOMERs.InsertOnSubmit(cust)

it does seem to work correctly if I only insert the last object in the chain, the rest get created automatically. This doesn't seem right comared to LINQ2SQL, how would I just insert a DIVISION when I already have a CUSTOMER created and inserted?

AndreyR · Post by **AndreyR** » Wed 29 Apr 2009 10:42

The bug you have described is already fixed in the current 5.20.27 Beta build of dotConnect for Oracle.
You can use the code with two InsertOnSubmit() calls, it should work.