Solved

Progress Bar with SQL query

Posted on 2001-07-03
10
719 Views
Last Modified: 2008-02-01
How to make a progress bar during running of SQL statement.

I have a big database file, and i make a search by using SQL statment with dataControl.recordsource, how to know the progressing of search.

elzohry
0
Comment
Question by:elzohry
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
You can't correctly use a progress bar with a Select statement. The reason is that you don't have events triggered that informs you the percentage retreived.
0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
>>how to know the progressing of search.

Guess wildly.  That is my best suggestion.  emoreau is right.
0
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
if you use the ActiveX Data Objects, and declare a recordset object WithEvents, it has a FetchProgress event which gives you an approximation of an operation.  it may be of interest to you...
0
 
LVL 3

Expert Comment

by:nigelrowe
Comment Utility
AzraSound, I've tried using that and I couldn't get it to work properly. I might have been doing something wrong, I would be interested if you could explain a bit further.
0
 
LVL 4

Expert Comment

by:Nazdor
Comment Utility
To get Events, you might need to Specify that your CONNECTION is asynchronous, eg:

Dim WithEvents RS as RecordSet
Dim WithEvents CN As Connection

    CN.ConnectionString = ...

    CN.Open , , , adAsyncConnect

...

    RS.Open CMD, , adOpenStatic, , adAsyncExecute


Then use the VBIDE to see what Events are available and (eg) add debug statements to all of them so that you can see what is going on.

This way, use RS instead of dataControl.recordsource.  Then when the recordset has loaded (RS_FetchComplete?) simply: Set DataControl.RecordSource = RS


0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 43

Accepted Solution

by:
TimCottee earned 100 total points
Comment Utility
Azra is right, the key to getting this to work properly is using adUseClient for the cursor location. Here is an example of how this can be used:

Private WithEvents rstLogInfo As ADODB.Recordset
Private WithEvents cnnLive As ADODB.Connection
Private lngMax As Long

Private Sub Command1_Click()
    lngMax = 500000
    Set cnnLive = New ADODB.Connection
    Set rstLogInfo = New ADODB.Recordset
    cnnLive.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Live;Data Source=OSIUK009"
    cnnLive.Open
    With rstLogInfo
        .CursorLocation = adUseClient
        pgbFetch.Max = lngMax
        .Open "Select * From LotBaseLog", cnnLive, adOpenStatic, adLockReadOnly, adAsyncFetch
    End With
End Sub
Private Sub Command2_Click()
    rstLogInfo.Close
    cnnLive.Close
    Set rstLogInfo = Nothing
    Set cnnLive = Nothing
End Sub

Private Sub rstLogInfo_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    MsgBox "Complete"
End Sub

Private Sub rstLogInfo_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    lblFetch.Caption = Progress & " / " & lngMax
    pgbFetch.Value = Progress
    DoEvents
End Sub

There are a number of things to remember when doing this sort of thing. One is that you need to fully manage the lifetime of the asynchronous call and ensure that you cancel it and set the object to nothing in order to release the resources. The important thing to remember though is that this is all irrelevant when doing a select statement; the reason being that until you have completed the select you don't know how many records will be returned, I have used an expected value of 500K in this example as I know that the table in question has approximately this number of rows. The Progress and MaxProgress values returned in the event are only related to the approximate number of rows processed so far and as such cannot be used as an accurate guide to progress. You CAN use this to indicate that some progress is continuing but NOT to determine accurately the extent of that progress. Another thing to remember is that you cannot access these events from the data control though I am not sure from memory whether the ado data control will allow these events.

I hope this helps (explain if not solve) the situation.
0
 
LVL 4

Expert Comment

by:Nazdor
Comment Utility
Another thought on this is to find out how long the command will take before running it - depends on the server, but on DB2 if you specify a CommandTimeout of zero (0), then it will return an error saying the specified command's estimated query processing time of xxx exceeds the maximum of 0.  (or words to that effect).

So parse that result to get the estimated query processing time, set a progress bar to that value then run a timer with one second intervals to update the progress bar.

Bear in mind that (I believe), Timers don't work Asyncronously if the program is run from the VBIDE, but they do probably work fine when run from a compiled exe - if not, then use SetTimer and KillTimer which should work as required (but it also depends on the ODBC being used).


Private Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long
0
 
LVL 3

Expert Comment

by:nigelrowe
Comment Utility
Thanks for the wonderful explanation Tim, I'll try it when I get time.
0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
Interesting.  Didn't know this was available.
0
 

Author Comment

by:elzohry
Comment Utility
Thanks for All, special thanks to AzraSound
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now