Debugging error "NullReferenceException was unhandled'

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

34 Replies - 7150 Views - Last Post: 26 January 2011 - 01:01 PM Rate Topic: -----

#1 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 08:55 AM

I have the following code on my form:

Imports System.Data.SqlClient
Public Class Main
    Protected WithEvents DataGridView1 As DataGridView
    Dim instForm2 As New Exceptions
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click
        Dim ssql As String = "select MAX(payrolldate) AS [payrolldate], " & _
                 "dateadd(s,  518399, max(payrolldate)) AS [Sunday]" & _
                  "from dbo.payroll" & _
                  " where payrollran = 'no'"
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand

        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = ssql
                oDr = .ExecuteReader()
            End With
            If oDr.Read Then
                payperiodstartdate = oDr.GetDateTime(1)
                payperiodenddate = payperiodstartdate.AddDays(7)
                Dim ButtonDialogResult As DialogResult
                ButtonDialogResult = MessageBox.Show("      The Next Payroll Start Date is: " & payperiodstartdate.ToString() & System.Environment.NewLine & "            Through End Date: " & payperiodenddate.ToString())
                If ButtonDialogResult = Windows.Forms.DialogResult.OK Then
                    exceptionsButton.Enabled = True
                    startpayrollButton.Enabled = False
                End If
            End If
            oDr.Close()
            oCmd.Connection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try

    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim ds As New DataSet
        Dim _sql As String = "SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime)  as duration INTO scratchpad3" & _
 " FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber" & _
 " where [Exceptions].exceptiondate between @payperiodstartdate and @payperiodenddate" & _
 " GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime," & _
 " [Exceptions].code, [Exceptions].exceptiondate"
        connection = New SqlConnection(connectionString)
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        adapter.SelectCommand = _CMD
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodstartdate", payperiodstartdate))
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodenddate", payperiodenddate))
        Try
            adapter.Fill(ds)
            If (ds Is Nothing) Then
                'it's empty
                MsgBox("There was no data for this time period, press Ok to continue", "No Data")
                connection.Close()
                Exceptions.Hide()
            Else
                'it exists and there are rows 
                adapter.Fill(ds)
                connection.Close()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
        Exceptions.Show()
    End Sub
    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
End Class



and when I try to run this in debug, I get the error "NullReferenceException was unhandled." The error shows up in my disassembly but the line in the code where this is breaking is here:

 adapter.SelectCommand = _CMD



can anyone offer any suggestions as to why this is failing?

Thank you

Doug

This post has been edited by dougancil2010: 21 January 2011 - 08:55 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Debugging error "NullReferenceException was unhandled'

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 09:08 AM

I don't see where you're giving your parameters values

Try
_CMD.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)


and see if that helps.

Or you could have used this but I think it is an outdated method.
_CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodstartdate", SqlDbType.DateTime).Value = payperiodstartdate)

This post has been edited by CharlieMay: 21 January 2011 - 09:11 AM

Was This Post Helpful? 0
  • +
  • -

#3 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 09:28 AM

Also, I don't see anywhere in Button2's click event where you're using connection.open()

This looks wrong. Are you trying to join two different tables, if not, you don't need it. If so, you need to specify the related columns between the 2 tables.
INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber

This post has been edited by CharlieMay: 21 January 2011 - 09:33 AM

Was This Post Helpful? 0
  • +
  • -

#4 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 12:01 PM

Charlie,

