Reading Excel into a dataset in VB.net

[nitromuse]

I haven't been able to configure a data adapter with the
wizard for Excel (Access is no problem) and when I try to
do it in code I still have problems, can anyone help,
here is some code I've been experimenting with.

Sub test()
Dim DS As System.Data.Dat aSet
Dim MyCommand As
System.Data.Ole Db.OleDbDataAda pter
Dim MyConnection As
System.Data.Ole Db.OleDbConnect ion
Dim datagrid1 As DataGrid
MyConnection = New
System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\Docum ents and
Settings\defaul t\My Documents\Payro ll.XLS; " & _
"Extended Properties=Exce l 8.0;")
MyCommand = New System.Data.Ole Db.OleDbDataAda pter
( _
"select * from [Sheet4$]", MyConnection)
DS = New System.Data.Dat aSet
MyCommand.Fill( DS)
datagrid1.SetDa taBinding(DS, "sheet4$")
MyConnection.Cl ose()
End Sub

Your help is greatly appreciated, Nitromuse

[Cor]

Hi Nitromuse,

There are more errors than one and I do not know if I get them in once so I
have simplified your code
a litte bit otherwise we could not see the corrections no more.
(It where not that much errors the most important one was that you did mix
up the command and the dataadapter)

Sub test()
Dim datagrid1 As DataGrid
(this datagrid1 you have to add to the form also if you do it in this way,
better is just to drag it to your form first)

dim MyConnection as New
System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\Docum ents and
Settings\defaul t\My Documents\Payro ll.XLS; " & _
"Extended Properties=Exce l 8.0;")
dim MyCommand as New System.Data.Ole DbCommand ( _
"select * from [Sheet4$]", MyConnection)
dim myAdapter as new data.oledbadapt er(mycommand) DS = New System.Data.Dat aSet
MyAdapter.Fill( DS)
datagrid1.SetDa taBinding(DS, "sheet4$")
MyConnection.Cl ose()
End Sub

I think we have the most now.

It was typed by hand, so if you have typos just change them and tell me if
you did succeed will you??

Cor

[nitromuse]

-----Original Message-----
Hi Nitromuse,

There are more errors than one and I do not know if I get them in once so Ihave simplified your code
a litte bit otherwise we could not see the corrections no more.(It where not that much errors the most important one was that you did mixup the command and the dataadapter)

Sub test()
Dim datagrid1 As DataGrid
(this datagrid1 you have to add to the form also if you

do it in this way,better is just to drag it to your form first)

dim MyConnection as New
System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\Docum ents and
Settings\defaul t\My Documents\Payro ll.XLS; " & _
"Extended Properties=Exce l 8.0;")
dim MyCommand as New System.Data.Ole DbCommand ( _
"select * from [Sheet4$]", MyConnection)
dim myAdapter as new data.oledbadapt er(mycommand)

DS = New System.Data.Dat aSet
MyAdapter.Fill( DS)
datagrid1.SetDa taBinding(DS, "sheet4$")
MyConnection.Cl ose()
End Sub
I think we have the most now.

It was typed by hand, so if you have typos just change

them and tell me ifyou did succeed will you??

Cor
.
Here's what I ended up with to make debug happy and I

still get what I've always gotten since I started ....

Imports System.Data.Ole Db

Private Sub Form1_Load(ByVa l sender As System.Object,
ByVal e As System.EventArg s) Handles MyBase.Load

Dim DS As System.Data.Dat aSet
Dim MyCommand As
System.Data.Ole Db.OleDbDataAda pter
Dim MyConnection As New _
System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\Docum ents and
Settings\defaul t\My Documents\Payro ll.XLS; " & "Extended
Properties=Exce l 8.0;")
'MyCommand = New
System.Data.ole db.OleDbDataAda pter
MyCommand = New System.Data.Ole Db.OleDbDataAda pter
("select * from [Sheet4$]", MyConnection)

DS = New System.Data.Dat aSet
MyCommand.Fill( DS)
DataGrid1.SetDa taBinding(DS, "sheet4$")
MyConnection.Cl ose()
End Sub

End Class
An unhandled exception of
type 'System.Data.Ol eDb.OleDbExcept ion' occurred in
system.data.dll

Any further thoughts ???

[BB]

Nitromuse,

Here are a couple thoughts.

1. I'm not familiar with the [Sheet4$] syntax, but I've
had no problems with code very similar to yours and using
a named range directly--e.g. "select * from myrange".

2. Use a try/catch and then messagebox.show on
ex.tostring, and you'll get a more descriptive error
message. Might tell you something about the syntax of
your range.

3. For this and other interesting tidbits, check out Karl
Moore's excellent book The Ultimate VB.NET and ASP.NET
Code Book. The case of pulling excel ranges into
datasets is explained in detail (p. 174). I've had no
problems using his code verbatim.

