[RESOLVED] Datatable error on call from click event subroutine

[projecttoday]

I have a subroutine which reads data from a datatable. When I call the subroutine from my button click event subroutine I get Object reference not set to an instance of an object.

It works when I call it from the load event subroutine. It must be because the Dim dt as New Datatable is in this subroutine.

I have tried moving Dim dt as New Datatable outside of the subroutines and I have tried adding the datatable as a parameter in the click event subroutine to no avail.

What to do? I can post the code if that will help.

[Maverickz]

I can post the code if that will help.

It will.

[projecttoday]

This is where the error occurs:

    Private Sub DisplayPage(ByVal PagN As Long, ByVal dt As DataTable)
        Dim iTopMargin As Long, index1 As Long, txtbxTextBox As TextBox, iRowHeight As Long

        iRowHeight = 36
        iTopMargin = 4
        index1 = 1

        For Each row As DataRow In dt.Rows          <---- this is the line that gets the error
            If CLng(row.Item("pagenum")) <> PagN Then Continue For
            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 140
            txtbxTextBox.Height = 58
            txtbxTextBox.Left = 4
            txtbxTextBox.Font = New Font("Microsoft Sans Serif", 12)
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("warehouse")), String.Empty, row.Item("warehouse"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)
            txtbxTextBox = Nothing

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 172
            txtbxTextBox.Height = 58
            txtbxTextBox.Left = 148
            txtbxTextBox.Font = New Font("Microsoft Sans Serif", 12)
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("locinwhse")), String.Empty, row.Item("locinwhse"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)
            txtbxTextBox = Nothing

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 140
            txtbxTextBox.Height = 58
            txtbxTextBox.Left = 324
            txtbxTextBox.Font = New Font("Microsoft Sans Serif", 12)
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("modelcategory")), String.Empty, row.Item("modelcategory"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)
            txtbxTextBox = Nothing

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 100
            txtbxTextBox.Height = 50
            txtbxTextBox.Left = 530
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("make")), String.Empty, row.Item("make"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 100
            txtbxTextBox.Height = 50
            txtbxTextBox.Left = 640
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("modelname")), String.Empty, row.Item("modelname"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 100
            txtbxTextBox.Height = 50
            txtbxTextBox.Left = 750
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("serialno")), String.Empty, row.Item("serialno"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 70
            txtbxTextBox.Height = 50
            txtbxTextBox.Left = 860
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("quantity")), String.Empty, row.Item("quantity"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 200
            txtbxTextBox.Height = 50
            txtbxTextBox.Left = 925
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("comments")), String.Empty, row.Item("comments"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 12
            txtbxTextBox.Height = 50
            txtbxTextBox.Left = 1188
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("removeddate")), String.Empty, row.Item("removeddate"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 12
            txtbxTextBox.Height = 50
            txtbxTextBox.Left = 1220
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("returneddate")), String.Empty, row.Item("returneddate"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)

            txtbxTextBox = New TextBox
            txtbxTextBox.Top = iTopMargin + (iRowHeight * (index1 - 1))
            txtbxTextBox.Width = 12
            txtbxTextBox.Height = 50
            txtbxTextBox.Left = 1250
            txtbxTextBox.Text = IIf(IsDBNull(row.Item("stockspare")), String.Empty, row.Item("stockspare"))
            txtbxTextBox.Tag = IIf(IsDBNull(row.Item("stockitemid")), String.Empty, row.Item("stockitemid"))
            AddHandler txtbxTextBox.Click, AddressOf MyMessage
            ReportPanel.Controls.Add(txtbxTextBox)
            index1 = index1 + 1
        Next

        Me.Controls.Add(ReportPanel)
        ReportPanel.Visible = True
        txtbxTextBox = Nothing
    End Sub

This is where the above is called from when producing the error:

    Private Sub NextButton_Click(ByVal Sender As System.Object, ByVal e As System.EventArgs)
        PagN = PagN + 1
        If PagN = NPage Then
            Me.NextButton.Enabled = True
            Me.PreviousButton.Enabled = True
            Me.LastButton.Enabled = False
            Me.NextButton.Enabled = False
        ElseIf PagN < NPage And PagN > 1 Then
            Me.FirstButton.Enabled = True
            Me.PreviousButton.Enabled = True
            Me.LastButton.Enabled = True
            Me.NextButton.Enabled = True
        ElseIf PagN < 1 Or PagN > NPage Then
            Exit Sub
        End If
        Me.PageNoTextBox.Text = PagN
        Me.ReportPanel = Nothing
        DisplayPage(PagN, dt)            <---- This is where the above is called
    End Sub

