Member Avatar for 404notfound

In my access database, I have a table for the master list of items discussed in a training meeting. If a certain item was discussed in a meeting, then the value in the database is 1, if it was not discussed, then the item is 0.
I'm trying to get these values into checkboxes on my form, checked if the value is 1 and unchecked if 0.
However when I run it, I get a "NullReferenceException was unhandled. Object reference not set to an instance of an object"
The code for that subroutine is:

Private Sub NavigateTrainingItems()

        chkDrugAlcoholPolicy.Checked = False
        chkElectricalSafety.Checked = False
        chkEmergencyResponse.Checked = False
        chkFirstAid.Checked = False
        chkForkliftTraining.Checked = False
        chkGlobalGap.Checked = False
        chkHeatStress.Checked = False
        chkLadderSafety.Checked = False
        chkLiftingItems.Checked = False
        chkOperatingEquipInspection.Checked = False
        chkPesticide.Checked = False
        chkPPE.Checked = False
        chkReportInjuryIllnessDeath.Checked = False
        chkSafetyPolicyProcedure.Checked = False
        chkSanitation.Checked = False
        chkSexualHarassment.Checked = False
        chkSlipsTripsFalls.Checked = False
        chkTransportation.Checked = False
        chkTreeWorkPruningOperations.Checked = False
        chkUseTools.Checked = False

        Dim dsTrainingItems As New DataSet
        Dim dtTrainingItems As New DataTable

        dsTrainingItems.Tables.Add(dtTrainingItems)
        Dim daTrainingItems As New OleDb.OleDbDataAdapter("SELECT * FROM tblTrainingItems WHERE ID = " & txtTrainingID.Text, con)
        daTrainingItems.Fill(dtTrainingItems)
        Try

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("Sanitation") = 1 Then
                chkSanitation.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("Pesticides") = 1 Then
                chkPesticide.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("UseOfTools") = 1 Then
                chkUseTools.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("LiftingItems") = 1 Then
                chkLiftingItems.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("ElectricalSafety") = 1 Then
                chkElectricalSafety.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("SafetyPolicyProcedure") = 1 Then
                chkSafetyPolicyProcedure.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("ReportingInjuriesIllnessDeath") = 1 Then
                chkReportInjuryIllnessDeath.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("TreeWorkPruningOperations") = 1 Then
                chkTreeWorkPruningOperations.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("HeatStress") = 1 Then
                chkHeatStress.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("OperatingEquipInspection") = 1 Then
                chkOperatingEquipInspection.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("FirstAid") = 1 Then
                chkFirstAid.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("Transportation") = 1 Then
                chkTransportation.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("SexualHarrassment") = 1 Then
                chkSexualHarassment.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("GlobalGap") = 1 Then
                chkGlobalGap.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("ForkliftTraining") = 1 Then
                chkForkliftTraining.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("EmergencyResponse") = 1 Then
                chkEmergencyResponse.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("SlipsTripsFalls") = 1 Then
                chkSlipsTripsFalls.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("LadderSafety") = 1 Then
                chkLadderSafety.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("PPE") = 1 Then
                chkPPE.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("DrugAlcoholPolicy") = 1 Then
                chkDrugAlcoholPolicy.Checked = True
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

So to troubleshoot, I add a DataGridView to the form, comment out all the chkBoxes and insert: 

DataGridView1.DataSource = dtTrainingItems.DefaultView

and it loads the querried data just fine.
Sooooo I'm confused. And sober. Anyone know any good rum?

Edited by 404notfound
  • 4 Contributors
  • 7 Replies
  • 462 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by tinstaafl
Member Avatar for rproffitt

Just in case I'm missing something, that tag "vw.net", is that a typo or a new language? Anyhow, while you are in Visual Studio, what line is faulting?

Also, let's consider if no records were retrieved. Line 29 and I could be wrong, looks like it could fail.

Member Avatar for 404notfound

Sorry that vw.net was a typo. Was supposed to be vb.net.
Its erroring on line 32.
Can you explain why it would fail? am I missing something obvious?

Member Avatar for rproffitt

OK, a few things.

  1. On line 32 I can't find where the variable inc is declared or set.
  2. In VS (visual studio), place a break on line 32 and examine the variables in that line.
Member Avatar for 404notfound

Inc is my counter as I cycle through the records. Here's the full code, and a screenshot of my form to clarify.

1.png 

Imports System.Data.OleDb

