Home All Groups Group Topic Archive Search About

DataReader in Excel (Better Post than the last)

microsoft.public.dotnet.languages.vb
Author
7 Apr 2005 2:41 PM
brix_zx2
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

Author
7 Apr 2005 3:51 PM
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


Show quoteHide quote
"brix_zx2" <brix***@discussions.microsoft.com> wrote in message
news:969587EE-1B4F-4604-B24C-956696555E32@microsoft.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
>
>
Author
7 Apr 2005 4:25 PM
brix_zx2
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.
Show quoteHide quote
"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" <brix***@discussions.microsoft.com> wrote in message
> news:969587EE-1B4F-4604-B24C-956696555E32@microsoft.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
> >
> >
>
>
>
Author
7 Apr 2005 5:00 PM
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

Show quoteHide quote
"brix_zx2" <brix***@discussions.microsoft.com> wrote in message
news:B475B63D-DD9A-4285-9E92-71DAAF335F1C@microsoft.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" <brix***@discussions.microsoft.com> wrote in message
>> news:969587EE-1B4F-4604-B24C-956696555E32@microsoft.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
>> >
>> >
>>
>>
>>



Post Other interesting topics
Is synclock needed in DLL?
getting row index of dataset
Howto: use mutliple languages C++ in VB.net
translation from c#
Compiler errors due to temp file inconsistencies ??
Remove items from listbox
Object reference not set to an instance of an object.
How to get assembly file info?
cast sortedlist value as hashtable
How to remove if blank in Detail in Crystal Report