Object reference not set to an instance.....Help

[themaj]

I'm trying to read a DBF file into a DataSet and then write a selected number of columns to a MDB.
I've been successful reading the DBF into the Dataset with this code;

Public Sub sReadDbf(ByVal dbffile As String)

        mydbfConn = New System.Data.OleDb.OleDbConnection

        mydbfConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            sAlohaPath & "\" & dbfFileFolder & "\ ;Extended Properties=dBASE IV;User ID=Admin;Password=;"

        mydbfConn.Open()

        Dim ds As New DataSet

        Try

            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM GndItem" & dbfFileName, mydbfConn)

            da.Fill(ds, "Data")

            Me.dgv1.DataSource = ds.Tables("Data")

            mydbfConn.Close()

        Catch ex As Exception

            Using writer As StreamWriter = New StreamWriter(sAlohaPath & "\" & dbfFileFolder & "\" & sLog, True)
                writer.WriteLine(Now & ": " & dbffile & " >---ERROR---< " & ex.Message)
            End Using

        End Try


    End Sub

Now I'm trying write the data to the MDB and I can't get past the initial part of this code. It crashes at the part where I'm just testing to see the number of rows in my DataSet table(0); MsgBox(ds.Tables("Data").Rows.Count) with error: NullReferenceException was unhandled & Object reference not set to an instance of an object.

Public Sub sWriteAccess()

        myAccConn = New OleDb.OleDbConnection
        Dim cmd As New OleDb.OleDbCommand

        myAccConn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\Gnditem.mdb"

        If Not myAccConn.State = ConnectionState.Open Then
            'open connection
            myAccConn.Open()

        End If

        MsgBox(ds.Tables("Data").Rows.Count) '''CRASH HERE!!!!

        Dim dtRows As Integer = Me.ds.Tables(0).Rows.Count

        Try
            For iX = 0 To dtRows - 1

                cmd.CommandText = "INSERT INTO GrindItems ( EMPLOYEE, CHECK, ITEM, PARENT, CATEGORY ) " & _
                                        " VALUES(" & _
                                        ds.Tables(0).Rows(iX).Item(1) & "," & _
                                        ds.Tables(0).Rows(iX).Item(2) & "," & _
                                        ds.Tables(0).Rows(iX).Item(3) & "," & _
                                        ds.Tables(0).Rows(iX).Item(4) & "," & _
                                        ds.Tables(0).Rows(iX).Item(5) & ")"

                cmd.ExecuteNonQuery()

            Next

        Catch ex As Exception

        End Try

        myAccConn.Close()

    End Sub

I can't figure out why the DataSet seems to be not available in the 2nd Sub, or where/how to set the instance.

I've got no idea whether I've got the rest of this INSERT query will work.

Appreciate some help & guidance here.

[Reverend Jim]

You declare and create ds in Sub sReadDbf but you didn't declare or create it in Sub sWriteAccess

[themaj]

OK Jim, help me out here. You have no idea how much I appreciate your input here.

How do I declare the 'ds' in a separate 'Sub sWriteAccess'and maintain its existence without destroying the 'ds' in a previous Sub.

You know me; I'm all ears here and want to understand.

[themaj]

I thought that the 'ds' was available when I declared it as a Public declaration at the top of my code. I though that would make it available to all my Subs.

[Reverend Jim]

I thought that the 'ds' was available when I declared it as a Public declaration at the top of my code

That may be true, however I can't see the top of your code that includes public declarations. All you posted were the two subs, one of which instantiates its own copy of ds and one that doesn't. And on the Public declaration did you just declare ds or declare it and create it as New?

[themaj]

Always good to hear from you Jim, here's the Declarations

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Threading

Public Class Form1
    Public dbfFileFolder As String
    Public dbfFileName As String
    Public dbfFileNameShort As String
    Public sAlohaPath As String = "C:\Aloha"
    Public GndSale As String = "GNDSALE.dbf"
    Public sLog As String = "GNDITEM.Log"
    Public sOutputCSV As String
    Public dbfPath As String
    Public iStore As Integer
    Public sStore As String
    Public mydbfConn As System.Data.OleDb.OleDbConnection
    Public myAccConn As System.Data.OleDb.OleDbConnection
    Public myAdapter As System.Data.OleDb.OleDbDataAdapter
    Public bsData As New BindingSource
    Public dt As DataTable
    Public ds As DataSet
    Public iFileExist As Integer
    Public bFileRead As Boolean
    Public GrindItems As DataSet = New DataSet()
    Public iTimer As Integer = 500
    Public iFileRead As Integer
    Public sFileFolder As String
    Public csvFileName As String
    Public sFullFilePath As String

Looking forward to hearing from you on this and any comment or suggest to my madness

[Pgmer]

Try Declare ds as new in public declaration or instantiate again in the sub where ur using the DS. If you instantiate it will be a new dataset you will loose the data in it.

[Reverend Jim]

I've said this before - I'm not well versed in DataTables, DataAdapters, DataSets, etc but I think I can take a few educated guesses. You declare ds at the class level and you fill it in sReadDbf but in that sub you do

Dim ds As New DataSet

so the actual "ds" that you are using in sReadDbf is a copy local to that sub. try replacing

Dim ds As New DataSet

with

ds = New DataSet

That way you will be using the same "ds" in both subs and when you reference it in sWriteAccess it will retain all the properties from when you filled it in sReadDbf.

[themaj]

Got it!!! Thanks to both Jim & Pgmer

I declared it as "Public ds As New DataSet" in the General Declarations and then removed all references in the various Subs.

So I'm past that wall but still having problems with the INSERT command in the sWriteAccess Sub above.

It doesn't blow up but doesn't seem to do anything :(

Ideas?

[themaj]

Am I missing an Update command or something at the bottom of sWriteAccess after:

Dim dtRows As Integer = Me.ds.Tables(0).Rows.Count

        Try
            If Not myAccConn.State = ConnectionState.Open Then
                'open connection
                myAccConn.Open()

            End If

            For iX = 0 To dtRows - 1

                cmd.CommandText = "INSERT INTO [GrindItems] ( EMPLOYEE, CHECK, ITEM, PARENT, CATEGORY ) " & _
                                        " VALUES(" & _
                                        ds.Tables(0).Rows(iX).Item(1) & "," & _
                                        ds.Tables(0).Rows(iX).Item(2) & "," & _
                                        ds.Tables(0).Rows(iX).Item(3) & "," & _
                                        ds.Tables(0).Rows(iX).Item(4) & "," & _
                                        ds.Tables(0).Rows(iX).Item(5) & ")"
                MsgBox(cmd.CommandText)
                cmd.ExecuteNonQuery()

            Next

        Catch ex As Exception
            myAccConn.Close()
        End Try

My MsgBox(cmd.CommandText) has the correct data for the first row but it doesn't save or even complete the For Next as far as I can figure.

[Begginnerdev]

Also, what might be a problem - When inserting strings, you will have to wrap the value in ''

Example:

cmd.CommandText = "INSERT INTO [GringItems] (EMPLOYEE,CHECK,ITEM,PARENT,CATEGORY)" & _
                        " VALUES ('" & _
                        ds.Tables(0).Rows(iX).Item(1) & "'," & _
                       'ect...

You will only need to wrap the values that are represented as string

[Pgmer]

Before starting the loop Can you just check DS is having tables and Rows?
And I am not sure about this line "For iX = 0 To dtRows - 1" your looping through dtrows and accessing values from dataset. Will dtRows have same number of rows as table of DS?

[themaj]

@ Begginnerdev....all data being inserted now are integer values; I'll add 'date' values once I get this to work.

@ Pgmer.....yeah, I did check that and it comes back with a little over 2,000 rows in the DS. When I hit Codeline 19 above it reports the correct values for the 1st row.

I'll try just using 1 row to see what happens but eventually I'll need to be insert several thousands rows.

I'm willing to try either a cmd.commandtext or data adapter if that makes any difference.

Thanks

[Begginnerdev]

Have you stepped through the code to make sure it is executing the query?

You can also try to create a new instance of cmd every command (what I use)

   cmd = New System.Data.OleDb.OleDbCommand("INSERT INTO [GrindItems] ( EMPLOYEE, CHECK, ITEM, PARENT, CATEGORY ) " & _
                                                " VALUES(" & _
                                                ds.Tables(0).Rows(iX).Item(1) & "," & _
                                                ds.Tables(0).Rows(iX).Item(2) & "," & _
                                                ds.Tables(0).Rows(iX).Item(3) & "," & _
                                                ds.Tables(0).Rows(iX).Item(4) & "," & _
                                                ds.Tables(0).Rows(iX).Item(5) & ")",yourConnection)

[themaj]

Guys, I can't figure this out and I've got a digital pistol to the side of my head now !!!!
I created a NEW Access table with only 3 fields to avoid any potential problems.

It correctly reads in all the DBF data to the dataset; it's row values are correctly reporting in the 1st MSGBOX so they're obviously available to this Sub.

I've pared it down to Insert just 1 (one) bloody row of 3 fields.

I get no error messages but my DB table [GRINDITEMS] is still empty.

What is wrong with this Sub? :( Did i mention I'm almost out of Xanax?

Please help!!!

Public Sub sWriteAccess()

        MsgBox(ds.Tables(0).Rows.Count & " Rows") 'Correctly reports number of rows in Dataset table

        myAccConn = New OleDb.OleDbConnection
        myAccConn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=C:\Aloha\Gnditem.mdb"

        Dim dtRows As Integer = Me.ds.Tables(0).Rows.Count

        Try

            Dim myAdapter As New OleDb.OleDbDataAdapter("INSERT INTO [GRINDITEMS] ( EMPLOYEE, CHECK, ITEM ) " & _
                                    " VALUES(" & _
                                    ds.Tables(0).Rows(2).Item(1) & "," & _
                                    ds.Tables(0).Rows(2).Item(2) & "," & _
                                    ds.Tables(0).Rows(2).Item(3) & ")", myAccConn)

            Dim CmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(myAdapter)

            myAccConn.Open()

            myAdapter.Update(ds.Tables(0))

        Catch ex As Exception
            MsgBox(ex.ToString) ' Get NO error messages

        End Try

        myAccConn.Close()

    End Sub

[themaj]

I even tried to stuff values in with this line

Dim myAdapter As New OleDb.OleDbDataAdapter("INSERT INTO [GRINDITEMS] ( EMPLOYEE, CHECK, ITEM ) " & _
                                    " VALUES(20,30,55)", myAccConn)