<VB 2008> Use ADO.net 3.5 to connect with Excel 2003

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

Hi,
I try to connect to Excel 2003 xls using OLDBConnection object. And I
use this connection String:
Dim str As String
str = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data1.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""
cnn = New OleDbConnection(str)
cnn.Open()
da = New OleDbDataAdapter("Select * From Employee", cnn)
da.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)

It give me no exception but my DataGridView is empty.
And after I stop debug, it give me this in output windows

The thread 0x1798 has exited with code 0 (0x0).
The thread 0x1610 has exited with code 0 (0x0).
'ExcelDB.vshost.exe' (Managed): Loaded 'C:\Users\Thit\Documents\Visual
Studio 2008\Projects\ExcelDB\ExcelDB\bin\Debug\ExcelDB.exe', Symbols loaded.
'ExcelDB.vshost.exe' (Managed): Loaded
'C:\Windows\assembly\GAC_MSIL\System.Runtime.Remoting\2.0.0.0__b77a5c561934e089\System.Runtime.Remoting.dll',
Skipped loading symbols. Module is optimized and the debugger option
'Just My Code' is enabled.
A first chance exception of type 'System.ArgumentException' occurred in
System.Data.dll
The thread 0x13ac has exited with code 0 (0x0).
The thread 0x171c has exited with code 0 (0x0).
The program '[5520] ExcelDB.vshost.exe: Managed' has exited with code 0
(0x0).

I try to google and everywhere give me the same connection string.

Even I take out all statements after cnn.open()
I still get the sam message in my output window.

Please advice
TIA
Sam
 
sam said:
Hi,
I try to connect to Excel 2003 xls using OLDBConnection object. And
I
use this connection String:
Dim str As String
str = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data1.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""
cnn = New OleDbConnection(str)
cnn.Open()
da = New OleDbDataAdapter("Select * From Employee", cnn)
da.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)

It give me no exception but my DataGridView is empty.
And after I stop debug, it give me this in output windows

The thread 0x1798 has exited with code 0 (0x0).
The thread 0x1610 has exited with code 0 (0x0).
'ExcelDB.vshost.exe' (Managed): Loaded 'C:\Users\Thit\Documents\Visual
Studio 2008\Projects\ExcelDB\ExcelDB\bin\Debug\ExcelDB.exe', Symbols
loaded. 'ExcelDB.vshost.exe' (Managed): Loaded
'C:\Windows\assembly\GAC_MSIL\System.Runtime.Remoting\2.0.0.0 __b77a5c56
1934e089\System.Runtime.Remoting.dll', Skipped loading symbols. Module
is optimized and the debugger option 'Just My Code' is enabled.
A first chance exception of type 'System.ArgumentException' occurred
in System.Data.dll
The thread 0x13ac has exited with code 0 (0x0).
The thread 0x171c has exited with code 0 (0x0).
The program '[5520] ExcelDB.vshost.exe: Managed' has exited with code
0 (0x0).

I try to google and everywhere give me the same connection string.

Even I take out all statements after cnn.open()
I still get the sam message in my output window.
Click to expand...

What
OS?
Version of .NET?

These could be important to the answer. Regardless, try the rest of the
post and see if it helps.


The OLEDB providers for JET shipped separately from the standard MDAC.
In some versions of .NET, if not all, you have to install JET
separately. I cannot find the download page, but there is a download
page for the JET providers somewhere. This KB
(http://support.microsoft.com/default.aspx/kb/239114) has a download,
but it is not all OSes.

The JET providers are supposed to be pushed out through windows update
now, so check if you have ignored that download and install (may be in
non-mandatory, so custom install may be needed).

Note that the Office 2007 driver is different:
http://snurl.com/qdtbx

NOTE that there was no 64 bit driver for JET for some time. If you ran
on a 64-bit machine, you ended up having to downgrade to 32-bit for your
web apps to have things running. I would assume this is updated by now,
but I don't run Access on my websites.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Gregory said:
sam said:
Hi,
I try to connect to Excel 2003 xls using OLDBConnection object. And
I
use this connection String:
Dim str As String
str = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data1.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""
cnn = New OleDbConnection(str)
cnn.Open()
da = New OleDbDataAdapter("Select * From Employee", cnn)
da.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)

It give me no exception but my DataGridView is empty.
And after I stop debug, it give me this in output windows

The thread 0x1798 has exited with code 0 (0x0).
The thread 0x1610 has exited with code 0 (0x0).
'ExcelDB.vshost.exe' (Managed): Loaded 'C:\Users\Thit\Documents\Visual
Studio 2008\Projects\ExcelDB\ExcelDB\bin\Debug\ExcelDB.exe', Symbols
loaded. 'ExcelDB.vshost.exe' (Managed): Loaded
'C:\Windows\assembly\GAC_MSIL\System.Runtime.Remoting\2.0.0.0 __b77a5c56
1934e089\System.Runtime.Remoting.dll', Skipped loading symbols. Module
is optimized and the debugger option 'Just My Code' is enabled.
A first chance exception of type 'System.ArgumentException' occurred
in System.Data.dll
The thread 0x13ac has exited with code 0 (0x0).
The thread 0x171c has exited with code 0 (0x0).
The program '[5520] ExcelDB.vshost.exe: Managed' has exited with code
0 (0x0).

