Updating with OracleDataAdapter, DataRelation

[byleth]

Hi,

I'm having a hard time trying to update a table that has child tables.

I have a DataSet with 8 tables and each DataSet was filled with a different OracleDataAdapter.

Then i've created a relation between two of the tables like this:

Dim col1 As DataColumn = ds.Tables("users").Columns("USER_ID")
Dim col2 As DataColumn = ds.Tables("users_profile").Columns("USER_ID")

dr = New DataRelation("r1", col1, col2)
ds.Relations.Add(dr)

Dim fk As ForeignKeyConstraint = dr.ChildKeyConstraint
fk.DeleteRule = Rule.Cascade
fk.UpdateRule = Rule.Cascade
fk.AcceptRejectRule = AcceptRejectRule.Cascade
ds.EnforceConstraints = True


Follwed by:

oda.Update(ds.Tables("users"))



The goal is to delete one datarow in the parent table and have it 'Cascade' through the child tables, however i get an 'integrity constraint violated: child record found' error instead.
It seems the relation doesn't make any effect, i get the exactly same thing not using it..


tx in advance.

 

[SBendBuckeye]

Is it possible that Oracle will automatically take care of that for you if deletes are setup to cascade? I don't know the answer, just asking the question in case it helps. Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[chrissie1]

Yep that is defenitly a oracle error saying you need to delete childs before parents. A real database always has referential integrity set and guess what this one has.

remember since the dataset isn't really linked to the databse you can have different relations in your dataset then the database.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[byleth]

hi Christiaan,

Are you saying i can't delete the parent and cascade through the childs? because that's what i want...

 

[chrissie1]

not if the oracle database is setup in this way. You will have to delete the children before the parents.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[byleth]

but i have

oda1.Update(ds.pTable)
oda2.Update(ds.cTable)

... the actual update in the database is done when i save and exit from the application, meanwhile i deleted parents with childs (ofcourse the childs and parent where deleted, but only in the DataSet). Are you saying this is impossible? Nor even with a relation and a Delete Rule set to Cascade?

 

[chrissie1]

like I said the dataset is detached form the database server and will not change your schema when you try to update the update,delete and insert statetements will still have to folow the database rules.

the rules and relations you have set above don't change the database just the dataset you use locally.



Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting