Date & Database & Calculation
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
Code:
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....
Re: Date & Database & Calculation
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.:
vb.net Code:
If Date.Today.AddDays(-55) > CDate(myDataRowOrDataReader("SomeDateColumn")) Then
