使用多个条件更新数据表

Question

我正在尝试使用以下列(名称、卡日期、打卡时间、打卡时间)更新ms-access表(Table2)

我需要我的sequel命令在名称、卡片日期、打卡时间(最大值)和打卡时间(为空)时更新打卡时间。

    Dim myconnection As OleDbConnection = New OleDbConnection
    Dim Update As String = "UPDATE Table2 SET [Clock Out Time]=" & TimeLbl.Text & " WHERE [Name]=" & EmployeeLbl.Text & ", AND [Card Date]= (select MAX(Card Date), AND [Clock In Time]= (select MAX(Clock In Time)), AND [Clock Out Time]=?"
    Dim dbsource As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\Docs\PRODUCTION\Shop Manager\Shop_Manager\Shop_Manager\Database2.accdb;")
    Dim conn = New OleDbConnection(dbsource)
    Dim cmd As New OleDbCommand(Update, conn)

    Try
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
    Catch ex As OleDbException
        MessageBox.Show("Error Clock Out Data from Table2")
    End Try

Answer 1

使用以下Update语句：

Dim Update As String = "UPDATE Table2 SET [Clock Out Time]='" & TimeLbl.Text & "' WHERE [Name]='" & EmployeeLbl.Text & "' AND [Card Date]in (select MAX([Card Date]) from Table2) AND [Clock In Time]in (select MAX([Clock In Time]) from Table2) AND [Clock Out Time] is null"

顺便说一句，在你的列名中尽量避免空格。这不是一个好的表示法，而且容易出错。

更新：如果您删除了字段名称中的空格，那么您应该有一个如下所示的表：

-----------------------------------------------------------------------------------------------------
|        ID         |       Name        |     CardDate      |   ClockInTime   |   ClockOutTime    |
-----------------------------------------------------------------------------------------------------

然后，update语句如下所示：

Dim Update As String = "UPDATE Table2 SET [ClockOutTime]='" & TimeLbl.Text & "' WHERE [Name]='" & EmployeeLbl.Text & "' AND [CardDate]in (select MAX([CardDate]) from Table2) AND [ClockInTime]in (select MAX([ClockInTime]) from Table2) AND [ClockOutTime] is null"