locked
Error when Form Loads (System Null Reference Exception) RRS feed

  • Question

  • Hi Good People

    I have a form called Money Totals. And what I am trying to achieve is when the form loads it shows running totals of a particular column, But I get an ( System Null Reference Exception, SQL was Nothing )

    The code I have, I thought checked if record existed or not. 

    Private Sub ItemsPurchased()
            'Sum Of Items Purchased =====================================
            SQL.ExecQuery("SELECT Sum(AmountPaid) As AccountSum " &
                     "FROM ItemsPurchased ")
            If SQL.RecordCount < 1 Then Exit Sub
            TxtItemsPurchased.Text = "£0.00"
    
            If SQL.RecordCount > 0 Then
                TxtItemsPurchased.Text = SQL.SQLDT.Rows(0)("AccountSum").ToString()
                TxtItemsPurchased.Text = FormatCurrency(TxtItemsPurchased.Text)
            End If
        End Sub

    Another Code I tried ...

     Private Sub ItemsPurchased()
            'Sum Of Items Purchased =====================================
            SQL.ExecQuery("SELECT Sum(AmountPaid) As AccountSum " &
                     "FROM ItemsPurchased ")
            If SQL.RecordCount < 1 Then
                TxtItemsPurchased.Text = "£0.00"
    
            ElseIf SQL.RecordCount > 0 Then
                TxtItemsPurchased.Text = SQL.SQLDT.Rows(0)("AccountSum").ToString()
                TxtItemsPurchased.Text = FormatCurrency(TxtItemsPurchased.Text)
            End If
        End Sub
     

    Can you good People show me or tell me where I am going wrong Please

    Kind Regards 

    Gary

     

    Gary Simpson

    Thursday, July 2, 2020 12:50 PM

Answers

  • Hello,

    Check for DbNull e.g. (no frills)

    Public Shared Sub SumDemo()
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText = "SELECT SUM(UnitPrice) AS Total FROM dbo.[Order Details] " &
                                  "WHERE dbo.[Order Details].OrderID = 10248;"
    
                cn.Open()
    
                Dim sumResult = cmd.ExecuteScalar()
                '
                ' Check for no value
                '
                If IsDBNull(sumResult) Then
                    Console.WriteLine("bad")
                Else
                    Console.WriteLine(sumResult)
                End If
            End Using
        End Using
    End Sub
    Edit: If the value is not DBNull then cast and format as needed as it's currently an object.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, July 2, 2020 1:17 PM

All replies

  • Hello,

    Check for DbNull e.g. (no frills)

    Public Shared Sub SumDemo()
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText = "SELECT SUM(UnitPrice) AS Total FROM dbo.[Order Details] " &
                                  "WHERE dbo.[Order Details].OrderID = 10248;"
    
                cn.Open()
    
                Dim sumResult = cmd.ExecuteScalar()
                '
                ' Check for no value
                '
                If IsDBNull(sumResult) Then
                    Console.WriteLine("bad")
                Else
                    Console.WriteLine(sumResult)
                End If
            End Using
        End Using
    End Sub
    Edit: If the value is not DBNull then cast and format as needed as it's currently an object.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, July 2, 2020 1:17 PM
  • Hi Karen.

    Thank you very much for getting back to me so quickly.

    The code I ended up with is ...

     Public Shared Sub SumDemo()
    
            Using cn As New SqlConnection With {.ConnectionString = "My Connection String"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT SUM(AmountPaid) AS Total " &
                                       "FROM ItemsPurchased;"
    
                    cn.Open()
    
                    Dim sumResult = cmd.ExecuteScalar()
                    '
                    ' Check for no value
                    '
                    If IsDBNull(sumResult) Then
                        FrmMoneyTotals.TxtItemsPurchased.Text = "£0.00"
                    Else
                        FrmMoneyTotals.TxtItemsPurchased.Text = FormatCurrency(sumResult)
                    End If
                End Using
            End Using
        End Sub

    I removed part of your code ( WHERE dbo.[Order Details],OrderID = 10248;)

    Because I was only interested in one column to check (AmountPaid) And if there was any values in that column, If no Values then show Currency values of 0.00 in a textbox. But if values exist then Add all the Values in that column. then put the sum value into the textbox.

    Karen Thank you very much

    Best Regards

    Gary


    Gary Simpson

    Thursday, July 2, 2020 2:21 PM
  • Glad to assist. Note the WHERE was so I could have a working copy to give you and figured you would remove the WHERE.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, July 2, 2020 2:29 PM