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

DataReader in Excel (Better Post than the last)

P: n/a
I have an Excel Spreadsheet I want to import the records (5 fields) into an
access 2000 DB. Went on the net looking and found stuff for SQL (which I
don't know a thing about) and now I'm confused. (Using VB.NET 2k3) Any help
would be appreciated.

Here is the code I have.... It doesn't work cause it errors out after the
bold comment line.

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myDataReader As System.Data.OleDb.OleDbDataReader
Dim ExcelCommand As System.Data.OleDb.OleDbCommand

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Program Files\97CS\SelfInspect\CheckList\NewCheckList.XLS; "
& _
"Extended Properties=Excel 8.0;")

Dim myTable As System.Data.DataTable
'Object reference not set to an instance of an object.
myDataReader = ExcelCommand.ExecuteReader
Dim myRow As DataRow = myTable.NewRow

While myDataReader.Read
myRow.Item("dbmFilterList") = myDataReader.GetData(0)
myRow.Item("dbmCheckList") = myDataReader.GetData(1)
myRow.Item("dbmNumber") = myDataReader.GetData(2)
myRow.Item("dbmQuestion") = myDataReader.GetData(3)
myRow.Item("dbmReference") = myDataReader.GetData(4)
ODAAddItems.Update(DsAddItems)
EndWhile
Nov 21 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
See some comments with *** in front... you have a lot of problems, I just
pointed out the obvious ones I saw.

***Don't think you need the DataAdapter (since you are using DataReader)
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myDataReader As System.Data.OleDb.OleDbDataReader

*** Dim ExcelCommand As NEW System.Data.OleDb.OleDbCommand
Dim ExcelCommand As System.Data.OleDb.OleDbCommand

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Program Files\97CS\SelfInspect\CheckList\NewCheckList.XLS;
" & _
"Extended Properties=Excel 8.0;")

***Unused line
Dim myTable As System.Data.DataTable
'Object reference not set to an instance of an object.

***This is because you didn't instanticate the ExcelCommand Object
***You will also probably have to tell it way you want data you want the
command to get
***You will need to attach the connection object to the Command, and open
the Connection object
myDataReader = ExcelCommand.ExecuteReader

***This line will give the same error as above, since table isn't
instanciated
***Doesn't look like you use it anyways
Dim myRow As DataRow = myTable.NewRow

While myDataReader.Read
myRow.Item("dbmFilterList") = myDataReader.GetData(0)
myRow.Item("dbmCheckList") = myDataReader.GetData(1)
myRow.Item("dbmNumber") = myDataReader.GetData(2)
myRow.Item("dbmQuestion") = myDataReader.GetData(3)
myRow.Item("dbmReference") = myDataReader.GetData(4)
ODAAddItems.Update(DsAddItems)
EndWhile
"brix_zx2" <br*****@discussions.microsoft.com> wrote in message
news:96**********************************@microsof t.com...
I have an Excel Spreadsheet I want to import the records (5 fields) into an
access 2000 DB. Went on the net looking and found stuff for SQL (which I
don't know a thing about) and now I'm confused. (Using VB.NET 2k3) Any
help
would be appreciated.

Here is the code I have.... It doesn't work cause it errors out after the
bold comment line.

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myDataReader As System.Data.OleDb.OleDbDataReader
Dim ExcelCommand As System.Data.OleDb.OleDbCommand

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Program Files\97CS\SelfInspect\CheckList\NewCheckList.XLS;
"
& _
"Extended Properties=Excel 8.0;")

Dim myTable As System.Data.DataTable
'Object reference not set to an instance of an object.
myDataReader = ExcelCommand.ExecuteReader
Dim myRow As DataRow = myTable.NewRow

While myDataReader.Read
myRow.Item("dbmFilterList") = myDataReader.GetData(0)
myRow.Item("dbmCheckList") = myDataReader.GetData(1)
myRow.Item("dbmNumber") = myDataReader.GetData(2)
myRow.Item("dbmQuestion") = myDataReader.GetData(3)
myRow.Item("dbmReference") = myDataReader.GetData(4)
ODAAddItems.Update(DsAddItems)
EndWhile

