Error handling in VB/oracle using stored procedures for DML

[Fig000]

Hi,

I am new to using oracle with VB. I started going the route where I use VB and only use oracle to populate record sets (no parameter queries or stored procedures of triggers). I found that the errors returned to VB were terse and didn't specify which table field was affect. For instance, if I had more than on NOT NULL field, all I would get was an error saying that "some field" had violated the not null constraint.
So I turned to using stored procedures for DML. I chose triggers since I could return the name of the field affected, but testing exceptions (such as violations of uniqueness constraints) seems impossible. I am confused as to how to get decent error messages back to vb with the names of the fields affected. Any suggestions?

Thanks,
Neil

 

[krzyzowiec]

I guess you are trying to insert a record into some table. If this is the case check what fields are required in the table and then before inserting it do an error check in VB program to catch those null fields.

 

[Fig000]

Hi krzyzowiec,

I had a feeling that you'd say to check the fields before I did the insert, in VB. The VB/Oracle books play up the advantages of using oracle stored procedures for DML statements (having error logic tied directly to your database is supposed to be good) but seem to leave out the fact the the error messages are somewhat vague at times. It seems to me that raising an error in a trigger might be best since, in a trigger, you know what field is being modified. Still this seems a bit clumsy to me.
But your idea still might be the best.

Thanks,
Neil