I am working on a project using vb and one of my forms has to display the current active reminders (reminders that haven't hit their deadline yet) into a datagridview and I have another datagridview for reminders that are past the deadline. The date and time along with reminder information is saved into my access database and I want to read the date and time from database and compare it to the system date and time and then display the reminder information.

This is how my form looks like; the top datagridview is for current reminders and the bottom one is for past/out of date reminders:

This the code for my form and what I’ve tried:

Imports System.Data.OleDb

Public Class frmReminderInfo

    Private Sub frmReminderInfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DisplayCurrentReminders()
    End Sub

    Private Sub DisplayCurrentReminders()
        Dim ReminderDateTime As Date
        Dim CurrentDateTime As Date
        CurrentDateTime = Date.Now
        CurrentDateTime = FormatDateTime(Date.Now, DateFormat.GeneralDate)
        ReminderDateTime = FormatDateTime(ReminderDateTime, DateFormat.GeneralDate)
        If DbConnect() Then
            DgvCurrentReminders.Rows.Clear()
            Dim SQLCmd As New OleDbCommand
            With SQLCmd
                .Connection = cn
                .CommandText = "SELECT ReminderDate FROM TblReminder "

                Dim rs As OleDbDataReader = .ExecuteReader()
                While rs.Read
                    ReminderDateTime = (rs(0).ToString)
                End While
            End With
        End If
        cn.Close()
        If CurrentDateTime = ReminderDateTime Then
            Dim SQLCmd As New OleDbCommand
            With SQLCmd
                Dim rs As OleDbDataReader = .ExecuteReader()
                While rs.Read
                    Dim NewStockRow As New DataGridViewRow()
                    NewStockRow.CreateCells(DgvCurrentReminders)
                    NewStockRow.SetValues({rs("ReminderID"), rs("CustomerName"), rs("DeviceInfo"), rs("RepairPrice"), rs("ReminderDate")})
                    NewStockRow.Tag = rs("ReminderID")
                    DgvCurrentReminders.Rows.Add(NewStockRow)
                End While
                rs.Close() 
            End With
        End If
        cn.Close()

    End Sub
End Class

CodePudding user response：

Disposable database objects like Connection should be declared locally in the method where they are used in a Using block.

You don't seem to have any idea of what the parts of an Sql string mean. The Select portion list the fields you want to retrieve. A star (*) in this clause means select all the fields. Here we are using a Where clause to filter the records. Only records where the field ReminderDate is greater than or equal to a parameter will be returned. This will only work if the data has been inserted properly as a DateTime.

The While loop keeps overwriting the value of ReminderDateTime on each iteration so only the last value returned by the reader will remain. Also, you are trying to force a String into a variable declared as a Date. Won't work.

Assuming the code could get beyond If CurrentDateTime = ReminderDateTime Then you would be working with a closed connection. Commands can't execute on a closed connection.

You also don't seem to have an idea how class objects work. Dim SQLCmd As New OleDbCommand Here you declare a new instance of the Command. You have no connection and no CommandText so it can't possibly be executed.

Take a look at the following code until it starts to make sense. Look up what Using blocks do. Look up the Load method of a DataTable to see what it does. Check out what a DataSource property has to offer.

Private Sub frmReminderInfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim dt = GetCurrentReminders()
    DgvCurrentReminders.DataSource = dt
End Sub

Private Function GetCurrentReminders() As DataTable
    Dim dt As New DataTable
    Using cn As New OleDbConnection("Your connection string"),
            SQLCmd As New OleDbCommand("SELECT * FROM TblReminder Where ReminderDate >= @Date", cn)
        SQLCmd.Parameters.Add("@Date", OleDbType.Date).Value = Now
        cn.Open()
        Using reader = SQLCmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function