11 Replies - 15047 Views - Last Post: 26 September 2010 - 12:45 PM Rate Topic: -----

#1 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 72
  • Joined: 06-August 09

EXCEL to DataGridView using OLEDB not working

Posted 23 September 2010 - 08:33 AM

I've got this code so far:

        Dim path As String = "C:\faktura.xls"

        Dim myDataset As New DataSet()
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & path & "; " & _
        "Extended Properties=Excel 8.0;"

        Dim myData As New OleDbDataAdapter("SELECT * FROM [Faktura$]", strConn)
        myData.TableMappings.Add("Table", "ExcelTest")
        myData.Fill(myDataset)

        DataGridView2.DataSource = myDataset.Tables(0).DefaultView


I've both double checked the path is right, and the sheet name is right.

I want the excel sheets into my datagridviewer, But the problem is that this dosn't work. It dosn't add any row's or coulums. Just stays grey.

I've tryed using serveal codes, did my reseach, but I just can't get this working.

Is This A Good Question/Topic? 0
  • +

Replies To: EXCEL to DataGridView using OLEDB not working

#2 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 175
  • View blog
  • Posts: 636
  • Joined: 03-February 10

Re: EXCEL to DataGridView using OLEDB not working

Posted 24 September 2010 - 06:21 AM

Are you sure that the connection string is correct? I have used this one in the passed
Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=""Excel 12.0;HDR=" + HDR + """"


I have also use this one for older Excel files.
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=""Excel 8.0;HDR=" + HDR + ";IMEX=1"""



HDR is an indicator of whether or not there is a header row for the data, and should be "Yes" or "No" depending.

Something I notice about your connection string, the Extended properties section is suppose to be enclosed in quotes as in

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/TestFile.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"

So you have to make sure to double up the quotes for the Extended Properties section.
Was This Post Helpful? 0
  • +
  • -

#3 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 72
  • Joined: 06-August 09

Re: EXCEL to DataGridView using OLEDB not working

Posted 24 September 2010 - 09:07 AM

My current excel worksheet is a 97.

Shoudn't the Extended properties be marked in a ' instead of a ":
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0';HDR=" + hdr + ";IMEX=1"


My current code now:
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim path As String = "C:\test.xls"
        Dim hdr As Boolean = False

        Dim myDataset As New DataSet()
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0';HDR=" + hdr + ";IMEX=1"


        Dim myData As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
        myData.TableMappings.Add("Table", "ExcelTest")
        myData.Fill(myDataset)

        DataGridView1.DataSource = myDataset.Tables(0).DefaultView

    End Sub


