0

I'm trying to load sum of columns to text boxes. but it give me this error:
"Object cannot be cast from DBNULL to other types"
this is code :

query = "SELECT sum(fuel) as ttlfuel, sum(stationery) as ttlstationery, sum(salary) as ttlsalary, sum(vehicle_part) as ttlvp, sum(other) as ttlother FROM mcs.expenses WHERE datee BETWEEN '" & dtpisf.Value & "' AND '" & dtpist.Value & "'"
        Dim cmd = New MySqlCommand(query, con)
        Dim dr As MySqlDataReader
        Try
            con.Open()
            dr = cmd.ExecuteReader()
            While dr.Read()
                Me.txtfuel.Text = Convert.ToDecimal(dr("ttlfuel"))
                Me.txtstationary.Text = Convert.ToDecimal(dr("ttlstationery"))
                Me.txtcollectorsalery.Text = Convert.ToDecimal(dr("ttlsalary"))
                Me.txtvehiclepart.Text = Convert.ToDecimal(dr("ttlvp"))
                Me.txtotherexpenses.Text = Convert.ToDecimal(dr("ttlother"))
            End While
            dr.Close()
            con.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
            Try
                If (con.State = ConnectionState.Open) Then
                    con.Close()
                End If
            Catch ex2 As Exception
                MsgBox(ex2.Message)
            End Try
        End Try
3
Contributors
6
Replies
16
Views
1 Year
Discussion Span
Last Post by සශික
Featured Replies
  • If one of your database columns does not have value then it will return NULL. You can either test for that before you try to convert or you can use a function in your query to return a default value (like zero). The MS SQL function is `COALESCE` as I … Read More

1

If one of your database columns does not have value then it will return NULL. You can either test for that before you try to convert or you can use a function in your query to return a default value (like zero). The MS SQL function is COALESCE as I recall. I'm about to go out but I can check this with an example when I return.

Edited by Reverend Jim

0

I assumed that datee was a column in his table and was spelled that way because Date is a reserved word. I sill think the problem is because one or more colums is NULL. Should be easy to tell by setting a breakpoint at the first line within the while loop and examining the values in the datareader.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.