While loading data to combobox show progressbar

[HydraNL]

Hi, I'm just a junior in VB.NET so please be patient.

I have an AccesDatabase that contains more then 900.000 records. In the Form_Load event these records are loaded into a combobox. This takes about 10 seconds. Sometimes more, sometimes less. To make the waiting for the user less boring I want to show a ProgressBar while loading the data to the Combo. Searched a long time on the internet, but can't find a good tutorial for a beginner anywhere. (not even here) I hope one of you guys could help me out.

The code I use to connect to the database:

Imports System.Data.OleDb

How do I implement the ProgressBar?

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

   Dim ConnectionString As String
   Dim DbConnection As OleDbConnection

   Dim DbCommand As OleDbCommand
   Dim DbDataReader As OleDbDataReader

   Try
      ConnectionString = _
      "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source = C:\test.mdb; Mode = Read;" & _
      "Persist Security Info = False"

      DbConnection = New OleDbConnection(ConnectionString)
      DbConnection.Open()

      DbCommandPlaats = New OleDbCommand("SELECT DISTINCT Field1 FROM TABLE", DbConnection)
      DbDataReaderPlaats = DbCommandPlaats.ExecuteReader

      Do While DbDataReaderPlaats.Read
         ComboBox1.Items.Add(DbDataReaderPlaats("PLAATS"))
      Loop

      DbDataReaderPlaats.Close()

How and where do I implement the ProgressBar? Perhaps I need to code another way. I'm not using "Main".

 

[HydraNL]

Do While DbDataReaderPlaats.Read
ComboBox1.Items.Add(DbDataReaderPlaats("PLAATS"))
Loop

Should be:

Do While DbDataReaderPlaats.Read
   ComboBox1.Items.Add(DbDataReaderPlaats("Field1"))
Loop

 

[ca8msm]

You could only implement a progressbar that updated at certain timer intervals unless you returned the records in stages. e.g.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Timer1.Interval = 1000
        Timer1.Start()
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        If ProgressBar1.Value <= 90 Then
            ProgressBar1.Value += 10
        Else
            Timer1.Stop()
        End If
    End Sub

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[HydraNL]

Well, I used that option, but it is not pretty. Like I said: Sometimes it takes longer, bus sometimes less.

What I want: When the reader starts to read

Do While DbDataReaderPlaats.Read

(I ques)
A Form with a progressBar that is reading/counting the records in Field1, then while loading the records in the combo showing the progress.

I hope you understand what I'm trying to achieve.

 

[SqueakinSweep]

A progress bar needs min,max and value to show a progress status. You cant get a record count from a datareader, so you cant populate the max value.

The alternatives are to implemement ca8msm's solution, or alternatively to get the data into a datatable, but that will also slow down the loading of data.

900,000 records also seems a bit excessive for a combo box

Sweep
...if it works dont mess with it

 

[ca8msm]

900,000 records also seems a bit excessive for a combo box

And for Access!

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[HydraNL]

No, not excessive. Works really fine. Never encountered problems. Using it for at least a year.

The Max value is known (908.751 records). Never more, never less.

 

[SqueakinSweep]

And for Access!

I dont think I would ever sleep at night, with so much data in an Access database...very scary!


Sweep
...if it works dont mess with it

 

[SqueakinSweep]

No, not excessive. Works really fine. Never encountered problems. Using it for at least a year.

Ouch...I really dont believe you should have said that so publicly and with such an air of confidence.




Sweep
...if it works dont mess with it

 

[ca8msm]

I've known Access to be very unstable with far less than 900,000 records and I would never trust it as far as I could throw it.

I think it was Rick who said, in another post, something similar to "Never trust a database that has a repair option in it's menu!". Oh how true!

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[HydraNL]

Ok..... What would you advice?

 

[HydraNL]

Is there an other way how to use the ProgressBar? I'm willing to recreate my programm. Just want to learn more.

 

[SqueakinSweep]

As I said earlier, you need to get your data into a datatable so that you can get a record count. If you can get data into a datareader, you should be able to adjust the code to get it into a dataset/datatable.

'dt is a Datatable
xProgBar.Minimum = 1
xProgBar.Maximum = dt.Rows.Count

dim iProgress as Integer=0
for each r as datarow in dt.rows
   ComboBox1.Items.Add(r("Field1"))
   iProgress += 1
   xProgbar.Value = iProgress
Loop

Sweep
...if it works dont mess with it

 

[HydraNL]

I will try that one out.. Thanx sofar dudes.

 

[ca8msm]

Ok..... What would you advice?

If you were talking about advise for a database there is a free version of SQL Server Express at

http://lab.msdn.microsoft.com/express/sql/

which is a lot more robust.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[HydraNL]

That is correct ca8msm.
Thank you I will check that one out also.

 

[HydraNL]

To bad ca8msm. This version is only for VS 2005 Beta 2. I've got VS.NET 2003.

 

[ca8msm]

SQL Server is a database - it is not dependant on any programming tool...

Check out the system requirements for it:

http://www.microsoft.com/downloads/...99-E74B-4AF8-90C7-FA0CA20A10C4&displaylang=en

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ca8msm]

You will however need version 2.0 of the framework installed

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[HydraNL]

Strange: It says that it's only for 2005 beta 2. I cannot install the SQL Server. I have the correct framework installed. I will look further to solve this problem.