DataReader in Excel (Better Post than the last)

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

Nov 21 '05 #1

Subscribe Post Reply

3937

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
"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

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

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

"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

by: NotGiven | last post by:

PHP

Fill TextBox with DataReader...

by: Islam Elkhayat | last post by:

In my C# Web Application I need to fill a textbox with DataReader and use a Next Button to view next value... I created the Datareader in the Page_Load but everytime it get result of the First...

C# / C Sharp

Returning Excel Spreadsheet as web page

by: KeithRaginF | last post by:

Hello I am trying to return the contents of an Excel file. However, I don't want to just redirect the request to the Excel file since it does not reside in a location where that is appropriate. ...

ASP.NET

grant IIS permission to create excel activex object

by: Steve Richter | last post by:

I am getting error in a vbscript: ActiveX component cant create object: Excel.Application. The vbscript code is: Dim objExcel Set objExcel = CreateObject("Excel.Application") I am pretty...

ASP.NET

DataReader in Excel (better post than the last)

by: brix_zx2 | last post by:

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

Visual Basic .NET

Converting DataReader to a DataTable Performance issue

by: Varangian | last post by:

Hi all, the question I want to ask if the conversion of a DataReader to a Table looping through the DataReader is better than using the Fill Method of the DataAdapter... I'm asking because...

C# / C Sharp

There is already an open DataReader associated with this Connection which must be closed first

by: fniles | last post by:

I am using VB.NET 2003, SQL 2000, and SqlDataReader. As I read data from tblA, I want to populate tblB. I use SQLDataReader for both tables. I do not use thread. When I ExecuteReader on tblB, I...

Visual Basic .NET

Make a DataReader internally preformat every record

by: Pittaman | last post by:

Hello, Sorry about the title, I had a hard time explaining this. I've done a little trick to solve a problem I was having with this third party control, and was wondering if anyone here could...

.NET Framework

Version Proofing Excel COM/.NET Wrapper

by: news.microsoft.com | last post by:

Not sure the best group to post to, so sorry for the cross post :(. There are several posts out there of how to automate excel from .net, i.e in the sample below, they are using Excel 9.0......

C# / C Sharp

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

DataReader in Excel (Better Post than the last)

Similar topics