I've written a small VB application that runs a SQL Server stored procedure via an ADO connection. The app is called from a Windows program. The procedure can take a minute to run, so I've increased the timeout value of the ADO connection above the default 30 seconds. I want to make users aware that the procedure is running 'in the background' and have tried adding a 'WAIT' splash screen, and changing the mouse pointer to an hourglass so that users can see that something is happening and don't start clicking frantically.

The problem is that my program appears to wait while the procedure runs. My splash screen will only display as a 'ghost screen' with a faint border, and is drawn fully once the procedure is complete. The mouse pointer briefly changes to an hourglass, but reverts to a standard pointer while the procedure runs.

Is this a common pitfall when executing database procedures with an ADO connection? How can I provide feedback to users while the stored procedure runs?


The code approximates to this currently:

Dim objADOdatasource As ADODB.Connection
Dim comADOdatasource As ADODB.Command
.....
.....

objADOdatasource.CommandTimeout > 30 seconds
comADOdatasource.CommandTimeout > 30 seconds

 With comADOdatasource
            .CommandText = cp_a_stored_procedure
            .CommandType = adCmdStoredProc 'Type : stored procedure
            .ActiveConnection = objADOdatasource.ConnectionString
 End With

display splash screen
mouse pointer = hourglass

comADOdatasource.Execute 'this is where the procedure is run

hide splash screen
mouse pointer = normal

...if return value is not success etc.

You may want to try inserting a Timer or the DoEvents function. Here is some info from MSDN...

DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.

Hope this helps.

Kenny Alligood

hi there..

while your program is running the executed command, is halted, so no doevents or such thing will work...
the best thing you can do, is before running the execute, show the wait window fully, doing a refresh so it viewed ok.
also you may want to put a timer on the wait window so you can run the query a second after this window appear..

HTH...

Gonzalo Bianchi

Thanks guys, setting a wait timer works fine. The wait time only has to be set at 1/10 of a second for my splash screen to display fully.

This is the code now:

        frmWait.Show
        Screen.MousePointer = vbHourglass

        iStart = Timer ' Set start time.
        Do While Timer < iStart + 0.1
            DoEvents ' Yield to other processes.
        Loop

        comADOdatasource.Execute

        frmWait.Hide
        Screen.MousePointer = vbDefault

        sReturnValue = sOutputParam.Value
        .........etc.