3

I'm using OracleCommand.ExecuteNonQuery() to insert into a table. Everything works fine but occasionally records are not inserted. So is it possible that ExecuteNonQuery() doesn't insert the record and also doesn't throw an exception?

I'm not using stored procedures. I'm not using a transaction. I am logging any exception that is thrown by ExecuteNonQuery() but apparently no exception is thrown... I am also not checking the return value of ExecuteNonQuery(). But is it possible that ExecuteNonQuery returns another value than 1 if the insert is successful?

Elz
  • 887
  • 1
  • 8
  • 12
  • Is this being done inside a transaction from code? Maybe the transaction is not being committed. – Jeff Meatball Yang Dec 02 '09 at 15:19
  • The reason _why_ they're not inserted would be interesting...also, maybe you could post the Insert-Statement. – Bobby Dec 02 '09 at 15:23
  • A few years later, but I have the same issue with the 4.0 Oracle.DataAccess components. The ExecuteNonQuery doesn't throw an exception when there's an error AND it stops execution of any code after that line. – b.pell Apr 20 '12 at 18:14

6 Answers6

3

It shouldn't. ExecuteNonQuery returns an integer indicating the number of rows affected. If no rows were inserted, 0 should be returned. It should thrown an exception when it fails to execute the query. E.x.: the connection is closed, the table doesn't exist, etc.

Hasani Blackwell
  • 2,026
  • 1
  • 13
  • 10
  • Is there any documentation stating that checking for the Exception is sufficient to know the error for (INSERT, UPDATE, DELETE) operations? Can I just ignore the return value and just depend on the catch statement. Also, in the MSDN documentation it states that exception are not thrown in .NET 1.1, but it did not state that exception are sure to be thrown on .NET 2.0 afterwards. – Nap Feb 26 '10 at 05:10
2

Not unless you're swallowing the exception somewhere, like this:

try
{
   DoSomethingOnTheDbThatMightThrowAnException();
}
catch(Exception ex)
{
   // Do nothing, thus swallowing the exception
}

Also, check the return value of ExecuteNonQuery(). If it is 0, you might want to consider throwing an exception yourself.

Neil Barnwell
  • 41,080
  • 29
  • 148
  • 220
1

It's quite unlikely.

Do you have a error handler at a higher level which might be catching the error, and throwing it away?

Bravax
  • 10,453
  • 7
  • 40
  • 68
1

"Everything works fine but occasionally records are not inserted." Possibly inserting into/through a view which can allow you to insert records which you can't actually see. Virtual Private Database/Row Level Security can do this too.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
0

Just for the record I had this issue.

It occurred because I was testing for a null or DBNull.Value from a gridviewcell in a row when building the Command parameters for the stored proc from the cells.

Something like this:

if(cell.Value != DBNull.Value && cell.Value != null)  
{    
    //add the parameter to the Command object  
}

This meant that it was creating a parameter when I got an empty string and the Convert.ToDecimal didn't throw the NumberFormatException at that time.

Instead the NumberFormatException got thrown when I tried to execute the cmd.ExecuteNonQuery().

I'm guessing that there is a conversion which takes place at runtime when the ExecuteNonQuery() is kicked off.

I fixed it by switching to:

if(cell.Value != DBNull.Value &&
!string.IsNullOrEmpty(cell.Value.ToString()))
{
   //Create and Add the parameter
}
Rob
  • 45,296
  • 24
  • 122
  • 150
MIke
  • 1
0

It could be that ExecuteNonQuery() throws an exception that is not caught by your try-catch block. Check your Windows Event log to be sure; an AccessViolationException could occur due to one of your drivers crashing and that sort of exception is not normally catchable in managed code from .NET 4.0 onwards. A solution is described on this link.

Community
  • 1
  • 1
Philip Atz
  • 886
  • 1
  • 10
  • 26