[2008] Speeding Up this Code - Urgent!

[Wesley008]

Alright, I have this code which - loops through a text file (very big text file) to find the Course ID, then it adds the item to the List Box and while its doing that, it creates the .mdb file & adds some data from the text file to the database, and while all that is going, a Progress Bar ( which I have yet to figure out how to get it to increment with my Adding of Data ).

The Code is very slow and the application gets hung up with Not Responding if I click on it, I want the user to be able to click on the App to cancel the current import but at the moment it just freezes up.

Code:

Option Strict Off
Imports System.Data.OleDb
Imports System.IO
Module modImport
    Public Sub ImportCourse()
        With frmCourses
            If .ofdOpen.ShowDialog = DialogResult.OK Then
                Dim FileCopyName As String = Application.StartupPath & "\Data\Courses\" & Path.GetFileName(.ofdOpen.FileName.Remove(.ofdOpen.FileName.LastIndexOf("."))) & ".CSV"
                If File.Exists(FileCopyName) = True Then
                    File.Delete(FileCopyName) 'Temp
                Else
                    File.Copy(.ofdOpen.FileName, FileCopyName)
                    SQL = "SELECT [Classes(Import If #1)] FROM " & .ofdOpen.FileName
                    Dim Path As String = IO.Path.GetFullPath(.ofdOpen.FileName.Remove(.ofdOpen.FileName.LastIndexOf("\")))
                    Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";")
                    Command = New OleDbCommand(SQL, Connection)
                    Command.Connection.Open()
                    Adapter.SelectCommand = Command
                    DS = New DataSet
                    Adapter.Fill(DS)
                    Command.Connection.Close()
                    frmImport.pbImport.Maximum = DS.Tables(0).Rows.Count
                    frmImport.pbImport.Minimum = 0
                    For Each DRow As DataRow In DS.Tables(0).Rows
                        If .lstCourses.Items.Contains(DRow("Classes(Import If #1)")) = False Then
                            Connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Data\Courses\" & DRow("Classes(Import If #1)") & ".mdb")
                            CreateCourse(Connection.ConnectionString)
                            SQL = "INSERT INTO [Information] (CourseCode) VALUES (?); "
                            Command = New OleDbCommand(SQL, Connection)
                            Command.Connection.Open()
                            Command.Parameters.AddWithValue("CourseCode", DRow("Classes(Import If #1)"))
                            Command.ExecuteNonQuery()
                            Connection.Close()
                            frmImport.Show()
                            If frmImport.pbImport.Value < frmImport.pbImport.Maximum Then
                                frmImport.pbImport.Value += 5
                            End If
                        End If
                    Next
                    frmImport.Close()
                    File.Delete(FileCopyName)
                End If
 
            End If
        End With
    End Sub
End Module

[MaximilianMayrhofer]

If you don't want to hang the UI, call the sub using

ThreadPool.QueueUserWorkItem(AddressOf ImportCourse)

To cancel you'll have to regularly check a boolean value. Or you can kill the thread (not recommended).

[Wesley008]

Hedy, I did that but I get this error and poiints to .ofdOpen.ShowDialog

Current thread must be set to single thread apartment (STA) mode before OLE calls can be made. Ensure that your Main function has STAThreadAttribute marked on it. This exception is only raised if a debugger is attached to the process.

[dbasnett]

adding

application.doevents

in the for next loop will make the ui more responsive

[Wesley008]

So how do I fix the above problem & how do Ig et the progress bar to go with increase with the adding of the listbox items. ?

[Shaggy Hiker]

Why is Option Strict OFF? That alone will cost you in speed.

I don't see a loop, so I assume you didn't post that part, which doesn't really matter. DoEvents will interupt execution to allow the app to process any pending messages. Adding that line to the loop will remove the not responding issue and make the UI responsive. You would want to change the loop, though. If you have a Cancel button, just have it set a boolean to true, and in the loop, quit the loop if the boolean is true. Therefore, the DoEvents will allow the Cancel button click event to fire if the user clicks the button, which will set the boolean to true, which will stop the loop when execution comes back to the loop after the DoEvents.

As for the progress bar, can you figure out how much the total progress is such that you can determine how far along you are each time through the loop? If you can, then you have more options, otherwise you need a progress bar that is more like a spinning clock, or some such. You've seen those in plenty of places. They are just used to indicate that progress is happening, but they don't give the user any information as to how far along they are and how long they have to go.

[Wesley008]

Well what I'm doing atm is setting the maxium to the DS.tables(0).Rows.Count and then adding +5 to the values of the progress bar each time it loops but then near the end it says 'Number HEre' must be smaller than the max and bigger than min . I guess because its going over. So how would I stop that? I tried doing Value = 0 on the Progress Bar but it just restarted the whole PB.

[Shaggy Hiker]

Right, check whether the value+5 would be greater than the max value, and if it is, set the value to the max value.

[dbasnett]

i agree with shaggy about option strict, turn it on.

[Wesley008]

Value of '8860' is not valid for 'Value'. 'Value' should be between 'minimum' and 'maximum'. Parameter name: Value

with this code:

frmImport.Show()
                            frmImport.pbImport.Maximum = DS.Tables(0).Rows.Count
                            If frmImport.pbImport.Value >= DS.Tables(0).Rows.Count Then
                                frmImport.pbImport.Value = frmImport.pbImport.Maximum
                            ElseIf frmImport.pbImport.Value < DS.Tables(0).Rows.Count Then
                                frmImport.pbImport.Value += 5
                            End If

[demausdauth]

why increment by 5 each time? For each row you are going to add 5 but the max is N. If N is 600 rows then you will go over the max value by the time you hit row 120.

Either you can change your loop to not use For ... Each or you need to initialize a counter, and increment it 1 for each row processed (basically a good ole for...next loop)

[Shaggy Hiker]

I, too, don't see why you are incrementing by 5, but no matter. Change the code as such:

If frmImport.pbImport.Value > DS.Tables(0).Rows.Count+5 Then
 frmImport.pbImport.Value = frmImport.pbImport.Maximum
else
 frmImport.pbImport.Value += 5                          
End If

[Wesley008]

Yea, I changed around the code to do pbImport.increment(5) and it works like a charm. I was just following a tut I found through Google. I guess it wasn't very effictive.

[Shaggy Hiker]

So, did you get rid of that Option Strict OFF?

[Wesley008]

Yes, I changed it to Option Strict On, I was reading another post of yours where the OO said that 1 teacher said don't use the other said used and then you went into explaining and thats where I decided I would turn it on - but I turned it off becuse I as doing something like this

dim i as integer
i = i + 1

but then it said + was not a Option Stric thingy. so I turned it off, but forgot to turn it back on.

[Shaggy Hiker]

Good, one of my programs showed a 50% speed boost by turning Option Strict ON. You seem like a good candidate for that benefit.