OracleDataAdapter.Update returns 1, even when no row updated

[Martin Angers]

Hi,

I am using MS VS.NET 1.1, with Microsoft's .NET Oracle client
targeting an Oracle 9i database.

I use the OracleDataAdapter to update a table through some stored
procedures (one for Insert, one for Update and one for Delete). All
commands are correctly set to the DataAdapter, along with SourceColumn
and SourceVersion. In fact, the Update works fine, except when it
updates nothing, it still returns 1 and doesn't raise the
DbConcurrencyException!

I tested by retrieving some records in a DataTable, then I deleted
these records from the DB, and after that I deleted them from the
DataTable and called DataAdapter.Update(). I expected the exception,
but instead got no error...

The Stored Procedures are in a Package, and are all Procedures (not
Functions). Am I missing something? As far as I recall, this worked
fine with Sql Server. Is it a limitation of the Oracle data provider?

Thanks,
Martin

 

[Craig]

Does anyone have any information on this problem? I'm
experiencing the exact same thing. I get the correct
RecordsAffected value for SQL Server but not for Oracle.
Thanks...

 

[Martin Angers]

Hi Craig,

What I did was add an output parameter to each Update and Delete
stored proc, and send back the number of affected records in this
parameter (using SQL%RowCount right after the Update or Delete
statement).

Then, in .NET, I add an event handler to the RowUpdated event of the
OracleDataAdapter. In this handler, I check the value of the output
parameter, and if it is 0, then I throw a DbConcurrencyException. In
other words, I do manually what the Oracle data provider should have
done all by itself.

I haven't tried the Oracle ODP.NET. It might work. Please keep me
posted if you try it.

HTH,
Martin

 

[Craig]

Thanks Martin.

The Oracle ODP.NET has the same behavior as
Microsoft's .NET Data Provider for Oracle. So, I guess
I'll try to implement your work around...

Do you know how to submit issues like this to Microsoft?
It's either a bug or a design oversight...

 

[Martin Angers]

I don't know where you can submit these kind of problems... You could
try this address http://msdn.microsoft.com/vstudio/support/support.aspx

I checked real quick but I didn't see any way of submitting
bugs/problems...