DataAdapter update method fails

  • Thread starter Thread starter Lars Beyer-Olsen
  • Start date Start date
L

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
 
Lars Beyer-Olsen said:
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?
Click to expand...

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. Got any ideas?

Lars
 
David Browne said:
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
Click to expand...

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
 
Lars Beyer-Olsen said:
"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?
Click to expand...

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
 
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
 
Back
Top