vb6 does not recognize the error when there is a result set

Question

I have a stored procedure which returns a result set with the error message, when an error occurs. If it executes without any error, the result set is empty (Command(s) completed successfully)

On the vb6 side, I execute the sp and check whether there is an error by

If Err <> 0 Then
 ' do sth
End If

But, when there is a result set, the Err is always 0.

How should I handle this situation?

---

Sorry for inadequate explanation.

Here are my scripts:

--my sample table
create table #InvoiceDocument (InvoiceID int, ItemID int, Price float, DocStatus bit)

--my test values
insert into #InvoiceDocument (InvoiceID, ItemID, Price)
values (1, 1, 2.5), (1, 2, 5.0), (1,5, null)


--my sample procedure
create procedure sp_ApproveInvoice @InvoiceID int
as
begin

    set nocount on

    select * into #temp
    from #InvoiceDocument
    where Price is null and InvoiceID = @InvoiceID

    if exists (select 1 from #temp)
    begin
        select InvoiceID, ItemID, Price from #temp 
        RAISERROR ('There are non priced items. Invoice can not be approved!',16, 1)    
        return   
    end

    update #InvoiceDocument
    set DocStatus = 1
    where InvoiceID = @InvoiceID

end

when I execute this:

sp_ApproveInvoice 1

It both generates a resultset (Results), and an error message (Messages).

On the vb6 side, vb6 can not catch the error here.

Answer 1

You need to RAISERROR in the stored proc to set the error.

Or use output parameters. Or the RETURN statement

Or add logic to distinguish "success" and "fail" recordsets in the client code.

Currently, Err has no meaning because there is no error state

Personally, I use RAISERROR as per my question here with this template

Answer 2

Your particular case cannot be caught with Err since your stored procedure is not generating any error in the traditional sense---it's either giving an empty result set or a normal result set. How can VB know the semantics of a non-empty result set?

For this to work, you either need to change your stored procedure to raise error OR you need to check the result set returned by the stored procedure directly in your code (nothing to do with Err).