Results 1 to 11 of 11

Thread: How to Browse Files While Using OLEDB Connection in the same time

  1. #1

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    How to Browse Files While Using OLEDB Connection in the same time

    Dear All

    I am using a code to loop through excel files in a Directory using FolderBrowserDialog and it is working well but Slow, And I want to speed up the procedure by using OLEDB Connection , What I Discovered that the OLEDB is working well with one file having one path but when I tried to use it for looping for many files in a Directory it is failed

    Here is my Code

    Code:
       If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
                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 DtSet As DataSet
                    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                    MyConnection = New System.Data.OleDb.OleDbConnection
                ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Directory & "\" & "*.xls" & "' 
                ;Extended Properties=Excel 8.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
    Notice I tried to use Files or File But it Does not work either

    Appreciate your informing me what is missing here or how can I use OLEDB for group of files

    Thanks, Regards

    Moheb Labib

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,034

    Re: How to Browse Files While Using OLEDB Connection in the same time

    Quote Originally Posted by meho2020 View Post
    Dear All

    I am using a code to loop through excel files in a Directory using FolderBrowserDialog and it is working well but Slow, And I want to speed up the procedure by using OLEDB Connection , What I Discovered that the OLEDB is working well with one file having one path but when I tried to use it for looping for many files in a Directory it is failed
    It's not clear what you want.
    1)OLEDB for selecting a Excel File and then selecting a sheet?
    2)select all Excel Files with all sheet's and perform a Select Statment?

    no1. makes sence
    no2. will blow up in your face if you have 200> Excel Files in a Folder with say 3 Sheet's each
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to Browse Files While Using OLEDB Connection in the same time

    You can't use a wildcard in a connection string. That's crazy. You need to specify an actual file in your connection string. Look at this:
    Code:
    For Each File In Files
    Now look at this:
    Code:
    MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Directory & "\" & "*.xls" & "';Extended Properties=Excel 8.0;")
    How does that make sense? You've got the file path so use it:
    Code:
    MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File & "';Extended Properties=Excel 8.0;")
    There are numerous ways that the code could be further improved but the fundamental issue is that you're trying to connect to a file without specifying the file.

  4. #4

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: How to Browse Files While Using OLEDB Connection in the same time

    Dear Chris

    What I want is to select some columns in the first sheet and add them to the datagrid , then goes to the next file and select the same columns and add them after the last record in the datagrid and so on

  5. #5

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: How to Browse Files While Using OLEDB Connection in the same time

    Dear Jmcilhinney

    Thanks your reply , However when I use

    Code:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File & "';Extended Properties=Excel 8.0;"
    it says Operator & is not defined for types string and fileinfo

    Thanks

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to Browse Files While Using OLEDB Connection in the same time

    I didn't notice that you were using DirectoryInfo and FileInfo because there seems no reason to do so. Just call Directory.GetFiles and that will return a String array instead of a FileInfo array and the code I provided will work. If you must stick with FileInfo for some reason (I see no such reason) then use its FuleName property to get the file path.

  7. #7

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: How to Browse Files While Using OLEDB Connection in the same time

    Dear jmcilhinney

    Thanks I used File.FullName and it gave me the first file, now How can I call Directory.GetFiles

    Regards

    Moheb Labib

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to Browse Files While Using OLEDB Connection in the same time

    Quote Originally Posted by meho2020 View Post
    How can I call Directory.GetFiles
    What do you not understand about the information you found when you searched the web for yourself? Look first, ask questions later. We are not a substitute for your own effort.

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to Browse Files While Using OLEDB Connection in the same time

    Looking more closely at your code, how does it make sense to loop through multiple files and try to display them all in a single DataGridView anyway?

  10. #10

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: How to Browse Files While Using OLEDB Connection in the same time

    Dear jmcilhinney

    It is the core of the system which are developing is to loop through folder and get the specific columns data from the first file then the second until we finish the files and we use these data in another procedure, I may not use data grid view , I can put them in another excel sheet, the most important thing is not to miss any row of data

    Thanks, Regards
    Moheb Labib

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,034

    Re: How to Browse Files While Using OLEDB Connection in the same time

    Quote Originally Posted by meho2020 View Post
    Dear jmcilhinney

    It is the core of the system which are developing is to loop through folder and get the specific columns data from the first file then the second until we finish the files and we use these data in another procedure, I may not use data grid view , I can put them in another excel sheet, the most important thing is not to miss any row of data

    Thanks, Regards
    Moheb Labib
    what does the result file look like ?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width