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 !