[2008] 2 Questions: Access Database & Progress Bar

[Wesley008]

Question 1:
My Access File is Adding All my Courses that Contain More than 1 of into 1 Database File.

Example:

I have ICS4M1-01 4 Times in a File

It creates the new Database File (ICS4M1-01.mdb) & then suppose to add just 1 to Information but instead it will add 3 others to it, all with the same information.

How can I fix this?

The Code for all of it is below.

Option Strict On
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 * 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()
                    Application.DoEvents()
                    If bImportCancel = False Then
                        For Each DRow As DataRow In DS.Tables(0).Rows
                            If .lstCourses.Items.Contains(DRow("Classes(Import If #1)")) = False Then
                                frmImport.Show()
                                Connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Data\Courses\" & DRow("Classes(Import If #1)").ToString() & ".mdb")
                                CreateCourse(Connection.ConnectionString)
                                SQL = "INSERT INTO [Information] (CourseCode, RoomNumber) VALUES (?, ?);"
                                Command = New OleDbCommand(SQL, Connection)
                                Command.Connection.Open()
                                Command.Parameters.AddWithValue("CourseCode", DRow("Classes(Import If #1)"))
                                Command.Parameters.AddWithValue("RoomNumber", DRow("Home Room/Form"))
                                Command.ExecuteNonQuery()
                                Connection.Close()
                                frmImport.pbImport.Maximum = DS.Tables(0).Rows.Count
                                If frmImport.pbImport.Value > DS.Tables(0).Rows.Count + 5 Then
                                    frmImport.pbImport.Value = frmImport.pbImport.Maximum
                                    File.Delete(FileCopyName)
                                    frmImport.Close()
                                    MessageBox.Show("Import Complete")
                                Else
                                    frmImport.pbImport.Increment(5)
                                End If
                            End If
                        Next
                        MsgBox("Done")
                    End If
                End If
            End If
        End With
    End Sub
End Module

Question 2:
My Progress Bar doesn't really work. I want it to increment while reading from the list and each time it reads one line of the file I want it to + 1 to the Value, and then once it reaches the End I want it to say Done, like in MsgBox("Done") but frankly, it doesn't, how would I go about incrementing to the process of reading line by line ?

[Jenner]

1) Add "DISTINCT" to your SQL line, this will chop out repeated data and only pull unique rows:

SQL = "SELECT DISTINCT * FROM " & .ofdOpen.FileName

You're pulling all the columns though, so even if one of those columns have different data, you'll get repeat listings. Always pull only the data you need:

SQL = "SELECT DISTINCT CourseCode, RoomNumber FROM " & .ofdOpen.FileName

You should use a parameter for your tablename too:
SQL = "SELECT DISTINCT CourseCode, RoomNumber FROM ? "
...
Command.Parameters.AddWithValue("TableName", .ofdOpen.FileName)

2) Make a progress bar that has a Min = 0 and a Max = 100.
Next, get a count of the rows you're going to be adding:
total = DS.Tables(0).Rows.Count

Finally, make a counting variable and += 1 each loop.
Now, it's just simple math we all learned in 5th grade:

ProgressBar.Value = (count / total) * 100

[Wesley008]

Thanks Jenner You seem to be one with great knowledge also. Always helping me, and giving me tips on how to improve.

[Wesley008]

Total = DS.Tables(0).Rows.Count
                                Count += 1
                                If frmImport.pbImport.Value >= Total Then
                                    MessageBox.Show("Import Complete")
                                    frmImport.pbImport.Value = frmImport.pbImport.Maximum
                                    File.Delete(FileCopyName)
                                    frmImport.Close()
                                Else
                                    frmImport.pbImport.Value = (Count / Total) * 100
                                End If

For some reason, when it reaches the end, or near it, it just stops. Not showing messagfe box.

[Jenner]

You're using the wrong value in your comparison.

pbImport.Value will only ever be between 0 and 100. Why? Because you're the math you use standardizes it between 0 and 100; which is perfect for a progress bar.

What happens if you got 200 rows, so Total = 200?

pbImport.Value will NEVER be >= Total.

What you should be checking is: Count >= Total, because Count will be between 1 and Total. As soon as it hits Total, it'll break to your end-routine.