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

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 10 Months Ago by Reverend Jim

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 article has been dead for over six months. Start a new discussion instead.