Member Avatar for සශික

I making application using vb.net and mysql database(mysql query broweser). In my programm there is two date time pickers (dtpisf & dtpist). I want to run this application like this, when I select two dates from date time pickers and click button it will show total of column in text box.
I recently used correct command and danniweb team help me to that.
but all time when I select two date range and perform command it show "Object cannot be cast from DBNULL to other types" after this happen I checked that sql command (query) on mysql query brwser. It show result sucessfully.
I also find method to resolve this. But still my vb.net knowledge is not good. that's why I can't deal with this error.
Therefore please help me this.
VB.net button code :

Private Sub Button18_Click(sender As Object, e As EventArgs) Handles Button18.Click
       query = "SELECT sum(fuel) as ttlfuel  FROM 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

When at mysql query browser I run this code :

SELECT sum(fuel)as ttlfuel FROM expenses
WHERE datee BETWEEN '2016-01-01' and '2016-09-01';

On the mysql query browser this code show result and also when I change query code at vb.net it show result in text box.
but when I change date values to date time pickers "Object cannot be cast from DBNULL to other types" error shows.
Therefore I think this error show because wrong date format.
Please be kind to solve this guys !

Member Avatar for සශික

I also created parameters ! hey look and help me :/

query = "SELECT sum(fuel) as ttlfuel  FROM expenses WHERE datee BETWEEN '" & dtpisf.Value & "' and '" & dtpist.Value & "'"
        Dim cmd = New MySqlCommand(query, con)
        cmd.Parameters.AddWithValue("@startdate", dtpisf.Value.Date.ToString("yyyy-dd-MM"))
        cmd.Parameters.AddWithValue("@enddate", dtpist.Value.Date.ToString("yyyy-dd-MM"))
        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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.