DataReader in Excel (Better Post than the last)

G

Guest

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
 
C

Chris

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
 
G

Guest

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.
 
C

Chris

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
 
Top