View Single Post
  #1 (permalink)  
Old June 21st, 2005, 10:58 AM
jeffharding jeffharding is offline
Registered User
 
Join Date: Jun 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using Oracle SP's in VBA

Hi all,
I am trying to use two oracle stored procedures by creating a cmd object in VBA. I am getting errors about expressions of wrong type. I did an example in TOAD and it worked fine. I just can't seem to set up my cmd parameters right. Here is my VB code.

Private Sub cmdReceipt_Click()
    Set cmd = New ADODB.Command
       With cmd
            Set prm = cmd.CreateParameter("RetVal", adBoolean, adParamReturnValue) 'Return value for SP
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_CO", adVarChar, adParamInput, 200, RecCo)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_PURCHASE_ORDER", adVarChar, adParamInput, 200, PurchaseOrder)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_ITEM", adVarChar, adParamInput, 200, RecItem)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_QTY", adInteger, adParamInput, , Qty)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_REC_DATE", adDate, adParamInput, , RecDate)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_WAYBILL", adVarChar, adParamInput, 200, RecWaybill)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_SERIAL_NUMBER", adVarChar, adParamInput, 200, RecSerialNumber)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_LOT_NUMBER", adVarChar, adParamInput, 200, RecLotNumber)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_LOT_SELL", adDate, adParamInput, , LotSell)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_LOT_EXPIRE", adDate, adParamInput, , LotExpire)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_MSG", adVarChar, adParamOutput, 200, RecMsg)
            .Parameters.Append prm
            .ActiveConnection = MIConn
            .CommandType = adCmdStoredProc
            .CommandText = "PO_RECEIPT"
        End With
            cmd.Execute


            If cmd.Parameters("Retval").Value Then
                MsgBox ("Success")
            Else
                MsgBox ("Failed")
            End If

End Sub


Here is the basis of the SP:

CREATE OR REPLACE function po_receipt(p_co in varchar2,
                       p_purchase_order in varchar2,
                       p_item in varchar2,
                       p_qty in number,
                       p_rec_date in date,
                       p_waybill in varchar2,
                      p_serial_number in varchar2,
                      p_lot_number in varchar2,
                      p_lot_sell in date,
                      p_lot_expire in date,
                       p_msg out varchar2) return Boolean
IS
  ct number;
    v_co varchar2(5) := nvl(p_co,'100');
    v_sql_code number;
    v_stock_qty number;
    v_po_receive_key number;
    v_line_key number;
    v_waybill varchar2(30);



This is how i got it to work thru SQL Editor:

DECLARE
  RetVal Boolean;
  P_CO VARCHAR2(200);
  P_PURCHASE_ORDER VARCHAR2(200);
  P_ITEM VARCHAR2(200);
  P_QTY NUMBER;
  P_REC_DATE DATE;
  P_WAYBILL VARCHAR2(200);
  P_SERIAL_NUMBER VARCHAR2(200);
  P_LOT_NUMBER VARCHAR2(200);
  P_LOT_SELL DATE;
  P_LOT_EXPIRE DATE;
  P_MSG VARCHAR2(200);

BEGIN
  P_CO := '100';
  P_PURCHASE_ORDER := '1900';
  P_ITEM := 'WATER';
  P_QTY := 2;
  P_REC_DATE := '01-MAR-05';
  P_WAYBILL := NULL;
  P_SERIAL_NUMBER := NULL;
  P_LOT_NUMBER := NULL;
  P_LOT_SELL := NULL;
  P_LOT_EXPIRE := NULL;
  P_MSG := NULL;

  RetVal := PO_RECEIPT ( P_CO, P_PURCHASE_ORDER, P_ITEM, P_QTY, P_REC_DATE, P_WAYBILL, P_SERIAL_NUMBER, P_LOT_NUMBER, P_LOT_SELL, P_LOT_EXPIRE, P_MSG );
  COMMIT;
END;

There is probably some syntax difference. Can someone help me out??