Help on OleDbCommand error handling

[phinoppix]

I'm having a little problem with my code. Im trying to update a record using OleDbCommand.
This is the initializer code:

    strSQL = "UPDATE CUSTOMER SET " + 
                "NAME=?, CTCPERSION=?, ISVAT=?, ISACTIVE=?, NATURE=?, COUNTRY=?, REMARKS=? " +
                "WHERE ID=?;";
    cmdUpdate = new OleDbCommand(strSQL, CGateKeeper.conSales);
    cmdUpdate.CommandType  = CommandType.Text;
    cmdUpdate.Parameters.Add("name"     , OleDbType.VarChar, 150);
    cmdUpdate.Parameters.Add("ctcperson", OleDbType.VarChar, 50);
    cmdUpdate.Parameters.Add("isvat"    , OleDbType.Boolean);
    cmdUpdate.Parameters.Add("isactive" , OleDbType.Boolean);
    cmdUpdate.Parameters.Add("nature"   , OleDbType.Integer);
    cmdUpdate.Parameters.Add("country"  , OleDbType.VarChar, 20);
    cmdUpdate.Parameters.Add("remarks"  , OleDbType.VarChar, 200);
    cmdUpdate.Parameters.Add("id"       , OleDbType.Integer);

And here's how it's used:

    tran = CGateKeeper.conSales.BeginTransaction(IsolationLevel.ReadCommitted);
    
    cmdUpdate.Parameters["id"].Value            = file.ID;
    cmdUpdate.Parameters["name"].Value          = file.Name;
    cmdUpdate.Parameters["ctcperson"].Value     = file.CtcPerson;
    cmdUpdate.Parameters["isvat"].Value         = file.IsVat;
    cmdUpdate.Parameters["isactive"].Value      = file.IsActive;
    cmdUpdate.Parameters["nature"].Value        = file.Nature;
    cmdUpdate.Parameters["country"].Value       = file.Country;
    cmdUpdate.Parameters["remarks"].Value       = file.Remarks;
    
    cmdUpdate.Transaction = tran;
    recAffect = cmdUpdate.ExecuteNonQuery();
    tran.Commit();

The system raises the error:

    An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

And breaks to

recAffect = cmdUpdate.ExecuteNonQuery();

part.

How do I get additional information on the exception? This is the same message when a constraint violation occurs.
I just found out so while doing trial and error on code segments, and not much of a help.
Funny, I was able to make Insert statement work, but not Update. It's very obvious I'm missing something
out here, but, pity me...

TIA

 

[chiph]

Surround your code where you're using the update query & it's parameters with a try..catch block:

try {
  tran = CGateKeeper.conSales.BeginTransaction(IsolationLevel.ReadCommitted);
    
  cmdUpdate.Parameters["id"].Value            = file.ID;
  cmdUpdate.Parameters["name"].Value          = file.Name;
  cmdUpdate.Parameters["ctcperson"].Value     = file.CtcPerson;
  cmdUpdate.Parameters["isvat"].Value         = file.IsVat;
  cmdUpdate.Parameters["isactive"].Value      = file.IsActive;
  cmdUpdate.Parameters["nature"].Value        = file.Nature;
  cmdUpdate.Parameters["country"].Value       = file.Country;
  cmdUpdate.Parameters["remarks"].Value       = file.Remarks;
    
  cmdUpdate.Transaction = tran;
  recAffect = cmdUpdate.ExecuteNonQuery();
  tran.Commit();
}
catch (OleDbException ODex) {
  // examine the ODex object here & log error
}
catch (System.Exception ex) {
  // examine the ex object here & log error
}
finally {
  // close, dispose, & set to null your OleDb 
  // objects here
}

Note that you can have multiple catch statements -- but they must go from the specific to the general (all exceptions inherit from System.Exception, so he goes last).

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[phinoppix]

Tnx chiph! I found the bug just last night after work. Just a typo on the sql statement

strSQL = "UPDATE CUSTOMER SET " + 
                "NAME=?, [b]CTCPERSION[/b]=?, ISVAT=?, ISACTIVE=?, NATURE=?, COUNTRY=?, REMARKS=? " +
                "WHERE ID=?;";

I've just learned about the exception (OleDbException), but thanks for the additional info (nested catch).

2 things I learned from this "problem": read MSDN first and try to understand what you just read. (hehe)