Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Show status of loading DataSet by Progress Bar

Posted on 2004-11-02
17
Medium Priority
?
688 Views
Last Modified: 2008-01-09
Hi,

I have to present the status of loading data into a data grid by progress bar.
I have a SQL query that executes for about one minute. During that time (and loading data into grid), I need to present percentage of loaded data (or executed SQL) by progress bar. I do not know how many records will be returned from the query (nor want to execute somethink like "select count(*) from ....").
I have VB code like this :

Dim ds As DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql_query, DB_Connection_String As String
....
ds = New DataSet
da = New OleDb.OleDbDataAdapter(sql_query, DB_Connection_String)

da.Fill(ds)

Thanks, Peter
0
Comment
Question by:pnedic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12472425
Hi peter,

You cannot do that during a fill,

There are a couple of things you can do,

1. Use a datareader
2. Use a seperate thread to simulate the data being (very inaccurate)
3. just show a hourglass pointer or show a popup window during the fill
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12472467
Hold the phone, just thought of something, Give me a second and I'll test it first.
0
 
LVL 3

Author Comment

by:pnedic
ID: 12472519
OK, I'm reading info about DataReader
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12472731
Hi Pnedic,

I found possible other way but to get it the work properly you really need to now the maximum number of rows that are being read. Because the progressbar needs a .maximim value
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12472741
The same actually goes for the datareader, there you also need to know the maximum value of the progress bar
0
 
LVL 3

Author Comment

by:pnedic
ID: 12472884
Hm,

well, when the destiny is such terrible :), let's suppose I know the number of records to be returned (e.g. 1500). Then how to implement progress bar : using DataReader (OleDbDataReader) or I can use DataSet as well ?

Thanks
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 750 total points
ID: 12472954
Hi Pnedic,

Well you actually can do it during the fill by using the rowchanged event of the dataset, something like this
you have to use this line for it to work (SqlDataAdapter1.AcceptChangesDuringFill = True)

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ds.Tables.Add("test")
        SqlDataAdapter1.AcceptChangesDuringFill = True
        AddHandler ds.Tables(0).RowChanged, AddressOf ron
        SqlDataAdapter1.Fill(ds, "test")
        DataGrid1.DataSource = ds.Tables(0)
    End Sub

    Private Sub ron(ByVal sender As Object, ByVal e As System.Data.DataRowChangeEventArgs)
        pb1.Value += 1
    End Sub
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12472978
HMMM, you actually don't need this line

(SqlDataAdapter1.AcceptChangesDuringFill = True)


sorry my mistake :-)
0
 
LVL 3

Author Comment

by:pnedic
ID: 12473427
Hm, tomorrow I'll let you know about the result. I need to check this

Thanks
0
 
LVL 3

Author Comment

by:pnedic
ID: 12483341
Hm, nothing helped. So I given up of progress bar.

Other thing what I've tried is to remove progress bar and replace it with some animated gif.
What happens is that subform (containing animated gif) is loaded but at the moment DataSet starts loading data, animated gif becomes frozen.
My idea was, at form_load event, to open subform with animated gif and when DataSet (and data grid) is loaded with data, to close subform.

Any idea how to overcome this ?

Regards
0
 
LVL 3

Author Comment

by:pnedic
ID: 12483371
Yap, one more thing, I have also tried to force refreshing of subform with using Timer object, but also got the same result.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12483458
Hi Pnedic,

What do you mean with nothing helped, do you get an error. Because I tested my solution and it works perfectly (if you know the total rows that are read).

About the animated gif, if you want that to work you have to do that in a seperate thread otherwise the fill will use all the cpu and leave nothing for the gif.
0
 
LVL 3

Author Comment

by:pnedic
ID: 12483632
Sorry, yes your solution is OK so far as I know the total number of records to be returned. Since I deal with an extreme "heavy" sql (returns more than 350K rows), executing this sql two times (first to get count and second to get data) is extremly "expensive". (I will need to pay attention to optimizing of SQL and tables - beside this programming problem). This is the reason why I have given up of progress bar.

Can you give some practical example of generating separate thread that runs gif...

Thanks
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12484089
So what did you do ?

by the way I tested an executescalar on 500.000 records (to get to total number of rows) and it only took 0.7 seconds


0
 
LVL 3

Author Comment

by:pnedic
ID: 12484101
Done using thread(s).
I have placed executing SQL and loading DataSet and data grid in separate thread. Activating subform has placed after activating the loading data thread

Thanks
0
 
LVL 3

Author Comment

by:pnedic
ID: 12484184
executescalar ?

can you be more specific ?

Anyhow, I have tested executing SQL in TOAD, and also it takes one year (definitely it needs some actions on sql / table(s))
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12484265
.executescalar only returns one value, and is very fast (little overhead)

        dim x as integer
        Dim mycom As New OleDb.OleDbCommand("select count(*) from tabel4", OleDbConnection1)
        OleDbConnection1.Open()
        x = mycom.ExecuteScalar
        OleDbConnection1.Close()

as you can see this was done on a access database (which is pretty slow), I tested the same on an sql server with a stored procedure and there it only took 0.3 seconds


0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

604 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