Thanks for catching that about the connection.open, I've added that back. As far as the values for the variables, I have a module created with those values in them. When I looked at this with debug, I see the first instance of adapter as Nothing. I'm almost absolutely sure that this is where the NullreferenceException error is coming from because looking at the values for payrollstartdate and payrollenddate, they have the correct values (before I added the table adapter to my code) oh and as far as the sql query goes, that query works and provides the results I need (it's been tested outside of the program with no errors.)

This post has been edited by dougancil2010: 21 January 2011 - 12:03 PM

Was This Post Helpful? 0
  • +
  • -

#5 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 12:18 PM

Updated code
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim ds As New DataSet
        Dim _sql As String = "SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime)  as duration INTO scratchpad3" & _
 " FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber" & _
 " where [Exceptions].exceptiondate between @payperiodstartdate and @payperiodenddate" & _
 " GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime," & _
 " [Exceptions].code, [Exceptions].exceptiondate"
        connection = New SqlConnection(connectionString)
        connection.Open()
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        adapter.SelectCommand = _CMD
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodstartdate", SqlDbType.DateTime).Value = payperiodstartdate)
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodenddate", SqlDbType.DateTime).Value = payperiodenddate)
        Try
            adapter.Fill(ds)
            If (ds Is Nothing) Then
                'it's empty
                MsgBox("There was no data for this time period, press Ok to continue", "No Data")
                connection.Close()
                Exceptions.Hide()
            Else
                'it exists and there are rows 
                adapter.Fill(ds)
                connection.Close()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
        Exceptions.Show()
    End Sub


Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9278
  • View blog
  • Posts: 34,789
  • Joined: 12-June 08

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 12:31 PM

Try adding your parameters to _CMD *BEFORE* you add it to the adapter. Most properties are 'byval'.
Was This Post Helpful? 0
  • +
  • -

#7 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 12:39 PM

Modi,

I hope this is what you mean:

what was this:
 adapter.SelectCommand = _CMD
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodstartdate", SqlDbType.DateTime).Value = payperiodstartdate)
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodenddate", SqlDbType.DateTime).Value = payperiodenddate)


is now this:
_CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodstartdate", SqlDbType.DateTime).Value = payperiodstartdate)
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodenddate", SqlDbType.DateTime).Value = payperiodenddate)
        adapter.SelectCommand = _CMD

.. if that's the case. I did change that with no difference. I still get the Null error.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9278
  • View blog
  • Posts: 34,789
  • Joined: 12-June 08

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 12:44 PM

What line is it occurring at?
Was This Post Helpful? 0
  • +
  • -

#9 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 12:47 PM

I'm getting this error from disassembly:

0000010d mov rax,qword ptr [rbp+18h]

but when I put a line break at this line I don't get the error so I'm pretty sure it's occuring here:

          adapter.Fill(ds)



Line 18 in post #5.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9278
  • View blog
  • Posts: 34,789
  • Joined: 12-June 08

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 12:49 PM

Are you running this in 'debug' mode? Put a break point at the top of the event and step through each line until it fails.

Why are you filling the dataset twice? Lines 56 and 64? Do it once at 56..

I ran your code (for the most part) and with the exception of making the parameters more normal with what I do it did not error out on me.
 Private Sub Button2_Click() '(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim connectionString As String = "data source=zzz;initial catalog=zzz;Integrated Security=True"
        Dim ds As New DataSet

        Dim payperiodstartdate As New DateTime(2011, 1, 19)
        Dim payperiodenddate As New DateTime(2011, 1, 20)
        Dim _sql As String = "select top(10) * from [dbo].zzzz with(nolock) where [DATE_ENTERED] > @payperiodstartdate and [DATE_ENTERED] < @payperiodenddate "
        connection = New SqlConnection(connectionString)
        connection.Open()
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        adapter.SelectCommand = _CMD
        _CMD.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
        _CMD.Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
        Try
            adapter.Fill(ds)
            If (ds Is Nothing) Then
                'it's empty
                MsgBox("There was no data for this time period, press Ok to continue")
                connection.Close()
                'Exceptions.Hide()
           ' Else
                'it exists and there are rows 
                'adapter.Fill(ds)
                'connection.Close()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        Finally
            connection.Close()
        End Try
        '  Exceptions.Show()
    End Sub


This post has been edited by modi123_1: 21 January 2011 - 01:05 PM

Was This Post Helpful? 0
  • +
  • -

#11 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 01:13 PM

ok so I stepped through the process and now it's failing at this line:

   Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)



but the sqlcommand is correct.
Was This Post Helpful? 0
  • +
  • -

#12 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 01:23 PM

Modi,

I don't get it either. I just reviewed your code alongside mine and as you said, besides your parameters being more "normal" it's essentially the same code and I'm getting that error.
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9278
  • View blog
  • Posts: 34,789
  • Joined: 12-June 08

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 01:35 PM

Where are you using this? Webform? Winform?
Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 01:37 PM

doug,
I just copied modi123_1's code and made changes to accept my database and it's working fine here too.

Maybe remark out your _Sql statement and try a more generic one just as a test. I know you have it working outside the code but it would be something to try.
Was This Post Helpful? 0
  • +
  • -

#15 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Debugging error "NullReferenceException was unhandled'

Posted 21 January 2011 - 01:38 PM

Modi its a Winform.

Here's what I did:

 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxxx;uid=xxxxx;password=xxxxxx"
        Dim ds As New DataSet
        Dim _sql As String = "drop table scratchpad1"
        connection = New SqlConnection(connectionString)
        connection.Open()
        'Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        '_CMD.Parameters.AddWithValue("@payperiodstartdate", SqlDbType.DateTime)
        ' _CMD.Parameters.AddWithValue("@payperiodenddate", SqlDbType.DateTime)
        ' adapter.SelectCommand = _CMD
        Try
            adapter.Fill(ds)
            If (ds Is Nothing) Then
                'it's empty
                MsgBox("There was no data for this time period, press Ok to continue", "No Data")
                connection.Close()
                Exceptions.Hide()
            Else
                connection.Close()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
        Exceptions.Show()
    End Sub



and I'm still getting the error.

This post has been edited by dougancil2010: 21 January 2011 - 01:45 PM

Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3