DataAdapter update method fails

[Lars Beyer-Olsen]

Hi.

I got a problem with the OleDbDataAdapter object in .Net.
The case is:

I fill a dataset with data from an XML-file. This works fine.
I then want to store the data into a database. This also seems to work
fine.
The problem ouccurs when I try to update the data in the table. If I
read the same XML-file into the dataset and perform the Update method
of the dataadapter it throws this Exception:

ORA-00001: unique constraint (SOME.TABLE) violated

Source code:

################################################################
Dim cb As New OleDbCommandBuilder(OleDbDataAdapter1)
DataSet1.ReadXmlSchema( _
"H:\temp\foo.xsd")
DataSet1.ReadXml( _
"H:\temp\foo.xml")
OleDbDataAdapter1.Update(DataSet1)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
################################################################

It seem to me that the dataadapter trys to do an insert and fails
because there
already is a row with the same values in the table.

Anyone got a clue how to solve this?

Lars

 

[David Browne]

Hi.

I got a problem with the OleDbDataAdapter object in .Net.
The case is:

I fill a dataset with data from an XML-file. This works fine.
I then want to store the data into a database. This also seems to work
fine.
The problem ouccurs when I try to update the data in the table. If I
read the same XML-file into the dataset and perform the Update method
of the dataadapter it throws this Exception:

ORA-00001: unique constraint (SOME.TABLE) violated

Source code:

################################################################
Dim cb As New OleDbCommandBuilder(OleDbDataAdapter1)
DataSet1.ReadXmlSchema( _
"H:\temp\foo.xsd")
DataSet1.ReadXml( _
"H:\temp\foo.xml")
OleDbDataAdapter1.Update(DataSet1)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
################################################################

It seem to me that the dataadapter trys to do an insert and fails
because there
already is a row with the same values in the table.

Anyone got a clue how to solve this?

If the row in the datatable has RowState.Added, the data adapter will
attempt an insert. Only if the row has RowState.Modified will the
dataAdapter attempt an update.

David

 

[Lars Beyer-Olsen]

This i know David. The problem is that all the rows in the dataset get
the Rowstate.Added when i read the XML-file. I think I'm looking for a
method that in some way tells the dataset that the row already exsist in
the database. Got any ideas?

Lars

 

[Lars Beyer-Olsen]

If the row in the datatable has RowState.Added, the data adapter will
attempt an insert. Only if the row has RowState.Modified will the
dataAdapter attempt an update.

David

This i know David. The problem is that all the rows in the dataset get
the Rowstate.Added when i read the XML-file. I think I'm looking for a
method that in some way tells the dataset that the row already exsist
in
the database, and that a update should be executed instead of an
insert.

I could accomplish this by iterating all the rows in the dataset and
manually do an update for every row that fails on insert. The code
would look something like this:

################################################################
Dim row as System.Data.DataRow
for each row in DataSet1.Tables(0).Rows
try
InsertRow(row) ' Throws an exception if the insert fails
catch
UpdateRow(row) ' If the insert fails, this line is executed
end try
next
################################################################

But if this is to be done for a large amount of rows say 10000, it
would be a tedious task to accomplish.

Got any ideas?

Lars

 

[David Browne]

"David Browne" <davidbaxterbrowne no potted (e-mail address removed)> wrote in

This i know David. The problem is that all the rows in the dataset get
the Rowstate.Added when i read the XML-file. I think I'm looking for a
method that in some way tells the dataset that the row already exsist
in
the database, and that a update should be executed instead of an
insert.

I could accomplish this by iterating all the rows in the dataset and
manually do an update for every row that fails on insert. The code
would look something like this:

################################################################
Dim row as System.Data.DataRow
for each row in DataSet1.Tables(0).Rows
try
InsertRow(row) ' Throws an exception if the insert fails
catch
UpdateRow(row) ' If the insert fails, this line is executed
end try
next
################################################################

But if this is to be done for a large amount of rows say 10000, it
would be a tedious task to accomplish.

Got any ideas?

Ok, sure. I've got lots of ideas.

Here's one.

Create a global temporary with a rowtype to match your target table. Do the
inserts against that table, and then do a MERGE (on 9i), or an INSERT/UPDATE
(8i or less).

Here's another.

Change the InsertCommand to a PL/SQL program that will silently update
existing rows. Like this:

create table t(id int, name varchar2(25), value int);
/

declare
r t%rowtype;
begin
r.id := 1;
r.name := 'hello';
r.value := 23;

update t set
id = r.id,
name = r.name,
value = r.value
where id = r.id;

if SQL%NOTFOUND then
insert into t(id,name,value)
values(r.id, r.name, r.value);
end if;
end;


You will need to expose all the bind variables and attach parameters with
SourceColumn properties to the command.

declare
r t%rowtype;
begin
r.id := :0;
r.name := :1;
r.value := :2;

update t set
id = r.id,
name = r.name,
value = r.value
where id = r.id;

if SQL%NOTFOUND then
insert into t(id,name,value)
values(r.id, r.name, r.value);
end if;
end;

Or save this as a stored procedure, like this

create or replace procedure upsert_t(
pID int,
pName varchar2,
pValue varchar2)
is
r t%rowtype;
begin
r.id := pID;
r.name := pName;
r.value := pValue;

update t set
id = r.id,
name = r.name,
value = r.value
where id = r.id;

if SQL%NOTFOUND then
insert into t(id,name,value)
values(r.id, r.name, r.value);
end if;
end;



David

 

[MeDhanush]

may be you can play around with BeginLoadData, LoadDataRow and EndLoadData.
LoadDataRow is called with existing values or
call LoadDataRow with false as second param

Kishore