Re: DataAdapter update method fails by David
David
Tue Sep 16 10:02:20 CDT 2003
"Lars Beyer-Olsen" <foscogamgee@netscape.net> wrote in message
news:2adb1f2e.0309160049.1e8c2fd6@posting.google.com...
> "David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:<#24jMz6eDHA.2348@TK2MSFTNGP12.phx.gbl>...
> > "Lars Beyer-Olsen" <foscogamgee@netscape.net> wrote in message
> > news:2adb1f2e.0309150849.203dca8a@posting.google.com...
> > > 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
>
> 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