Reading Excel into a dataset in VB.net

  • Thread starter Thread starter nitromuse
  • Start date Start date
N

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.DataSet
Dim MyCommand As
System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As
System.Data.OleDb.OleDbConnection
Dim datagrid1 As DataGrid
MyConnection = New
System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Documents and
Settings\default\My Documents\Payroll.XLS; " & _
"Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter
( _
"select * from [Sheet4$]", MyConnection)
DS = New System.Data.DataSet
MyCommand.Fill(DS)
datagrid1.SetDataBinding(DS, "sheet4$")
MyConnection.Close()
End Sub

Your help is greatly appreciated, Nitromuse
 
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
Click to expand...

(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.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Documents and
Settings\default\My Documents\Payroll.XLS; " & _
"Extended Properties=Excel 8.0;")
dim MyCommand as New System.Data.OleDbCommand ( _
"select * from [Sheet4$]", MyConnection)
dim myAdapter as new data.oledbadapter(mycommand)
DS = New System.Data.DataSet
MyAdapter.Fill(DS)
datagrid1.SetDataBinding(DS, "sheet4$")
MyConnection.Close()
End Sub
Click to expand...
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
 
-----Original Message-----
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
Click to expand...

(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.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Documents and
Settings\default\My Documents\Payroll.XLS; " & _
"Extended Properties=Excel 8.0;")
dim MyCommand as New System.Data.OleDbCommand ( _
"select * from [Sheet4$]", MyConnection)
dim myAdapter as new data.oledbadapter(mycommand)
DS = New System.Data.DataSet
MyAdapter.Fill(DS)
datagrid1.SetDataBinding(DS, "sheet4$")
MyConnection.Close()
End Sub
Click to expand...
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
Click to expand...
still get what I've always gotten since I started ....

Imports System.Data.OleDb



Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

Dim DS As System.Data.DataSet
Dim MyCommand As
System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As New _
System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Documents and
Settings\default\My Documents\Payroll.XLS; " & "Extended
Properties=Excel 8.0;")
'MyCommand = New
System.Data.oledb.OleDbDataAdapter
MyCommand = New System.Data.OleDb.OleDbDataAdapter
("select * from [Sheet4$]", MyConnection)

DS = New System.Data.DataSet
MyCommand.Fill(DS)
DataGrid1.SetDataBinding(DS, "sheet4$")
MyConnection.Close()
End Sub

End Class


An unhandled exception of
type 'System.Data.OleDb.OleDbException' occurred in
system.data.dll

Any further thoughts ???
 
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 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
Click to expand...

(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.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Documents and
Settings\default\My Documents\Payroll.XLS; " & _
"Extended Properties=Excel 8.0;")
dim MyCommand as New System.Data.OleDbCommand ( _
"select * from [Sheet4$]", MyConnection)
dim myAdapter as new data.oledbadapter(mycommand)
DS = New System.Data.DataSet
MyAdapter.Fill(DS)
datagrid1.SetDataBinding(DS, "sheet4$")
MyConnection.Close()
End Sub
Click to expand...
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
Click to expand...
still get what I've always gotten since I started ....

Imports System.Data.OleDb



Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

Dim DS As System.Data.DataSet
Dim MyCommand As
System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As New _
System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Documents and
Settings\default\My Documents\Payroll.XLS; " & "Extended
Properties=Excel 8.0;")
'MyCommand = New
System.Data.oledb.OleDbDataAdapter
MyCommand = New System.Data.OleDb.OleDbDataAdapter
("select * from [Sheet4$]", MyConnection)

DS = New System.Data.DataSet
MyCommand.Fill(DS)
DataGrid1.SetDataBinding(DS, "sheet4$")
MyConnection.Close()
End Sub

End Class


An unhandled exception of
type 'System.Data.OleDb.OleDbException' occurred in
system.data.dll

Any further thoughts ???

.
Click to expand...
 
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
 
BB said:
3. For this and other interesting tidbits, check out Karl
Moore's excellent book The Ultimate VB.NET and ASP.NET
Code Book.
Click to expand...

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


Here's my Java version:

public static String getOrdinalString(long value)
{
String s = Long.toString(value);
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?
 
Back
Top