How do I import data from an infinite amount of data files into a DataGridView with vb.net?

Question

I need to import data into a DataGridView but I need the data to stack after the last import. So, if my first file is 100 records, and my second file is 200 records I should now have 300 records. I need to be able to press an import button browse to a file and import it and it adds to the end of the data set. The amount of files and records will vary greatly. here is my code so far, but right now it just overwrites the data already in the DataGridView each time I import new data. The excel file is just a test file, eventually ill add a folder browser dialog where I'll select "x" amount of files.

MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Work\4pc_test1.xlsx;Extended Properties=Excel 12.0;")
'MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fd.FileName & "';Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
MyCommand.TableMappings.Add("Table", "Net-informations.com")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConnection.Close()

@Reza Ahaei I hope this is a better description of what I need to do:) — Chrisetiquette, Sep 30 '16 at 15:45
As I said in the [comment](http://stackoverflow.com/questions/39758766/adding-multiple-datasources-to-a-datagridview-vb-net#comment66859353_39759762) under your previous quetsion: *Pay attention, the table should be in a scope which keep previous added rows. For example if you create a method which import rows from excel to a `DataTable`, you should create `DataTable` at form level and pass it to that method and you should not create it in the method.* — Reza Aghaei, Sep 30 '16 at 19:29
The answer which is posted by @Plutonix covers what you need. By default [DataTable.Load](http://stackoverflow.com/q/39676151/3110834) adds the result to the `DataTable`. — Reza Aghaei, Sep 30 '16 at 19:31

score 3 · Answer 1 · edited May 23 '17 at 12:17

If you want the data to append, you should not create a new DataSet and Datatable each time (in fact you dont need a DataSet at all for one table). Create and use the same one to hold the data.

Form/class level variables:

Private dtXLS As DataTable
' once connection string to rule them all
Private AceXLSConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties=Excel 12.0;"

Then a control loop:

Private Sub btnLoad_Click(etc etc
    ' create table ONCE
    dtXLS = New DataTable
    ' files to laod:
    Dim files As String() = {"C:\Temp\Sample1.xls", "C:\Temp\Sample2.xls"}

    For Each fname In files
        AppendXLS(fname)
    Next
    dgv1.DataSource = dtXLS
End Sub

Private Sub AppendXLS(filname As String)
    ' create conn str for this file
    Dim connx = String.Format(AceXLSConnStr, filname)

    ' always dispose of connections and commands
    Using dbCon As New OleDbConnection(connx)
        Using cmd As New OleDbCommand("select * from [Sheet1$]", dbCon)
            dbCon.Open()
            dtXLS.Load(cmd.ExecuteReader())
        End Using
    End Using
End Sub

Declared (Private) at the form level, the connection string mask and DataTable are available to both methods. The first creates the DataTable instance (once only), and data continuously added to it in the another.

Also, especially if you have an "infinite" number of files to load, you will want to be sure to close and dispose of the DBCommand and connection. A Using block declares and creates an object for use, then disposes of it for you at the end.

Results (file 1 has items 1-5, file 2 had items 10-15):

Since you seem at least a little confused by the idea of Scope, in a nutshell:

Dim/Private | Friend | Public declare a variable, thats all.
New creates an instance. Dim and New do not always have to go together. In fact, when you find yourself typing Dim foo As New... stop and think if that is what you really want, because....
Where a variable is declared determines its Scope - where it can be accessed from. A variable declared in a method exists only there.

There are other level of Scope such as block level. See Reference variables and objects elsewhere in a form for a bit more detail.

How do I import data from an infinite amount of data files into a DataGridView with vb.net?

1 Answers1