hth,

Bill Borg

-----Original Message-----

-----Original Message-----
Hi Nitromuse,

There are more errors than one and I do not know if Iget them in once so I

have simplified your code
a litte bit otherwise we could not see the corrections

no more.

(It where not that much errors the most important one

was that you did mix

up the command and the dataadapter)

Sub test()
Dim datagrid1 As DataGrid

(this datagrid1 you have to add to the form also if you

do it in this way,

better is just to drag it to your form first)

dim MyConnection as New
System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\Docum ents and
Settings\defaul t\My Documents\Payro ll.XLS; " & _
"Extended Properties=Exce l 8.0;")
dim MyCommand as New System.Data.Ole DbCommand ( _
"select * from [Sheet4$]", MyConnection)
dim myAdapter as new data.oledbadapt er(mycommand)

DS = New System.Data.Dat aSet
MyAdapter.Fill( DS)
datagrid1.SetDa taBinding(DS, "sheet4$")
MyConnection.Cl ose()
End Sub

I think we have the most now.

It was typed by hand, so if you have typos just change

them and tell me if

you did succeed will you??

Cor
.
Here's what I ended up with to make debug happy and I

still get what I've always gotten since I started ....

Imports System.Data.Ole Db

Private Sub Form1_Load(ByVa l sender As

System.Object,ByVal e As System.EventArg s) Handles MyBase.Load

Dim DS As System.Data.Dat aSet
Dim MyCommand As
System.Data.Ol eDb.OleDbDataAd apter
Dim MyConnection As New _
System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\Docum ents and
Settings\defau lt\My Documents\Payro ll.XLS; " & "Extended
Properties=Exc el 8.0;")
'MyCommand = New
System.Data.ol edb.OleDbDataAd apter
MyCommand = New System.Data.Ole Db.OleDbDataAda pter("select * from [Sheet4$]", MyConnection)

DS = New System.Data.Dat aSet
MyCommand.Fill( DS)
DataGrid1.SetDa taBinding(DS, "sheet4$")
MyConnection.Cl ose()
End Sub

End Class
An unhandled exception of
type 'System.Data.Ol eDb.OleDbExcept ion' occurred in
system.data.dl l

Any further thoughts ???

.

[Cor]

Hi Nitromuse,

Is there a reason you delete the oledbcommand all the time from your code?
You are giving the dataAdapter the name command, but that is not enough I
think.
(There is a methode to put it in the OldeDbAdapter, but I see no reason for
that now, that makes it only more difficult to debug).
See my example again, you can also check your connection string, I mis
something in it but am not sure if that is necessary, here is a link. And
look on the other message please for the code I supported you about the
OleDbCommand and OleDbAdapter.

http://www.connectionstrings.com/

Cor

System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\Docum ents and
Settings\defaul t\My Documents\Payro ll.XLS; " & "Extended
Properties=Exce l 8.0;")
'MyCommand = New
System.Data.ole db.OleDbDataAda pter
MyCommand = New System.Data.Ole Db.OleDbDataAda pter
("select * from [Sheet4$]", MyConnection)

DS = New System.Data.Dat aSet
MyCommand.Fill( DS)

[Mike]

"BB" <an*******@disc ussions.microso ft.com> wrote in message news:<07******* *************** ******@phx.gbl> ...

3. For this and other interesting tidbits, check out Karl
Moore's excellent book The Ultimate VB.NET and ASP.NET
Code Book.

Check out Karl's code at
http://www.developer.com/net/vb/article.php/3288031
Here's my Java version:

public static String getOrdinalStrin g(long value)
{
String s = Long.toString(v alue);
if (value < 0) value = -value;
value = value % 100;
if (value < 4 || value > 20)
{
switch ((int)(value % 10))
{
case 1: return s + "st";
case 2: return s + "nd";
case 3: return s + "rd";
}
}
return s + "th";
}

Am I missing something?

[scorpion53061]

Try this as well:

http://www.kjmsolutions.com/datasetarray.htm

"Mike" <ok*****@usa.ne t> wrote in message
news:e0******** *************** ***@posting.goo gle.com...

"BB" <an*******@disc ussions.microso ft.com> wrote in message news:<07******* *************** ******@phx.gbl> ...

3. For this and other interesting tidbits, check out Karl
Moore's excellent book The Ultimate VB.NET and ASP.NET
Code Book.

Check out Karl's code at
http://www.developer.com/net/vb/article.php/3288031
Here's my Java version:

public static String getOrdinalStrin g(long value)
{
String s = Long.toString(v alue);
if (value < 0) value = -value;
value = value % 100;
if (value < 4 || value > 20)
{
switch ((int)(value % 10))
{
case 1: return s + "st";
case 2: return s + "nd";
case 3: return s + "rd";
}
}
return s + "th";
}

Am I missing something?