Stored Procedures, VB6 and ADO RecordsAffected

From: Al Reid <areidjr_at_reidhyphenhome.com>
Date: Tue, 7 Oct 2003 07:38:17 -0400
Message-ID: <vo595g66gv5g03@corp.supernews.com>

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.

--
Al Reid

Received on Tue Oct 07 2003 - 06:38:17 CDT