I try to google and everywhere give me the same connection string.

Even I take out all statements after cnn.open()
I still get the sam message in my output window.
Click to expand...

What
OS?
Version of .NET?

These could be important to the answer. Regardless, try the rest of the
post and see if it helps.


The OLEDB providers for JET shipped separately from the standard MDAC.
In some versions of .NET, if not all, you have to install JET
separately. I cannot find the download page, but there is a download
page for the JET providers somewhere. This KB
(http://support.microsoft.com/default.aspx/kb/239114) has a download,
but it is not all OSes.

The JET providers are supposed to be pushed out through windows update
now, so check if you have ignored that download and install (may be in
non-mandatory, so custom install may be needed).

Note that the Office 2007 driver is different:
http://snurl.com/qdtbx

NOTE that there was no 64 bit driver for JET for some time. If you ran
on a 64-bit machine, you ended up having to downgrade to 32-bit for your
web apps to have things running. I would assume this is updated by now,
but I don't run Access on my websites.

Peace and Grace,
Click to expand...
Thank you very much for your response.
I use vb .net 2008 sp1 and Excel 2003 run on vista pro 64 bit. I just
try to learn how to use ado.net to connect to other database & data
storage beside MS Access. I have no problem connect to MS Access 2003.
I some what recall the need to install MDAC for VB6., I though we get
over that already. I will try what you suggest, Thank you again.
Sam
 
sam said:
Gregory said:
sam said:
Hi,
I try to connect to Excel 2003 xls using OLDBConnection object. And
I use this connection String:
Dim str As String
str = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data1.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""
cnn = New OleDbConnection(str)
cnn.Open()
da = New OleDbDataAdapter("Select * From Employee", cnn)
da.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)

It give me no exception but my DataGridView is empty.
And after I stop debug, it give me this in output windows

The thread 0x1798 has exited with code 0 (0x0).
The thread 0x1610 has exited with code 0 (0x0).
'ExcelDB.vshost.exe' (Managed): Loaded 'C:\Users\Thit\Documents\Visual
Studio 2008\Projects\ExcelDB\ExcelDB\bin\Debug\ExcelDB.exe', Symbols
loaded. 'ExcelDB.vshost.exe' (Managed): Loaded
'C:\Windows\assembly\GAC_MSIL\System.Runtime.Remoting\2.0.0.0 __b77a5c56
1934e089\System.Runtime.Remoting.dll', Skipped loading symbols. Module
is optimized and the debugger option 'Just My Code' is enabled.
A first chance exception of type 'System.ArgumentException' occurred
in System.Data.dll
The thread 0x13ac has exited with code 0 (0x0).
The thread 0x171c has exited with code 0 (0x0).
The program '[5520] ExcelDB.vshost.exe: Managed' has exited with code
0 (0x0).

I try to google and everywhere give me the same connection string.

Even I take out all statements after cnn.open()
I still get the sam message in my output window.
Click to expand...

What
OS?
Version of .NET?

These could be important to the answer. Regardless, try the rest of the
post and see if it helps.

The OLEDB providers for JET shipped separately from the standard MDAC.
In some versions of .NET, if not all, you have to install JET
separately. I cannot find the download page, but there is a download
page for the JET providers somewhere. This KB
(http://support.microsoft.com/default.aspx/kb/239114) has a download,
but it is not all OSes.
The JET providers are supposed to be pushed out through windows update
now, so check if you have ignored that download and install (may be in
non-mandatory, so custom install may be needed).
Note that the Office 2007 driver is different:
http://snurl.com/qdtbx

NOTE that there was no 64 bit driver for JET for some time. If you ran
on a 64-bit machine, you ended up having to downgrade to 32-bit for your
web apps to have things running. I would assume this is updated by now,
but I don't run Access on my websites.
Peace and Grace,
Click to expand...
Thank you very much for your response.
I use vb .net 2008 sp1 and Excel 2003 run on vista pro 64 bit. I just
try to learn how to use ado.net to connect to other database & data
storage beside MS Access. I have no problem connect to MS Access 2003.
I some what recall the need to install MDAC for VB6., I though we get
over that already. I will try what you suggest, Thank you again.
Sam
Click to expand...
I got it now. I just understand what do you mean by down grade to x86.
Thank you.
sam
 
sam said:
I got it now. I just understand what do you mean by down grade to x86.
Thank you.
Click to expand...

I run a 64-bit machine and I still sometimes feel like the "red headed
stepchild" (an English colloquialism for outsider in the corner). It is
getting better, but there are still many hurdles (programs that install in
"Program Files (x86)" for example).

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
I don't use Jet databases with web apps either - it just isn't
designed for that architecture...
Click to expand...

You will get no fight from me for this statement. I have been editing
Access lately and it is not much use for Enterprise level windows apps
either.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Back
Top