And still not inputting anything in the datagridviewer.
(I've made a new test sheet, called test.xls on the path above, and a new sheet name, so don't worry about that being wrong.

This post has been edited by Hypermx: 24 September 2010 - 09:13 AM

Was This Post Helpful? 0
  • +
  • -

#4 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 175
  • View blog
  • Posts: 636
  • Joined: 03-February 10

Re: EXCEL to DataGridView using OLEDB not working

Posted 24 September 2010 - 10:02 AM

The hdr value needs to be Yes or No

Also are you able to put a breakpoint on line where you set the DataSource of the DataGridView1 and view the DataSet object to see what it has in it for values?
Was This Post Helpful? 0
  • +
  • -

#5 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 72
  • Joined: 06-August 09

Re: EXCEL to DataGridView using OLEDB not working

Posted 24 September 2010 - 10:33 AM

I know how to put a breakpoint, but how would i view the object of the Dataset?
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: EXCEL to DataGridView using OLEDB not working

Posted 24 September 2010 - 11:36 AM

Try using this:
Dim hdr as String = "No" ' or "Yes" depending on whether you use headers.
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=" + hdr + ";IMEX=1"""

Was This Post Helpful? 0
  • +
  • -

#7 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 175
  • View blog
  • Posts: 636
  • Joined: 03-February 10

Re: EXCEL to DataGridView using OLEDB not working

Posted 24 September 2010 - 01:16 PM

View PostHypermx, on 24 September 2010 - 10:33 AM, said:

I know how to put a breakpoint, but how would i view the object of the Dataset?


after setting the breakpoint you right click on the myDataSet, do QuickView. After the QuickView window comes up it will show the identifier and then to the right will be the type with a magnifying glass. Click on that and it should expand into another window that lets you choose the tables in the data set.
Was This Post Helpful? 0
  • +
  • -

#8 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 72
  • Joined: 06-August 09

Re: EXCEL to DataGridView using OLEDB not working

Posted 25 September 2010 - 05:10 PM

View Postdemausdauth, on 24 September 2010 - 12:16 PM, said:

View PostHypermx, on 24 September 2010 - 10:33 AM, said:

I know how to put a breakpoint, but how would i view the object of the Dataset?


after setting the breakpoint you right click on the myDataSet, do QuickView. After the QuickView window comes up it will show the identifier and then to the right will be the type with a magnifying glass. Click on that and it should expand into another window that lets you choose the tables in the data set.


I made a breakpoint at
myData.Fill(myDataset)
checked the quickview, and clicked the magnifying glass. It says the dataset is empty.

I've also tryed CharlieMay's connection string, and checked with a breakpoint. It also says that the Dataset is empty.

Edit 1:

I've played around with the breakpoints, and noticed "myData" server's version was
{"Invalid operation. The connection is closed."}


I don't know if that the one responsible?

This post has been edited by Hypermx: 25 September 2010 - 05:18 PM

Was This Post Helpful? 0
  • +
  • -

#9 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: EXCEL to DataGridView using OLEDB not working

Posted 26 September 2010 - 05:48 AM

Hypermx, here is the exact code I used:
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim path As String = "C:\test.xls"
        Dim hdr As String = "No"

        Dim myDataset As New DataSet()
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=" + hdr + ";IMEX=1"""
        Dim myData As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
        myData.TableMappings.Add("Table", "ExcelTest")
        myData.Fill(myDataset)

        DataGridView1.DataSource = myDataset.Tables(0).DefaultView

    End Sub

I created a simple spreadsheet test.xls and populated a few fields on Sheet1

I can tell you, as a test, I changed the filename above to something that didn't exist and received no error, just a blank datagrid. So I would check to ensure that the file test.xls does in fact reside in the root directory of your c drive.

Edit:
It is definitely your connection string. I just used yours and got the blank grid. Here is what I found.
The apostrophe(') after 8.0 needs to be moved to the end of the string to enclose all the properties. Make that change and it should work. I suggest copying what is here to make sure you use this exact string.

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=" + hdr + ";IMEX=1'"

This post has been edited by CharlieMay: 26 September 2010 - 05:54 AM

Was This Post Helpful? 0
  • +
  • -

#10 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 72
  • Joined: 06-August 09

Re: EXCEL to DataGridView using OLEDB not working

Posted 26 September 2010 - 10:03 AM

View PostCharlieMay, on 26 September 2010 - 04:48 AM, said:

Hypermx, here is the exact code I used:
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim path As String = "C:\test.xls"
        Dim hdr As String = "No"

        Dim myDataset As New DataSet()
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=" + hdr + ";IMEX=1"""
        Dim myData As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
        myData.TableMappings.Add("Table", "ExcelTest")
        myData.Fill(myDataset)

        DataGridView1.DataSource = myDataset.Tables(0).DefaultView

    End Sub

I created a simple spreadsheet test.xls and populated a few fields on Sheet1

I can tell you, as a test, I changed the filename above to something that didn't exist and received no error, just a blank datagrid. So I would check to ensure that the file test.xls does in fact reside in the root directory of your c drive.

Edit:
It is definitely your connection string. I just used yours and got the blank grid. Here is what I found.
The apostrophe(') after 8.0 needs to be moved to the end of the string to enclose all the properties. Make that change and it should work. I suggest copying what is here to make sure you use this exact string.

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=" + hdr + ";IMEX=1'"



I've copyed the excact full code you posted, and replaced the connection string with the one in the edit.

And I'm it's still not showing anything.

I've checked again that that is is on the excact path :"C:\test.xls" ( Sheenshots uploaded. )

Attached image(s)

  • Attached Image
  • Attached Image

This post has been edited by Hypermx: 26 September 2010 - 10:04 AM

Was This Post Helpful? 0
  • +
  • -

#11 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: EXCEL to DataGridView using OLEDB not working

Posted 26 September 2010 - 10:44 AM

OK, I think I found your solution...

Is that version of Window7 64bit?

If so, you need to drop to x86 only as Jet4.0 won't work in 64bit. You're probably set at AnyCPU with will try to execute under 64bit in a 64bit OS.

In Visual Studio, Open your project properties. This is done by Double-Clicking on the "My Project" in your solution explorer.

Click on the Compile Tab

Click the Advanced Compile Options button

Change the Target CPU to x86
Was This Post Helpful? 1
  • +
  • -

#12 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 72
  • Joined: 06-August 09

Re: EXCEL to DataGridView using OLEDB not working

Posted 26 September 2010 - 12:45 PM

Okay, That solved it :)

Thanks!

This post has been edited by Hypermx: 27 September 2010 - 07:38 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1