[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Form Screen Blanking out during processing in VB.NET

I am loading an access database from an EXCEL spreadhseet.  The first part of the code as follows get the records from the EXCEL spreadsheet (16613 records).

Dim strSQL As String = "SELECT * from [Texp Points$]"

ContentOleDBConn = New OleDb.OleDbConnection
ContentOleDBConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strExcelFilePath & ";Extended Properties=""Excel 8.0;HDR=YES;"""

Dim DataAdaptor As New OleDb.OleDbDataAdapter(strSQL, ContentOleDBConn)
Dim Ds As New DataSet
Dim DrFields As DataRow

      Try
         DataAdaptor.Fill(Ds)
         intRowCount = Ds.Tables(0).Rows.Count   'Returns 16613.

I then proceed the add the records to the database using the follow code during the process trying to display a ProgressBar so the user can see whats happening:

         ProgressBar1.Step = 1
         ProgressBar1.Minimum = 0
         ProgressBar1.Maximum = intRowCount

         intColumnCount = ds.Tables(0).Columns.Count
         TextBox2.Text = "Retrieved " & intRowCount & " records which have " & intColumnCount & " Columns per row"
         TextBox2.Refresh()

         ContentOleDBConn.Close()
         ContentOleDBConn = New OleDb.OleDbConnection
         ContentOleDBConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath

        'Deletes existing records ....
         strSQL = "Delete * From PointsFile"
         Dim delDataAdaptor As New OleDb.OleDbDataAdapter(strSQL, ContentOleDBConn)
         ContentOleDBConn.Close()

        'Proceed to add the new records ....
         strSQL = "Select * From PointsFile"
         Dim newDataAdaptor As New OleDb.OleDbDataAdapter(strSQL, ContentOleDBConn)
         Dim cb As OleDb.OleDbCommandBuilder
         Dim newDs As New DataSet
         Dim newDrFields As DataRow
         newDataAdaptor.Fill(newDs, "PointsFile")

         For intCounter = 0 To intRowCount - 1
            DrFields = Ds.Tables(0).Rows(intCounter)
            newDrFields = newDs.Tables("PointsFile").NewRow()

            newDrFields("TEBusiness") = "EXP"
            newDrFields("TECountry") = "AUSTRALIA"
            newDrFields("TEState") = Mid(DrFields(1), 1, 3)
            newDrFields("TEPostcode") = DrFields(2)
            newDrFields("TESuburb") = DrFields(3)
            newDrFields("TEDepotCode") = UCase(Mid(DrFields(4), 1, 3))
            newDrFields("TEZone") = Mid(DrFields(5), 1, 4) & "-" & Trim(DrFields(6))

            newDs.Tables(0).Rows.Add(newDrFields)
            cb = New OleDb.OleDbCommandBuilder(newDataAdaptor)
            newDataAdaptor.InsertCommand = cb.GetInsertCommand
            newDataAdaptor.Update(newDs, "PointsFile")
            newDs.AcceptChanges()

            ProgressBar1.PerformStep()
        Next

      Catch ex As Exception
         MessageBox.Show(ex.Message)
      End Try

This works OK and retrieves and loads the records OK (a bit slow though).  The only thing that is going wrong is that the form screen blanks out during processing and the ProgressBar is not displayed.  The form is totally blank.  When completed a MessageBox message is displayed ("Completed....") as defined further down in the program.  Can anyone pleas help on why the form screen would be blanking out?
0
bertpet
Asked:
bertpet
  • 4
  • 4
  • 3
1 Solution
 
MsShadowCommented:
The form screen is blanking out cause you are working in the same thread. So as soon as you start loading your data that thread is using its resources to do the conversion and does not handle painting your form or changing your progress bar. As soon as the loading is finished the thread can handle the next steps which is drawing the progressbar and showing hte messagebox.
0
 
bertpetAuthor Commented:
Thank you very much.  Now how do I fix it?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mike TomlinsonMiddle School Assistant TeacherCommented:
The "quick fix" is to add Application.DoEvents() in your loop:

        For intCounter = 0 To intRowCount - 1

            ... code ...

            ProgressBar1.PerformStep()
            Application.DoEvents()
        Next
0
 
bertpetAuthor Commented:
As I am not sure yet on how to implement Asynchronycal programming I am accepting and using the Appplications Do.Events(), which does the job admirably.
0
 
MsShadowCommented:
:-) I got owned, Application.DoEvents is a nice solution.
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Lol...I wouldn't say you got "owned" MsShadow...

While my solution works, I was sure to point out that it is a "quick fix" as Application.DoEvents() is not usually considered the "proper way" of doing things.

Threading is obviously the correct way of doing it but to the uninitiated it is quite intimidating.  Someone who is "loading an access database from an EXCEL spreadhseet." is probably not a hard core seasoned OOP programmer.  =)
0
 
bertpetAuthor Commented:
Not quite sure why Loading an Access Database from an EXCEL spreadsheet disqualifies me as a hard core seasoned OOP programmer.  You are correct though I am not a hard core seasoned OOP programmer.  Perhaps you can help me with an example on how I would incorporate the code example I have provided above into asynchronical programming (the net is lacking a bit on examples in this area).
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Lol...

Take a look at these PAQs first:
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21741689.html
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21729609.html
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_21702866.html

Then try to apply it to your code.  If you have any questions, run into problems, or just can't get started let me know and I'll jump back in.

Feel free to jump in there as well MsShadow!   =)
0
 
MsShadowCommented:
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21729609.html

This is the best example to show how it works. Basically what you want to do is start a new thread that does the conversion. Then you start the conversion in that thread. Once the conversion is done you raise an event to notify to other thread (the one displaying the ui) of the fact that the conversion is done. Catch the event and display your conversion done message.

I'm not putting the full code here since you will never understand completely how it works till you tried to do it all yourself.
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Hehe...that's the second link I provided...that Idle_Mind guy is great...wait a minute...that's me!

j/k...            =)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now