I have an applicatio that raises invoices. I post the header to tbl_invoice and its items to tbl_invoice_item, tables are linked on invoice_no field.
I have 2 stored procedures, 1 to post the header details and 1 to post the items.
When submiting the invoice from my vb6 application I begin a transaction post the header, post the items then commit. This way if either stored procedure fails the entire transaction is rolled back
Whats happenning is the header is being posted, the first line item then on the next line item sql error occurs re violation of foreign key constratint on header table. It looks like the header is posted, an error occurs on the the first line item causing the header to be rolled back, for some reason the sql error is not being returned to my application and so continuing to post the second line item. By this time there is no header hence the error msg.