每次显示或加载表单时，使用当前日期填充一列

Question

我想用当前日期填充列中的所有条目，以便可以将其与DateofTest列进行比较，并确定自上次测试以来有多少天。我想我可以计算出天数，但我不知道如何用当前日期填充该列。

 Private Sub Reports_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
    mySql = "Update Studentdata, SET currentDate = Date()"
    ConnDB()
    myCommand = New OleDbCommand(mySql, myConnection)

End Sub

Answer 1

您实际上是在执行update命令吗？试试这个：

    Dim myConnection As New OleDb.OleDbConnection()  'Complete as needed
    Dim mySql As String = "Update Studentdata SET currentDate = Date()"
    Using cmd As New OleDb.OleDbCommand(mySql, myConnection)
        Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ERROR UPDATING")
        Finally
            cmd.Connection.Close()
        End Try
    End Using

或者尝试从代码中填充日期，并将其作为参数提供，如下所示：

     Using cmd As New OleDb.OleDbCommand()
        cmd.Connection = myConnection
        Try
            cmd.Connection.Open()
            cmd.CommandText = "Update Studentdata SET currentDate = @Date"
            cmd.Parameters.Add(New OleDb.OleDbParameter("@Date", DateTime.Now))
            cmd.ExecuteNonQuery()
        Catch ex1 As Exception
            MessageBox.Show(ex1.Message, "ERROR UPDATING")
        Finally
            cmd.Connection.Close()
        End Try
    End Using

Answer 2

就我记忆中的access数据库而言，对于包含毫秒的日期可能会有一些问题，所以你可以这样做(同时删除"SET“前的逗号)：

Dim dateWithoutMiliseconds as DateTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day)
mySql = String.Format("Update Studentdata SET currentDate = {0}", dateWithoutMiliseconds)