DataReader To DataSet

brix_zx2

Member
Joined
Apr 6, 2005
Messages
12
Location
Altus AFB
Programming Experience
1-3
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)
End While
 
To make things easier, you should use import statements at the very top of your code.

Imports System.Data
Imports System.Data.OleDb

Now yu will not need to use the fully qualified name. Just use something like this:

Dim myConn as Oledb.connection 'much easier

Next you need to instantiate it.

myConn = New OledbConnection

You can combine this into one declaration statement like:

Dim myConn as New Oledbconnection

You have to instatiate a variable before you can use it.

Hope that helps.

Chester
 
brix_zx2 said:
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
first things first.
use imports for easy access of the objects.
Imports System.Data
Imports System.Data.OleDb
second
instantiate the class
Dim MyCommand As New OleDbDataAdapter()
Dim MyConnection As New OleDbConnection()
Dim myDataReader As New OleDbDataReader()
Dim ExcelCommand As New OleDbCommand()

now tell me..what are the errors. :)
 
Replace

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

Dim myTable As New System.Data.DataTable
'Object reference not set to an instance of an object.
 
Back
Top