Definition of the datatable:

Public Class MDIMainParentForm
    Dim dbConnection As New OleDbConnection(connSTR)
    Dim HeaderPanel As Panel, ReportPanel As Panel, FooterPanel As Panel
    Dim PagN As Long, NPage As Long
    Dim FirstButton As Button, NextButton As Button, LastButton As Button, PreviousButton As Button, PageNoTextBox As TextBox
    Dim FirstLabel As Label, NextLabel As Label, LastLabel As Label, PreviousLabel As Label, PageNoLabel As Label
    Dim dt As DataTable                              <------ this is the datatable

Code which fills the datatable:

        Dim da As New OleDbDataAdapter(strSQL, dbConnection)
        Dim dt As New DataTable
        Try
            dbConnection.Open()
        Catch exc As OleDbException
            MsgBox(exc.Message)
            Exit Sub
        End Try
        da.Fill(dt)
        da.Dispose()
        da = Nothing

        dbConnection.Close()

Code which calls the subroutine successfully. I can repeat this and it will work.

DisplayPage(1, dt)

The above 2 snipets are in this subroutine:

    Private Sub StockReportButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles StockReportButton.Click

[Maverickz]

In this code block change ByVal dt As DataTable to ByRef dt As DataTable
Really with the other two suggested changes below, you shouldn't even need to pass it in, but since you already are passing it in we are changing it to ByRef.

    Private Sub DisplayPage(ByVal PagN As Long, ByVal dt As DataTable)
        Dim iTopMargin As Long, index1 As Long, txtbxTextBox As TextBox, iRowHeight As Long

        iRowHeight = 36
        iTopMargin = 4
        index1 = 1

        For Each row As DataRow In dt.Rows          <---- this is the line that gets the error

In this code block change Dim dt As DataTable to Dim dt As New DataTable

Public Class MDIMainParentForm
    Dim dbConnection As New OleDbConnection(connSTR)
    Dim HeaderPanel As Panel, ReportPanel As Panel, FooterPanel As Panel
    Dim PagN As Long, NPage As Long
    Dim FirstButton As Button, NextButton As Button, LastButton As Button, PreviousButton As Button, PageNoTextBox As TextBox
    Dim FirstLabel As Label, NextLabel As Label, LastLabel As Label, PreviousLabel As Label, PageNoLabel As Label
    Dim dt As DataTable                              <------ this is the datatable

In this code block delete the line Dim dt As New DataTable

        Dim da As New OleDbDataAdapter(strSQL, dbConnection)
        Dim dt As New DataTable
        Try
            dbConnection.Open()
        Catch exc As OleDbException
            MsgBox(exc.Message)
            Exit Sub
        End Try
        da.Fill(dt)
        da.Dispose()
        da = Nothing

        dbConnection.Close()

Let me know how that works.

[projecttoday]

After making these 3 changes I get a Value cannot be null error message on

da.Fill(dt)

. And the same if I restore ByRef back to ByVal.

[Maverickz]

Ok change it back to ByRef. Put the Dim dt As New DataTable back where we removed it from and comment out the one we changed to be Dim dt As New DataTable.

[projecttoday]

When I do that, on this line

DisplayPage(PagN, dt)

in the NextButton subroutine I get 'dt' is not declared

[Maverickz]

Ok

In this block put the line back how it was originally

Public Class MDIMainParentForm
    Dim dbConnection As New OleDbConnection(connSTR)
    Dim HeaderPanel As Panel, ReportPanel As Panel, FooterPanel As Panel
    Dim PagN As Long, NPage As Long
    Dim FirstButton As Button, NextButton As Button, LastButton As Button, PreviousButton As Button, PageNoTextBox As TextBox
    Dim FirstLabel As Label, NextLabel As Label, LastLabel As Label, PreviousLabel As Label, PageNoLabel As Label
    Dim dt As DataTable                              <------ this is the datatable

In this block change "Dim dt As New DataTable" to "dt = New DataTable"

        Dim da As New OleDbDataAdapter(strSQL, dbConnection)
        Dim dt As New DataTable
        Try
            dbConnection.Open()
        Catch exc As OleDbException
            MsgBox(exc.Message)
            Exit Sub
        End Try
        da.Fill(dt)
        da.Dispose()
        da = Nothing

        dbConnection.Close()

[projecttoday]

It works! Thanks so much. You have been very helpful.

[Maverickz]

It works! Thanks so much. You have been very helpful.

No problem, don't forget to mark the thread resolved.