Chrisetiquette Chrisetiquette - 2 months ago 16
Vb.net Question

Adding multiple datasources to a datagridview vb.net

I have a

DataGridView
that I am importing data into from multiple excel files. But every time I import the data it overwrites the previous data. How do I get it to add the next excel file to the end of the previous in the data grid view?

If DataGridView1.RowCount > 0 Then
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)

Dim tab As DataTable = DtSet.Tables(0)
DataGridView1.DataSource = tab

MyConnection.Close()
Else

'The below connection allows for the opening of .xls files and .xlsx. The one reamed out below doesnt open up .xls 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()
End If

Answer

You can simply fill a single DataTable multiple times and rows will be added to the DataTable this way. For example:

Try
    Dim table = New DataTable()
    Dim connection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=D:\excel1.xlsx;" & _
        "Extended Properties=Excel 12.0;"
    Using adapter As New OleDbDataAdapter("select * from [Sheet1$]", connection)
        adapter.Fill(table)
    End Using
    connection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=D:\excel2.xlsx;" & _
        "Extended Properties=Excel 12.0;"
    Using adapter As New OleDbDataAdapter("select * from [Sheet1$]", connection)
        adapter.Fill(table)
    End Using
    Me.DataGridView1.DataSource = table
Catch ex As Exception
    MessageBox.Show(ex.ToString())
End Try

Number of columns in different excel files can be different, but if there is columns with the same name, the data of those columns should be of the same type.