troubleshooting Question

What's the problem with ADO command with adAsyncExecute

Avatar of nds_rahulmistry
nds_rahulmistry asked on
Oracle DatabaseVisual Basic Classic
1 Comment1 Solution3600 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros