Re: execute stored procedure asynchronously

JTL wrote:
I'm trying to asynchronously execute a stored procedure against SQL
Server from a VB6 client application and can't seem to make it work.
The error I'm getting is: "Operation cannot be performed while
connecting asynchronously". If I remove the "adAsyncConnect" option
from the connection string, the procedure runs fine-

Here is my sample db connection code:

sConnString = "Provider=SQLOLEDB;Data Source=myServer;Initial
conn.Open sConnString, "username", "password", adAsyncConnect

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spMyStoredProcedure"
cmd.Parameters.Append cmd.CreateParameter("param1", adVarChar,
adParamInput, 36, param1)
cmd.Parameters.Append cmd.CreateParameter("param2", adSmallInt,
adParamInput, , param2)

Did you instantiate your conn object using the WithEvents keyword? The
conn object needs to be a module-level variable.

"Asynchronous" means subsequent statements are executed without waiting
for the asynchronous statement to complete. Which means that you are
attempting to cass that Execute method before the connection is
connected. You might want to consider executing that command in the
connection's ConnectComplete event ...

Wait a minute ... you want to _execute the procedure_ asynchronously,
not _connect_ asynchronously! You need to remove the adSyncconnect
constant and use the adAsyncExecute option in the call to Execute to
make it execute asynchronously. Since it appears the proc is not
returning any records, you should also use adExecutenoRecords so ADO
does not waste CPU and resources creating an empty recordset object:

cmd.Execute ,,adAsyncExecute + adExecutenoRecords

Bob Barrows

PS. The ADO documentation can be found here:
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.