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_STOC K(SYSDATE, '" & BookStockName.Text & _
"', '" & DTPicker1.Value & "', '" & Remarks.Text & "','" & USERID & "'}"
Set rstBS = cDAL.ExecuteStoredProcedur e(CommandT ext)
'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(ByV al 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.
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_STOC
"', '" & DTPicker1.Value & "', '" & Remarks.Text & "','" & USERID & "'}"
Set rstBS = cDAL.ExecuteStoredProcedur
'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(ByV
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.