Help with Modal and no modal forms

[kathyms]

Hi, i'm having troubles when trying to show progress in a long operation, the problem is that i don't know how long is it going to take, because mi form calls a procedure in oracle and then returns with some information, can someone tell me any ideas on how to show progress without knowing how long is it going to be. I try to show a window saying "Processing..." but since my application is based on modal forms, i can't show it as no modal. Any ideas please????

hope u understand my very basic english

[RobDog888]

Maybe add a statusbar with "Processing" in it?

[kathyms]

mmm but there are no status before nor after that, it's a form that the user fill with some register information, and since the database server is slow and far, i need to show something that indicates that the application is still alive during the oracle procedure and then returns like nothing happened with a confirmation value and other steps follow.

[RobDog888]

The status bar is a control that go across the bottom of a form for displaying "status" type information. Just add it to your form from the common controls.

[Hack]

Is there looping involved in this long process?

If so, then that is where you would increment your progressbar.

If not, then showing a static message like "Processing..." is about your only option.

[RobDog888]

Its executing a stored procedure so its very difficult to retrieve any progress info.

[Hack]

Its executing a stored procedure so its very difficult to retrieve any progress info.

That is very true, but a "procedure in oracle" does not necessarily equal a stored procedure in oracle.

[RobDog888]

Well yes, that could be subjective to how the thread poster means it. But still there isnt too many options on retrieving progres info no matter if its a sp, adhoc, function, etc.

[kathyms]

yes, it's a stored procedure in oracle, and there's no looping, it's only one procedure, that takes at least 10 minutes to return with a answer, and during that the application doesn't do anything and sometimes looks like dead, that's why i need to show that it's still working, is there any way to show some movement, not necesarilly a especific progress, but maybe something like "Processing." and then "Processing.." and then "Processing..." and then again "Processing." until oracle finish?

[RobDog888]

You can use a timer event to do the updating of "Processing." "Processing.." "Processing..." for a label caption or in some other control. Do use a DoEvents where your executing the sp.

Or maybe play an AVI file.

[Hack]

is there any way to show some movement, not necesarilly a especific progress, but maybe something like "Processing." and then "Processing.." and then "Processing..." and then again "Processing." until oracle finish?

No...this would require sometype of looping so, if you could do that then you could update the progress with a progressbar. Your application will appear to be down and out. I would suggest using a statuspanel as Rob suggested, and along with "Processing", I would put something like..."Processing...this could take as much as 10 minutes, so please be patient."

At the end, make sure you pop up a message box indicating it has completely so there will be no mistake with the user that it is actually done.

[RobDog888]

With SQL Server you can get some returns like records affected and such but I am not familiar with Oracle. You might want to search or check thier help file for any return parameters or switches.

[kathyms]

where do i use the DoEvents? beacuse i put it in the timer event, but i doesn't do anything when the procedure is running, and moves again when the procedure stops

[Hack]

where do i use the DoEvents? beacuse i put it in the timer event, but i doesn't do anything when the procedure is running, and moves again when the procedure stops

Doevents is commonly used in loops, which you don't have here. Your procedure is taking up all of the processing time in your application because there is nowhere from within it to pause it long enough for your application to do something else, like update a progress bar.

[RobDog888]

Or if you have timer event procedure processing at the same time your running a call to a db.

[kathyms]

can i have a timer running at the same time that the stored procedure is running?

[Hack]

Or if you have timer event procedure processing at the same time your running a call to a db.

Hmmm...now thats an interesting thought and something I've not tried.

Will a timer continue to fire while executing a query or stored procedure?

[RobDog888]

AFAIK, the Timer control executes on a secondary process and not a secondary thread so it should work. I have seen the Timer event still fire even when a Modal MsgBox is being displayed.

[kathyms]

i've tried and it but it hasn't worked for me, but maybe i'm not doing or wirting everything i'm supposed to..... when the procedure is running, everything else stops, is there a way that it doesn't stop??

[kathyms]

AFAIK, the Timer control executes on a secondary process and not a secondary thread so it should work. I have seen the Timer event still fire even when a Modal MsgBox is being displayed.

u lost me

[Hack]

u lost me

He is talking about how things work within the Windows Operating System environment.

You said you tried it. Meaning you tried using a timer? What exactly did you do?

[kathyms]

yes, i tried using a timer and the doevents for the label's caption every 100 miliseconds, but when i call the procedure, doesn't move anymore until the procedure is over.

[RobDog888]

Ok, then its a synchronous call and unless you created an Out of Process ActiveX EXE to work in conjunction with your app, it wont be possible to do what you want.

[Hack]

yes, i tried using a timer and the doevents for the label's caption every 100 miliseconds, but when i call the procedure, doesn't move anymore until the procedure is over.

Well, it was kind of a shot in the dark.

[RobDog888]

It does work with other logic like the Modal MsgBox scenerio I posted earlier but I guess the ADO call doesnt work the same way. Good thing I posted AFAIK.

[Hack]

It does work with other logic like the Modal MsgBox scenerio I posted earlier but I guess the ADO call doesnt work the same way. Good thing I posted AFAIK.

