compare date today from saved date in mysql

[OtepTheThird]

Hi Guys,

Im currently working with application, where it will automatically inform the user that there is an overdue account today.
i decided to compare the current date(today) to stored date in mysql database, heres my code:

    Public Sub getReminder()
        Dim _con1 As New MySqlConnection
        Dim _cmd1 As New MySqlCommand
        Dim dr As MySqlDataReader
        Dim date1 As String


        With _con1
            .ConnectionString = "server=xxx.xxx.x.x;uid=xxx;pwd=xxx;database=daspatch"

            If _con1.State = ConnectionState.Closed Then
                _con1.Open()
            End If
        End With

        'date1 = Convert.ToDateTime(Today)

        _cmd1 = New MySqlCommand("SELECT date_due FROM billing_tbl", _con1)

        date1 = Date.Today

        dr = _cmd1.ExecuteReader()

        If dr.HasRows Then
            MsgBox("You have account already due")
        Else
            MsgBox("You dont have account due today")
        End If

    End Sub

error: date cannot convert to boolean()
i know i missed something here.

Thanks.

Otep

[Minimalist]

date1 = Date.Today should be date1 = CStr(Date.Today) because you declared date1 as string.

[Richard Mon]

Try this code and put in on the form where it loads(form load).

Private Sub NotifyAccount()

        Dim start As Date = dr.ToString
        Dim ts As TimeSpan
        Dim days As Integer
        Dim cmd As SqlCommand
        Dim sql As String
        Dim reader As SqlDataReader
        ts = start.Subtract(Now)
        days = ts.Days

        If days > 0 Then
            MsgBox("You have account already due")
        Else
            MsgBox("You dont have account due today")
        EndIf

EndSub

[OtepTheThird]

Hi Richard,
Thanks for the codes, i integrated in my code though it does not provide any errors but theres still something missing.

ive tried a lot of method with this problem but i cant arrive to what i want.
Anyways, in my datatable in MySQL I have date saved in this format MM-dd-yyyy now i want to call it on form load and notify user if theres an overdue account.

I just need a simple query and conditional statement on this.

e.g. query that will compare the current date to saved date in database and condition that if query founds date which is less than 30 days from current date it will trigger or provide message that there is an overdue.

Hope you can help me with this.

Salamat and Mabuhay

[Reverend Jim]

Why not use the builtin function. In MS SQL it is

SELECT COUNT(*) FROM billing_tbl
 WHERE CONVERT(DATE,date_due) = CONVERT(DATE,GETDATE())

I think the MySQL version is

SELECT COUNT(*)
  FROM billing_tbl
 WHERE DATE(date_due) = DATE(NOW())

[Santanu.Das]

Check the difference between the dates by using DateDiff() function.
It should be like

 If dr.HasRows Then
    If DateDiff(DateInterval.Day, dr("due_date"), Now()) < 30 Then
        MsgBox("You have account already due")
    Else
        MsgBox("You dont have account due today")
    End If
 End If