VB freezes while running SP

[MDA]

Hi all,

In my VB app, I execute a stored procedure... After executing the stored procedure, its seems VB is stuck waiting for some response from SQL server and I cannot do anything in VB until the SP is complete.

For example, if I want to show another form or even a msgbox, it will not show until the stored procedure is complete?? Why is this?? Is there anyway to free up VB while the SP is running? Note: the SP runs transactions and does not need to return any results to VB, other then to say it is complete.

Thanks for any ideas on this issue!!

Best regards,

Mike

 

[chiph]

Mike -

I found this example on MSDN that shows how to make asynchronous calls to ADO.

Chip H.

[tt]
Dim WithEvents conn As ADODB.Connection

Sub Form_Load()

Dim cmd As New ADODB.Command
Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=SQLOLEDB;Data Source=sql65server;User ID=sa;Password="""""";Initial Catalog=pubs"
conn.Open

cmd.Execute "select * from authors", conn, adAsyncExecute
Debug.Print "Command Execution Started."

End Sub

Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)

Debug.Print "Completed Executing the Command."

End Sub
[/tt]

 

[MDA]

Thanks Chip... You got me on the right track... However.. I am having another related problem now...

I am trying to loop until the stored procedure ends so that I can continue....

My syntax looks like this....
-------------------------------------
...
cmd.Execute , , adAsyncExecute

'** Show another form to update user on progress.. (code excluded)

Do Until cmd.State <> adStateExecuting
Debug.Print &quot;Executing...&quot;
Loop

------------------------------------------
However, this syntax does not seem to work??? It will just continue to run until it times out!! I have done a debug.print without the loop and it returns 0 (closed) so I tried using adStateClosed in place of adState... but does not work.

I have other code in the sub that I want to run after the execution is complete but not during. However, I need to use the adAsyncExecute to show the other form during the SP execution.

Also note.. the SP does not return any results.. I am thinking that perhaps the state is different when you are just executing a stored procedure????

Any suggestions are greatly appreciated!!

Regards,

Mike