It would have been way cool if it did work, but then again Rob, you got to think that if it did work, someone would have posted an example of it by now (I would think anyway)

[RobDog888]

True in a sense that most members are spammers like us. Maybe there is some member that knows but isnt online or seen this thread yet or maybe a detailed search on MS and here would turn something up.

[Hack]

True in a sense that most members are spammers like us. Maybe there is some member that knows but isnt online or seen this thread yet or maybe a detailed search on MS and here would turn something up.

You and I both have been around this forum for a while and we both have seen questions on this issue. For that matter, in our professional lives we have both faced this issue. I've done some pretty intense Googling, and I've not found anything.

Personally, I have always gone with your suggestion on the status panel, and a nebulus message indicating that the process could take a while.

[RobDog888]

The only person I can think of that would know, but for SQL Server and not Oracle, would be either szlamany or brucevde.

[Hack]

The only person I can think of that would know, but for SQL Server and not Oracle, would be either szlamany or brucevde.

We well see. I just sent a PM to both of them and GaryMazzone (he is a darn good Oracle dude) and asked them to take a look at this thread.

[RobDog888]

Cool, I'll keep an eye out for any replies so I will know any possible solutions for next time.

[szlamany]

Starting with ADO 2.0 "asyncronous" calls are possible.

I've never done one myself.

Apparently they are only part of the CONNECTION and RECORDSET objects - so if you are using a COMMAND object, this might not be possible. Although you can execute a COMMAND from the CONNECTION object - not sure if that's the same.

And I'm not even sure if the ORACLE driver supports ASYNC ADO calls.

How are you calling the SPROC from VB?

[RobDog888]

Thanks Steve for pointing out the obvious (Bangs head on desk).

From the help file...

Dim conn As New ADODB.Connection
            
Conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql65server;" _
   & "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open , , , adAsyncConnect
 
'Modified...
If conn.State = adStateExecuting Then 'Or adStateFetching
   Debug.Print "Still Executing"

[RobDog888]

Found another more relevant code example.

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

[szlamany]

RD - that was basically where I was headed - google for ADO that is not ADO.Net has become useless - so I was looking through a WROX book for examples...

But those two appear to point out exactly what has to happen.

[szlamany]

apparently the command object does handle async (as RD has pointed out).

My ADO 2.6 reference book says that as well...

options are:

adAsyncExecute - obvious
adAsyncFetch - initial batch of rows are fetched, then remaining are fetched asyncronously
adAsyncFetchNonBlocking - appears to be useful if you are going to be "searching" the recordset - returns last row if requested row not yet fetched

[RobDog888]

Ok, found a good example that you could use with the progressbar to show progress. The code example will need to be modified as its using a data grid.

Option Explicit
 
   Const strConn = "DSN=Pubs"
   Const strDefaultSQL = "SELECT * FROM Titles"
 
   Dim cn As ADODB.Connection
   Dim WithEvents rs As ADODB.Recordset
 
   Private Sub Form_Load()
      Command1.Caption = "Go"   
      Text1.Text = strDefaultSQL
   
      Set cn = New ADODB.Connection
      cn.Open strConn
   End Sub
 
   Private Sub Command1_Click()
      Dim strSQL As String
      strSQL = Text1.Text
   
      Set rs = New ADODB.Recordset
      With rs
         .CursorLocation = adUseClient
      
         .Properties("Initial Fetch Size") = 2
         .Properties("Background Fetch Size") = 4
      
         Debug.Print "Start"
         Debug.Print "Initial Fetch Size: " & _
                     .Properties("Initial Fetch Size")
         Debug.Print "Background Fetch Size" & _
                     .Properties("Background Fetch Size")
      
         .Open strSQL, cn, , , adAsyncFetch
      End With
   End Sub
 
   Private Sub rs_FetchProgress(ByVal Progress As Long, _
                                ByVal MaxProgress As Long, _
                                adStatus As ADODB.EventStatusEnum, _
                                ByVal pRecordset As ADODB.Recordset)
                                
      Debug.Print "Fetch: " & Progress & _
                  "  Max: " & MaxProgress
      
   End Sub
 
   Private Sub rs_FetchComplete(ByVal pError As ADODB.Error, _
                                adStatus As ADODB.EventStatusEnum, _
                                ByVal pRecordset As ADODB.Recordset)
                             
      If adStatus <> adStatusOK Then
         Debug.Print "Failed"
         Debug.Print "Error: " & pError.Number & " - " & pError.Description
      Else
         Set DataGrid1.DataSource = pRecordset
         Debug.Print "Done"
      End If
   
   End Sub

[szlamany]

btw - I've got a couple of places where SPROCS take 10 or 15 minutes - I've been wanting to switch to ASYNC mode myself...

kathyms - please get back to us with your success in using this method - thanks!

[RobDog888]

Ya, me too. See I am still learning new VB 6 code even to this day

[GaryMazzone]

I haven't found a method yet in oracle. The call to an Oracle SP is different the for SQL Server. The command object will not accept a for asyncronous operations.