Nov 21 '05 #2

P: n/a
Yea, I noticed half of those after I did this post but the other half worked
good, now I'm stuck with the following:

While myDataReader.Read
****Error: Column 'dbmFilterList' does not belong to table****
myRow.Item("dbmFilterList") = myDataReader.GetData(0)
myRow.Item("dbmCheckList") = myDataReader.GetData(1)
myRow.Item("dbmNumber") = myDataReader.GetData(2)
myRow.Item("dbmQuestion") = myDataReader.GetData(3)
myRow.Item("dbmReference") = myDataReader.GetData(4)
ODAAddItems.Update(DsAddItems)
EndWhile

Is this saying that it can't find that in my access database? which is
loaded into a seperate dataset.
"Chris" wrote:
See some comments with *** in front... you have a lot of problems, I just
pointed out the obvious ones I saw.

***Don't think you need the DataAdapter (since you are using DataReader)
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myDataReader As System.Data.OleDb.OleDbDataReader

*** Dim ExcelCommand As NEW System.Data.OleDb.OleDbCommand
Dim ExcelCommand As System.Data.OleDb.OleDbCommand

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Program Files\97CS\SelfInspect\CheckList\NewCheckList.XLS;
" & _
"Extended Properties=Excel 8.0;")

***Unused line
Dim myTable As System.Data.DataTable
'Object reference not set to an instance of an object.

***This is because you didn't instanticate the ExcelCommand Object
***You will also probably have to tell it way you want data you want the
command to get
***You will need to attach the connection object to the Command, and open
the Connection object
myDataReader = ExcelCommand.ExecuteReader

***This line will give the same error as above, since table isn't
instanciated
***Doesn't look like you use it anyways
Dim myRow As DataRow = myTable.NewRow

While myDataReader.Read
myRow.Item("dbmFilterList") = myDataReader.GetData(0)
myRow.Item("dbmCheckList") = myDataReader.GetData(1)
myRow.Item("dbmNumber") = myDataReader.GetData(2)
myRow.Item("dbmQuestion") = myDataReader.GetData(3)
myRow.Item("dbmReference") = myDataReader.GetData(4)
ODAAddItems.Update(DsAddItems)
EndWhile
"brix_zx2" <br*****@discussions.microsoft.com> wrote in message
news:96**********************************@microsof t.com...
I have an Excel Spreadsheet I want to import the records (5 fields) into an
access 2000 DB. Went on the net looking and found stuff for SQL (which I
don't know a thing about) and now I'm confused. (Using VB.NET 2k3) Any
help
would be appreciated.

Here is the code I have.... It doesn't work cause it errors out after the
bold comment line.

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myDataReader As System.Data.OleDb.OleDbDataReader
Dim ExcelCommand As System.Data.OleDb.OleDbCommand

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Program Files\97CS\SelfInspect\CheckList\NewCheckList.XLS;
"
& _
"Extended Properties=Excel 8.0;")

Dim myTable As System.Data.DataTable
'Object reference not set to an instance of an object.
myDataReader = ExcelCommand.ExecuteReader
Dim myRow As DataRow = myTable.NewRow

While myDataReader.Read
myRow.Item("dbmFilterList") = myDataReader.GetData(0)
myRow.Item("dbmCheckList") = myDataReader.GetData(1)
myRow.Item("dbmNumber") = myDataReader.GetData(2)
myRow.Item("dbmQuestion") = myDataReader.GetData(3)
myRow.Item("dbmReference") = myDataReader.GetData(4)
ODAAddItems.Update(DsAddItems)
EndWhile


Nov 21 '05 #3

P: n/a
Unless you changed your program since you last posted this is how you are
creating myRow. You see by this that myTable has no structure.

Dim myTable As New System.Data.DataTable
Dim myRow As DataRow = myTable.NewRow

Try this:

Dim myTable As New System.Data.DataTable
myTable.Columns.Add("dbmFilterList")
.....
myTable.Columns.Add("dbmReference")
Dim myRow As DataRow = myTable.NewRow

