Avatar of nds_rahulmistry
nds_rahulmistry asked on

What's the problem with ADO command with adAsyncExecute

I am using VB6 as front-end and Oracle 10g as back-end.

I have a stored procedure in Oracle to generate Stock as on Given Date. The stored procedure is as following :

Oracle Side

Create or Replace Procedure GENERATE_BOOK_STOCK ( argTransDate DATE, argBookStockName VARCHAR2(50), argBookStockDate DATE, argBookStockRemarks VARCHAR2(250), argUserName VARCHAR2(15) )
IS
TID NUMBER;
BEGIN
      SELECT BOOK_STOCK_SEQ.nextval INTO TID FROM DUAL;

      INSERT INTO BOOK_STOCK_HEADER (TRANS_ID, TRANS_DATE, BOOK_STOCK_NAME, BOOK_STOCK_DATE, REMARKS, USER)
      SELECT TID, argTransDate, argBookStockName, argBookStockDate, argBookStockRemarks, arguserName from DUAL;

      INSERT INTO BOOK_STOCK(TRANS_ID, PRODUCT_ID, PLANT_ID, BATCH_ID, STOCK_QTY, UNIT_ID)
      SELECT TID, PRODUCT_ID, PLANT_ID, BATCH_ID, SUM(IN_FLOW - OUT_FLOW) AS STOCK_QTY, UNIT_ID
      FROM VU_LEDGER WHERE TRANS_DATE <= argBookStockDate
      GROUP BY TID, PRODUCT_ID, PLANT_ID, BATCH_ID, UNIT_ID;

      COMMIT;
END;



Visual Basic Side

I am using a Data Access Class called clsDAL. The class contains a function to execute Oracle Stored Procedure.
In my Form I have declared an ADODB recordset using WithEvents so that I can have more control.
I am executing the stored procedure asynchronusly so that it will execute in background and will not block my VB Application.



In the Form

dim cDAL as clsDAL
Private WithEvents rstBS As ADODB.Recordset      'To hold Book Stock Recordset


Private Sub Form_Load()
    Set cDAL = New clsDAL
End Sub


Private Sub cmdGenerateBookStock_Click()
      Dim CommandText As String
      CommandText =       "{CALL AURAMRP.GENERATE_BOOK_STOCK(SYSDATE,'" & BookStockName.Text & _
                  "', '" & DTPicker1.Value & "', '" & Remarks.Text & "','" & USERID & "'}"
      Set rstBS = cDAL.ExecuteStoredProcedure(CommandText)

      'THIS RECORDSET IS USED FOR FURTHER PROCESSING
End Sub


Private Sub rstBS_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
          MsgBox "Completed"
End Sub


In the class clsDAL


Public Function ExecuteStoredProcedure(ByVal CommandText As String) As ADODB.Recordset
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = dbCnn            'dbCnn is pre-defined valid connection
    cmd.CommandText = CommandText      
   
    'if I am using :   Set ExecuteStoredProcedure = cmd.Execute (, , adAsyncExecute),
    'it is not executing the command - it is not giving any error - and the bookstock is also not generated

    cmd.Execute , , adAsyncExecute            
   
    Set cmd = Nothing
End Function


I am not getting the message through my form function rstBS_FetchComplete.
Visual Basic ClassicOracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck