Data Grid Replaces the next File Data instead of copying after the first file

meho2021

New member
Joined
Mar 9, 2021
Messages
3
Programming Experience
10+
Dear All

I am having a code which copies excel files data from specific folder and paste them to Data Grid view.

The Data Grid Read all data of all files but the problem that it does not give the data of the first file and the second file respectively but it replaces the first file data with the second file data as a result, it always gives the last file data only instead of giving the first and the second file data together, I know there is something missing in my code which I do not know

Here is my code

VB.NET:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            DataGridView1.Rows.Clear()
            Dim Directory = FolderBrowserDialog1.SelectedPath
            Dim Files() As System.IO.FileInfo
            Dim DirInfo As New System.IO.DirectoryInfo(Directory)
            Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
            For Each File In Files
                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                Dim DtSet As DataSet
                MyConnection = New System.Data.OleDb.OleDbConnection
    ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File.FullName & "';  
    Extended Properties=Excel 4.0;")
               MyCommand = New OleDbDataAdapter("select SN, BarMark, Diameter, Length, Quantity 
    ,BBSName from [SCHEDULE$]", MyConnection)
                MyCommand.TableMappings.Add("Table", "Test")
                DtSet = New DataSet
                MyCommand.Fill(DtSet)
                DataGridView1.DataSource = DtSet.Tables(0)
                MyConnection.Close()
            Next
        End If
    End Sub

Appreciate your support since this is important to my work

Thanks, Regards
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,540
Location
Norway
Programming Experience
10+
Inside the loop (each file) you create a new DataSet, fill it, and assign it to DataSource. This means DataSet only contains one file, and only the last assignment to DataSource shows.
 

meho2021

New member
Joined
Mar 9, 2021
Messages
3
Programming Experience
10+
Inside the loop (each file) you create a new DataSet, fill it, and assign it to DataSource. This means DataSet only contains one file, and only the last assignment to DataSource shows.
Dear John

You are referring that the error is creating dataset inside the loop, if this is wrong what is the alternative ? Also, the role of For Each File in files is to read each file and paste its data to the dataset then with next it goes to the next file and do the same, what do you think?
 
Last edited:

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,540
Location
Norway
Programming Experience
10+
You are referring that the error is creating dataset inside the loop, if this is wrong what is the alternative ?
Create it outside the loop, so you can fill it with data from all files.
 
Top Bottom