Date & Database & Calculation

[Alexandru_mbm]

I have a real problem for the last 2 days...

I have a date stored in my MS Access DB as "ShortDate" (dd/MM/yyyy).

I need to read this date and compare it with "current date - X days". Where X days is a Value saved by the user (into another form 5, 30, 60 days... etc).

In the short way:


If "Current Date - 55 Days" it's equal OR in that interval of "DataBase Date" then MessageBox.Show("Warning!!! Expiring in {X} Days")

ElseIf "Current Date - 55 Days" it's greater than "DataBase Date" MessageBox.Show("It's expired.")

EndIf

Dim data_curenta As Date = Date.Now
Dim data_curenta_formatata As Date = data_curenta.ToString("dd/MM/yyyy")
Public Shared valabilitate_cu, valabilitate_prcu, valabilitate_acd, valabilitate_pracd As String
Public Shared data_expirare_bzd_prcu_aditional As Date

..................................


   Private Sub verificare_valabilitate_prcu()

        Dim Conexiune As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Windows.Forms.Application.StartupPath & "\urb.mdb;Jet OLEDB:Database Password=XXXXXXX"
        Dim cn As New OleDb.OleDbConnection(Conexiune)
        Dim sql As String = "SELECT prelungire_data_expirare FROM cu WHERE prelungire_activa='1' AND prelungire_folosita='0'"
        Dim cmd As New OleDb.OleDbCommand(sql, cn)
        Dim dr As OleDb.OleDbDataReader

        Try

            cn.Open()
            dr = cmd.ExecuteReader

            While (dr.Read)

                Dim data_expirare_bzd_prcu As Date = dr("prelungire_data_expirare")
                Dim data_expirare_bzd_prcu_formatata As Date = data_expirare_bzd_prcu.ToString("dd/MM/yyyy")
                data_expirare_bzd_prcu_aditional = data_expirare_bzd_prcu_formatata.AddDays(-valabilitate_prcu - 1).ToString("dd/MM/yyyy")

            End While

            cn.Close()

        Catch ex As Exception

            Me.TopMost = False
            frm_eroare.lbl_numar_eroare.Text = Err.Number
            frm_eroare.txt_detalii_eroare.Text = Me.Text & " (" & Me.Name & ")" & vbCrLf & vbCrLf & ex.Message & ex.StackTrace & " (" & Err.Source & "-" & Err.LastDllError & "-" & Err.Erl & ")"
            frm_eroare.Show()

        End Try

        If (cn.State And ConnectionState.Open) <> 0 Then

            cn.Close()

        End If

        '#####################################

        Me.Cursor = Cursors.WaitCursor

        Dim conn As OleDb.OleDbConnection
        Dim data As DataTable
        Dim Adapter As OleDb.OleDbDataAdapter
        Dim CommandBuild As OleDb.OleDbCommandBuilder

        Dim connStr As String
        connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Windows.Forms.Application.StartupPath & "\urb.mdb;Jet OLEDB:Database Password=XXXXXXX"
        conn = New OleDb.OleDbConnection(connStr)

        Try
            conn.Open()

            data = New DataTable
            Adapter = New OleDb.OleDbDataAdapter("SELECT numar_certificat_2, data_certificat_2, nume_solicitant, prelungire_data_expirare FROM cu WHERE prelungire_data_expirare < " & data_expirare_bzd_prcu_aditional & " AND prelungire_activa='1' AND prelungire_folosita='0'".ToString, conn)
            CommandBuild = New OleDb.OleDbCommandBuilder(Adapter)

            Adapter.Fill(data)

            Me.dgv_prcu.DataSource = data

            Me.dgv_prcu.Columns(0).Width = 100
            Me.dgv_prcu.Columns(1).Width = 100
            Me.dgv_prcu.Columns(2).Width = 250
            Me.dgv_prcu.Columns(3).Width = 100

            Me.dgv_prcu.Columns(0).HeaderText = "Nr. document"
            Me.dgv_prcu.Columns(1).HeaderText = "Din data"
            Me.dgv_prcu.Columns(2).HeaderText = "Pe numele de"
            Me.dgv_prcu.Columns(3).HeaderText = "Expira la"

            Me.Cursor = Cursors.Default

            conn.Close()

        Catch ex As OleDb.OleDbException
            MessageBox.Show("Eroare: " + ex.Message & "", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Cursor = Cursors.Default
        End Try

    End Sub

i just cannot imagine how can i do this ? i have searched.... i have tryied in may ways...

Thanks in advice....

[jmcilhinney]

First up, do NOT convert any Date objects to Strings. Dates are Dates and should remain Dates at all times. If you get a Date value from the database then you can easily compare that to the current Date, e.g.:

If Date.Today.AddDays(-55) > CDate(myDataRowOrDataReader("SomeDateColumn")) Then