Chris

"brix_zx2" <br*****@discussions.microsoft.com> wrote in message
news:B4**********************************@microsof t.com...
Yea, I noticed half of those after I did this post but the other half
worked
good, now I'm stuck with the following:

While myDataReader.Read
****Error: Column 'dbmFilterList' does not belong to table****
myRow.Item("dbmFilterList") = myDataReader.GetData(0)
myRow.Item("dbmCheckList") = myDataReader.GetData(1)
myRow.Item("dbmNumber") = myDataReader.GetData(2)
myRow.Item("dbmQuestion") = myDataReader.GetData(3)
myRow.Item("dbmReference") = myDataReader.GetData(4)
ODAAddItems.Update(DsAddItems)
EndWhile

Is this saying that it can't find that in my access database? which is
loaded into a seperate dataset.
"Chris" wrote:
See some comments with *** in front... you have a lot of problems, I just
pointed out the obvious ones I saw.

***Don't think you need the DataAdapter (since you are using DataReader)
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myDataReader As System.Data.OleDb.OleDbDataReader

*** Dim ExcelCommand As NEW System.Data.OleDb.OleDbCommand
Dim ExcelCommand As System.Data.OleDb.OleDbCommand

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Program
Files\97CS\SelfInspect\CheckList\NewCheckList.XLS;
" & _
"Extended Properties=Excel 8.0;")

***Unused line
Dim myTable As System.Data.DataTable
'Object reference not set to an instance of an object.

***This is because you didn't instanticate the ExcelCommand Object
***You will also probably have to tell it way you want data you want the
command to get
***You will need to attach the connection object to the Command, and open
the Connection object
myDataReader = ExcelCommand.ExecuteReader

***This line will give the same error as above, since table isn't
instanciated
***Doesn't look like you use it anyways
Dim myRow As DataRow = myTable.NewRow

While myDataReader.Read
myRow.Item("dbmFilterList") = myDataReader.GetData(0)
myRow.Item("dbmCheckList") = myDataReader.GetData(1)
myRow.Item("dbmNumber") = myDataReader.GetData(2)
myRow.Item("dbmQuestion") = myDataReader.GetData(3)
myRow.Item("dbmReference") = myDataReader.GetData(4)
ODAAddItems.Update(DsAddItems)
EndWhile
"brix_zx2" <br*****@discussions.microsoft.com> wrote in message
news:96**********************************@microsof t.com...
>I have an Excel Spreadsheet I want to import the records (5 fields) into
>an
> access 2000 DB. Went on the net looking and found stuff for SQL (which
> I
> don't know a thing about) and now I'm confused. (Using VB.NET 2k3) Any
> help
> would be appreciated.
>
> Here is the code I have.... It doesn't work cause it errors out after
> the
> bold comment line.
>
> Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
> Dim MyConnection As System.Data.OleDb.OleDbConnection
> Dim myDataReader As System.Data.OleDb.OleDbDataReader
> Dim ExcelCommand As System.Data.OleDb.OleDbCommand
>
> MyConnection = New System.Data.OleDb.OleDbConnection( _
> "provider=Microsoft.Jet.OLEDB.4.0; " & _
> "data source=C:\Program
> Files\97CS\SelfInspect\CheckList\NewCheckList.XLS;
> "
> & _
> "Extended Properties=Excel 8.0;")
>
> Dim myTable As System.Data.DataTable
> 'Object reference not set to an instance of an object.
> myDataReader = ExcelCommand.ExecuteReader
> Dim myRow As DataRow = myTable.NewRow
>
> While myDataReader.Read
> myRow.Item("dbmFilterList") = myDataReader.GetData(0)
> myRow.Item("dbmCheckList") = myDataReader.GetData(1)
> myRow.Item("dbmNumber") = myDataReader.GetData(2)
> myRow.Item("dbmQuestion") = myDataReader.GetData(3)
> myRow.Item("dbmReference") = myDataReader.GetData(4)
> ODAAddItems.Update(DsAddItems)
> EndWhile
>
>


Nov 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.