Hello guys,

Can anyone please enlighten me on situations where running a data manipulation
command that results in an error on SQL Server does NOT raise a corresponding
error in a calling VB application?

I have two tables with a parent-child relationship which is enforced through
declarative referential integrity on all delete operations performed on the
parent i.e. parent record may not be deleted if it has a child.

As expected, executing "DELETE FROM parent WHERE parent_id = x" using the
query optimiser produces an error and the statement is not executed. When
I include the same statement as part of a stored procedure and then execute
the stored procedure from the query optimiser, I also get an error at the
statement as expected. Upon error the stored procedure rollbacks the entire
transaction.

Now, the perplexing situation is this: when I execute the stored procedure
using a command object within a VB application, within a procedure that has
a On Error Goto clause to catch errors, the error does not pop-up within
the VB application.

When the same VB procedure executes an insert or update operation upon the
database using stored procedures, with data that violates some rule on the
database, say leaving null values in non-nullable fields, those stored procedures
produce errors and roll back the transactions, the same way the delete operation
above is done. The only difference is that error conditions produced by insert
and update operations within those stored procedures are reproduced as expected
in the calling VB application.

Is it a known bug that errors encountered within stored procedures which
DELETEs data are NOT reproduced within the calling VB application?

Here's a snippet from the stored procedure. Basically the delete operation
is very simple and not much can go wrong with it:

<snip>
/* Trying to delete from parent table */
DELETE FROM DonorId WHERE id = @id

/* Unable to delete from parent table for whatever reason*/
IF @@error <> 0
BEGIN
ROLLBACK TRAN NJSNewDonor_SP_delete
raiserror(@@error, 16, 1)
RETURN -1
END

/* All okay */
IF @tc = 0
COMMIT TRAN NJSNewDonor_SP_delete

RETURN 0

I'm using SQL Server 7.0.

How the error that occurs in the above stored procedure does NOT get caught
in the calling VB application wherelse a similar error in another stored
procedure that does an INSERT or UPDATE
DOES get caught in VB?

Any hint would be appreciated. Thank you in advance.

Khalizan