By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,694 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,694 IT Pros & Developers. It's quick & easy.

Importing excel data w/ oledb

P: n/a
I've been butting heads with this code, it always errors at
objAdapter1.Fill(objDataTable1)
Is oledb the best way to do this? How about ADO.NET?

Dim objDataTable1 As DataTable
Dim objConn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& strFilePath & ";Extended Properties=Excel 8.0")
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [Freeze
Data$]")
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd,
objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect

objAdapter1.Fill(objDataTable1)

objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()

Return objDataTable1

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hi,

Couple of things.

1) connection string. Should have hdr = yes; Use 8.0 for excel 97, 9.0 for
2000, 10.0 for 2002 (xp), 11.0 for 2003

OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& strFilePath & ";Extended Properties=Excel 8.0; HDR = YES;")

2) make sure you have the worksheet name correct

Ken
-------------------
"Claud Balls" <Li**@trainer.com> wrote in message
news:uj**************@TK2MSFTNGP09.phx.gbl...
I've been butting heads with this code, it always errors at
objAdapter1.Fill(objDataTable1)
Is oledb the best way to do this? How about ADO.NET?

Dim objDataTable1 As DataTable
Dim objConn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& strFilePath & ";Extended Properties=Excel 8.0")
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [Freeze
Data$]")
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd,
objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect

objAdapter1.Fill(objDataTable1)

objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()

Return objDataTable1

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #2

P: n/a
On Wed, 23 Mar 2005 16:57:04 -0800, Claud Balls <Li**@trainer.com> wrote:

I've been butting heads with this code, it always errors at
objAdapter1.Fill(objDataTable1)
Is oledb the best way to do this? How about ADO.NET?

Dim objDataTable1 As DataTable
Dim objConn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& strFilePath & ";Extended Properties=Excel 8.0")
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [Freeze
Data$]")
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd,
objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect

objAdapter1.Fill(objDataTable1)

objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()

Return objDataTable1
What error do you get when you butt heads?
Paul
~~~~
Microsoft MVP (Visual Basic)
Nov 21 '05 #3

P: n/a
I've added HDR=YES, and I tried using Excel 9.0, since I have 2000, but
the open connection errors. The fill is still not working with a value
cannot be null message, which would lead me to believe my sql query
returned no results, which doesn't make sense, as it is pretty straight
forward:

Dim objCmdSelect As OleDbCommand = _
New OleDbCommand("SELECT * FROM [Freeze Data$]", objConn)

Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataTable1)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #4

P: n/a
On Thu, 24 Mar 2005 10:46:38 -0800, Claud Balls <Li**@trainer.com> wrote:

I've added HDR=YES, and I tried using Excel 9.0, since I have 2000, but
the open connection errors. The fill is still not working with a value
cannot be null message, which would lead me to believe my sql query
returned no results, which doesn't make sense, as it is pretty straight
forward:

Dim objCmdSelect As OleDbCommand = _
New OleDbCommand("SELECT * FROM [Freeze Data$]", objConn)

Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataTable1)

Actually HDR=Yes is not required unless the first row of the Worksheet contains column names.

I still don't have enough info with respect to your error message. Could you post the exact text?
Paul
~~~~
Microsoft MVP (Visual Basic)
Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.