Public Class Form1
    Public inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDbConnection

    Dim dbProvider As String
    Dim dbSource As String

    Dim ds As New DataSet
    Dim dsTrainees As New DataSet
    Dim dsItems As New DataSet

    Dim dtItems As New DataTable

    Dim da As New OleDbDataAdapter
    Dim daTrainees As New OleDbDataAdapter
    Dim daItems As New OleDbDataAdapter

    Dim sql As String
    Dim sqlTrainees As String
    Dim sqlItems As String

    Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs)
        Me.Close()

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.MdiParent = frmContainer
        Me.MaximizeBox = False

        dbProvider = "provider = microsoft.ace.oledb.12.0;"
        dbSource = "Data Source = C:\DatabaseFiles\Safety5.accdb"

        con.ConnectionString = dbProvider & dbSource

        Try
            con.Open()
            MsgBox(con.State.ToString)
            sql = "SELECT * FROM tblTrainingInfo"
            sqlTrainees = "SELECT * FROM tblTrainees"
            sqlItems = "SELECT * FROM tblTrainingItems"

            da = New OleDbDataAdapter(sql, con)
            daTrainees = New OleDbDataAdapter(sqlTrainees, con)
            daItems = New OleDbDataAdapter(sqlItems, con)

            da.Fill(ds, "Training")
            daTrainees.Fill(dsTrainees, "Trainees")
            daItems.Fill(dsItems, "Items")

            con.Close()
            MaxRows = ds.Tables("Training").Rows.Count
            inc = -1

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

    Public Sub NavigateTrainees()
        Dim dsTrainees As New DataSet
        Dim dtTrainees As New DataTable

        dsTrainees.Tables.Add(dtTrainees)
        Dim daTrainees As New OleDb.OleDbDataAdapter("SELECT ID, Fname, Mname, Lname from tblTrainees where ID = " & txtTrainingID.Text, con)
        daTrainees.Fill(dtTrainees)
        dgvTrainees.DataSource = dtTrainees.DefaultView
    End Sub

    Private Sub NavigateTrainingItems()

        chkDrugAlcoholPolicy.Checked = False
        chkElectricalSafety.Checked = False
        chkEmergencyResponse.Checked = False
        chkFirstAid.Checked = False
        chkForkliftTraining.Checked = False
        chkGlobalGap.Checked = False
        chkHeatStress.Checked = False
        chkLadderSafety.Checked = False
        chkLiftingItems.Checked = False
        chkOperatingEquipInspection.Checked = False
        chkPesticide.Checked = False
        chkPPE.Checked = False
        chkReportInjuryIllnessDeath.Checked = False
        chkSafetyPolicyProcedure.Checked = False
        chkSanitation.Checked = False
        chkSexualHarassment.Checked = False
        chkSlipsTripsFalls.Checked = False
        chkTransportation.Checked = False
        chkTreeWorkPruningOperations.Checked = False
        chkUseTools.Checked = False

        Dim dsTrainingItems As New DataSet
        Dim dtTrainingItems As New DataTable

        dsTrainingItems.Tables.Add(dtTrainingItems)
        Dim daTrainingItems As New OleDb.OleDbDataAdapter("SELECT * FROM tblTrainingItems WHERE ID = " & txtTrainingID.Text, con)
        daTrainingItems.Fill(dtTrainingItems)

        Try

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("Sanitation") = 1 Then
                chkSanitation.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("Pesticides") = 1 Then
                chkPesticide.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("UseOfTools") = 1 Then
                chkUseTools.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("LiftingItems") = 1 Then
                chkLiftingItems.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("ElectricalSafety") = 1 Then
                chkElectricalSafety.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("SafetyPolicyProcedure") = 1 Then
                chkSafetyPolicyProcedure.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("ReportingInjuriesIllnessDeath") = 1 Then
                chkReportInjuryIllnessDeath.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("TreeWorkPruningOperations") = 1 Then
                chkTreeWorkPruningOperations.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("HeatStress") = 1 Then
                chkHeatStress.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("OperatingEquipInspection") = 1 Then
                chkOperatingEquipInspection.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("FirstAid") = 1 Then
                chkFirstAid.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("Transportation") = 1 Then
                chkTransportation.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("SexualHarrassment") = 1 Then
                chkSexualHarassment.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("GlobalGap") = 1 Then
                chkGlobalGap.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("ForkliftTraining") = 1 Then
                chkForkliftTraining.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("EmergencyResponse") = 1 Then
                chkEmergencyResponse.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("SlipsTripsFalls") = 1 Then
                chkSlipsTripsFalls.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("LadderSafety") = 1 Then
                chkLadderSafety.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("PPE") = 1 Then
                chkPPE.Checked = True
            End If

            If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("DrugAlcoholPolicy") = 1 Then
                chkDrugAlcoholPolicy.Checked = True
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub
    Private Sub NavigateRecords()
        Dim cb As New OleDb.OleDbCommandBuilder

        txtTrainingID.Text = ds.Tables("Training").Rows(inc).Item(1)

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(2)) Then
            cbRanches.Text = ds.Tables("Training").Rows(inc).Item(2)
        Else
            cbRanches.Text = ""
        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(3)) Then
            txtLocation.Text = ds.Tables("Training").Rows(inc).Item(3).ToString
        Else
            txtLocation.Text = ""
        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(4)) Then
            mtbDate.Text = ds.Tables("Training").Rows(inc).Item(4)
        Else
            mtbDate.Clear()
        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(5)) Then
            mtbStartTime.Text = ds.Tables("Training").Rows(inc).Item(5)
        Else
            mtbStartTime.Clear()
        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(6)) Then
            mtbFinishTime.Text = ds.Tables("Training").Rows(inc).Item(6)
        Else
            mtbFinishTime.Text = ""
        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(7)) Then
            txtCrews.Text = ds.Tables("Training").Rows(inc).Item(7)
        Else
            txtCrews.Clear()
        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(7)) Then

            txtCrews.Text = ds.Tables("Training").Rows(inc).Item(7)
        Else
            txtCrews.Clear()
        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(8)) Then
            txtSupervisor.Text = ds.Tables("Training").Rows(inc).Item(8)
        Else
            txtSupervisor.Clear()

        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(9)) Then
            txtForeperson.Text = ds.Tables("Training").Rows(inc).Item(9)
        Else
            txtForeperson.Clear()
        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(10)) Then
            txtActivity.Text = ds.Tables("Training").Rows(inc).Item(10)
        Else
            txtActivity.Clear()
        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(11)) Then
            txtTrainer.Text = ds.Tables("Training").Rows(inc).Item(11)
        Else
            txtTrainer.Clear()

        End If

        If Not IsDBNull(ds.Tables("Training").Rows(inc).Item(12)) Then
            txtTopics.Text = ds.Tables("Training").Rows(inc).Item(12)
        Else
            txtTopics.Clear()
        End If

    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        MessageBox.Show(inc.ToString)
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
            NavigateTrainees()
            NavigateTrainingItems()
        Else
            MsgBox("No more records")
        End If
    End Sub

    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
        If inc > 0 Then
            inc = inc - 1

            NavigateRecords()
            NavigateTrainees()

        Else
            MsgBox("No more rows")
        End If
    End Sub

    Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
            NavigateTrainees()

        End If
    End Sub

    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click

        If inc < MaxRows Then
            inc = MaxRows - 1
            NavigateRecords()
            NavigateTrainees()

        End If
    End Sub

    Private Sub NewToolStripMenuItem_Click(sender As Object, e As EventArgs)
        txtTrainingID.Text = MaxRows + 1000 + 1

        cbRanches.SelectedItem = ""
        cbRanches.Focus()
        txtCrews.Clear()
        txtLocation.Clear()
        txtSupervisor.Clear()
        txtForeperson.Clear()
        txtTrainer.Clear()
        mtbDate.Clear()
        mtbStartTime.Clear()
        mtbFinishTime.Clear()
        txtActivity.Clear()
        txtTopics.Clear()
        btnSave.Visible = True
        btnCancel.Visible = True
        btnAttendees.Visible = True
        dgvTrainees.DataSource = Nothing
        RanchList()

    End Sub

    Private Sub SearchToolStripMenuItem_Click(sender As Object, e As EventArgs)
        frmSearch.Show()

    End Sub

    Private Sub RanchList()
        Dim cmd As OleDb.OleDbCommand
        Dim adapter As OleDbDataAdapter
        Dim dt As New DataTable

        cbRanches.Items.Clear()
        sql = "SELECT * FROM tblTrainingInfo"

        cmd = New OleDbCommand(sql, con)

        Try
            con.Open()
            adapter = New OleDbDataAdapter(cmd)
            adapter.Fill(dt)
            cbRanches.Items.Clear()

            For Each row In dt.Rows
                cbRanches.Items.Add(row(2))
            Next
            con.Close()
            For i As Int16 = 0 To cbRanches.Items.Count - 2
                For j As Int16 = cbRanches.Items.Count - 1 To i + 1 Step -1
                    If cbRanches.Items(i) = cbRanches.Items(j) Then
                        cbRanches.Items.RemoveAt(j)
                    End If
                Next
            Next

        Catch ex As Exception
            MsgBox(ex.ToString)

        End Try

    End Sub

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        frmSearch.Show()

    End Sub
End Class

Okay I put in a MessageBox.Show(dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("Sanitation").ToString) to show what value was in there (only way I know to show whats in the variable) and it came back with a null value error.

Member Avatar for Santanu.Das

If dsTrainingItems.Tables("TrainingItems").Rows(inc).Item("Sanitation") = 1 Then
chkSanitation.Checked = True
End If

Where from you get the table name "TrainingItems" ?
You can call by using index from the tables array of the dataset.
It should be

If dsTrainingItems.Tables(0).Rows(inc).Item("Sanitation") = 1 Then chkSanitation.Checked = True
Member Avatar for tinstaafl

I would suggest checking for Nothing instead of DBNull:

If Not IsNothing(ds.Tables("Training").Rows(inc).Item(3)) Then
    txtLocation.Text = ds.Tables("Training").Rows(inc).Item(3).ToString
Else
    txtLocation.Text = ""
End If

A function that returns an acceptable value would help to de-clutter your code:

Private Function GetValue(itemIndex As Integer) As String
    If Not IsNothing(ds.Tables("Training").Rows(inc).Item(itemIndex)) Then
        Return ds.Tables("Training").Rows(inc).Item(itemIndex)
    Else
        Return ""
    End If
End Function

txtTrainer.Text = GetValue(11).
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.