Help with Modal and no modal forms

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???? :D

hope u understand my very basic english :)

Maybe add a statusbar with "Processing" in it?

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.

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.

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.

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

Quote:

---

Originally Posted by RobDog888

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. :)

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. :)

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?

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.

Quote:

---

Originally Posted by kathyms

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.

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.

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

Quote:

---

Originally Posted by 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

---

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.

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

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

Quote:

---

Originally Posted by RobDog888

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?

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.

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?? :)

Quote:

---

Originally Posted by 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.

---

u lost me :confused:

Quote:

---

Originally Posted by kathyms

u lost me :confused:

---

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?

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.

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.

Quote:

---

Originally Posted by 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.

---

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

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. :D

Quote:

---

Originally Posted by 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. :D

---

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)

True in a sense that most members are spammers like us. :D 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.

Quote:

---

Originally Posted by RobDog888

True in a sense that most members are spammers like us. :D 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.

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

Quote:

---

Originally Posted by RobDog888

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.

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

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?

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

From the help file...

VB Code:

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"

Found another more relevant code example.

VB Code:

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

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.

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

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.

VB Code:

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

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!

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

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.

So SQL Server is better then Oracle ;) :D

That sounds like a severe limitation of Oracle. :(

This is what i use, i put the async mode in the execute, but now, how do i know when the procedure finish?

VB Code:

sSQL = "SIM_PA_CALCULO_COMISIONES.SIM_SPI_CALCULO_COMISIONES"
Set mStoredProcCmd = New ADODB.Command
With mStoredProcCmd
      .ActiveConnection = Cn
      .CommandType = adCmdStoredProc
      .CommandText = sSQL
                
      Set mStoredProcPrmIdMes = .CreateParameter("VI_NU_ID_MES", adInteger, adParamInput, 2, intMes)
      Set mStoredProcPrmIdAnhio = .CreateParameter("VI_NU_ID_ANHIO", adInteger, adParamInput, 4, intAnhio)
      Set mStoredProcPrmIdUsuario = .CreateParameter("VI_NU_ID_USUARIO", adInteger, adParamInput, , intIdUsuario)
                
      .Parameters.Append mStoredProcPrmIdMes
      .Parameters.Append mStoredProcPrmIdAnhio
      .Parameters.Append mStoredProcPrmIdUsuario
End With
        
mStoredProcCmd.Execute , , adAsyncExecute

Quote:

---

Originally Posted by 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.

---

You tried with ADO - newest version, like 2.8?

Is it the Oracle driver that doesn't support it?

What version of Oracle is he using, and how is he connecting also? I don't normally use the Oracle data objects in my apps. They might have something for setting the asyncronous connection there.

Dont you just change the connectionstring for Oracle and its the same when using ADO?

Where is you returnset? Is the command bring something back to the app? The ADO call to and Oracle SP normally take the ODBC form to operate propery

VB Code:

Set dbConn = New ADODB.Connection
With dbConn
    .Provider = "OraOLEDB.Oracle"
    .Properties("Data Source") = "ROWELLS"
    .Properties("User Id") = "wellsadmin"
    .Properties("Password") = "roweisgood"
    .Open
End With
 
Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = dbConn
With Cmd
    .Parameters.Append .CreateParameter(, adNumeric, adParamInput, , lngWellID)
    .Parameters.Append .CreateParameter(, adVarChar, adParamOutput, 50)
    .Parameters.Append .CreateParameter(, adNumeric, adParamOutput)
End With
 
If dbConn.State Then
    Cmd.Properties("PLSQLRSet") = True
    Cmd.CommandType = adCmdText
    Cmd.CommandText = "{CALL WellsAdmin.OneWellCount(?,?, ?)}"
    Set rs = Cmd.Execute()
End If

Yes I am using ADO2.8 there is another set of objects that are avail from Oracle that I normally do not use.

You can use the MS supplied version of the ADO driver but I always have trouble getting a return set back using it.

no, i don't need to return anything because it's a procedure that read some values in the database and inserts some others after some calculation, so in the normal mode i just wait for it to finish and when vb has the control again, it's shows a msgbox saying it was succesfull, but in the async mode, i need to know when it's finishing and i don't know for sure if it's still running.

post 37 by RD shows how to check the status of the execution.

Did you see that?

yes, but my problem is that i don't have anything to know when it's finished, on the example he uses kind of a loop because the rs fetch his records until he reaches to the number of rows obtained in the beginning of it, i don't have anything like that, or at least that was what i understand, please correct me if i'm getting this in the worng way :)

post #34 shows how to execute a command and an "event" that fires when the command is done - that seems like it will work even if a recordset is not involved.

Quote:

---

Originally Posted by RobDog888

Found another more relevant code example.

VB Code:

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

---

yes, haven't check that deeply, but my question is how or where can i call the conn_ExecuteComplete to know when the procedure is finished?

Can you think to write own exe without interface for that job.

1. Start program
2. Set running flag up on register
3. Do job
4. Turn running flag down
5. End program

Then your main program is just watching flag on register to know when job is done.

Quote:

---

Originally Posted by oh1mie

Can you think to write own exe without interface for that job.

1. Start program
2. Set running flag up on register
3. Do job
4. Turn running flag down
5. End program

Then your main program is just watching flag on register to know when job is done.

---

lost again :confused: this form is part of a bigger app that is based on modal forms, and what i need is to keep having control when i call a stored procedure to show some kind of progress, i can't do it in another forms because it is modal, so async mode for the procedure call should be my answer but i don't understand it completely yet :)

Quote:

---

Originally Posted by kathyms

lost again :confused: this form is part of a bigger app that is based on modal forms, and what i need is to keep having control when i call a stored procedure to show some kind of progress, i can't do it in another forms because it is modal, so async mode for the procedure call should be my answer but i don't understand it completely yet :)

---

If you is working with that procerude on separated thread. Then is it is not hanging main program. You can allways show some running envelopes or another symbols until running flag is up.

This was only one view. Sometimes when we think too complicated, then there is created thousand mails code, when it needs only couple lines.