Re: Stored Procedures, VB6 and ADO RecordsAffected

In article <vo595g66gv5g03_at_corp.supernews.com>, areidjr_at_reidhyphenhome.com says...
> I have an Oracle Stored Procedure:
>
> PROCEDURE LockInvoice(oReturn OUT VARCHAR2, inInvID IN NUMBER)
> IS
> BEGIN
> oReturn := 0;
>
> UPDATE APINVOICE.INVOICES
> SET STATUS = 1
> WHERE INV_ID = inInvID AND
> STATUS = 0;
>
> EXCEPTION
> WHEN OTHERS THEN
> oReturn := SQLERRM;
> END;
>
> When I execute it using a command object in a VB6 sp5 program as follows:
>
> With adoCmd
> Set .ActiveConnection = cn
> .CommandType = adCmdStoredProc
> .CommandText = "APINVOICE.WESCO_AP_APP.LockInvoice"
> .Parameters.Append .CreateParameter("oReturn", adVarChar, adParamOutput, 50)
> .Parameters.Append .CreateParameter("inInvID", adNumeric, adParamInput, , InvID)
> .Execute lngRecordsAffected, , adExecuteNoRecords
> If .Parameters("oReturn").Value = 0 Then
> LockInvoice = CBool(lngRecordsAffected)
> End If
> End With
>
> The lngRecordsAffected variable returns 1 even when no records are updated.
>
> However, If I do the following:
>
> With adoCmd
> Set .ActiveConnection = cn
> .CommandText="UPDATE APINVOICE.INVOICES SET STATUS = 1 WHERE INV_ID = 1 AND STATUS = 0"
> .CommandType=adCmdText
> .Execute lngRecordsAffected, , adExecuteNoRecords
> LockInvoice = CBool(lngRecordsAffected
> End With
>
> The lngRecordsAffected variable returns 0 when no records are updated.
>
> I see the same results with either the MSDAORA or OraOLEDB providers.
>
>
> Any Ideas as to why would be greatly appreciated.
>

Not sure why that's happening, could be a driver problem. But as a workaround you could add another OUT parameter and then add this to your stored proc after the UPDATE statement.

        NewOutParameter := SQL%ROWCOUNT;

That would be the oracle-safe way to do it.

• Tom