vb.net - 带 Access 数据库的VB更新命令

Question

 Function UpdateTableRow()
        sqlLink.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\will\Documents\Computing\ComputingProjectDatabase.accdb';"

    Try
        Dim sqlOrder As New OleDbCommand
        If sqlLink.State = ConnectionState.Closed Then
            sqlLink.Open()
        End If

        ' Creating the command and its parameter here before entering the loop to avoid a continue'
        ' create and destroy pattern for the OleDbCommand'

        sqlOrder.CommandText = "UPDATE StockSystem SET [Stock Price] =?, [Stock Size] =?, [Stock Quantity] =?, [Stock Category] =?, WHERE [Stock ID] =?"
        sqlOrder.Connection = sqlLink

        sqlOrder.Parameters.AddWithValue("@StockCategory", 0)
        sqlOrder.Parameters.AddWithValue("@StockQuantity", 0)
        sqlOrder.Parameters.AddWithValue("@StockSize", 0)
        sqlOrder.Parameters.AddWithValue("@StockPrice", 0)
        sqlOrder.Parameters.AddWithValue("@row", 0)

        Dim rows = DataGridView1.Rows

        For Each row In rows

            sqlOrder.Parameters("@row").Value = row.Cells(0).Value
            sqlOrder.Parameters("@StockPrice").Value = row.Cells(1).Value
            sqlOrder.Parameters("@StockSize").Value = row.Cells(2).Value
            sqlOrder.Parameters("@StockQuantity").Value = row.Cells(3).Value
            sqlOrder.Parameters("@StockCategory").Value = row.Cells(4).Value

            sqlOrder.ExecuteNonQuery()

        Next

        MsgBox("Data Updated.")

        'Problem with the update parameters, works for DELETE FUNCTION, needs to be changed to fit an UPDATE FUNCTION

    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        sqlLink.Close()

    End Try
    Return DataGridView1.SelectedRows
    DataGridView1.Refresh()
End Function

嘿，如您所见，我在这里有很多代码。只是它不是100％有效。我不断被告知 Syntax Error in UPDATE statement。现在，我非常确定update语句是正确的，因为如果没有参数，则没有参数就可以(如果没有参数就根本不起作用)。我已经坚持了大约一个星期，所以任何帮助都是很棒的。

我得到的第二个错误是，因为我正在使用Access，所以在表的底部有一个“空白”行。我当时以为(行-1)类似，但VB 2010根本不喜欢。

正如我所说，任何帮助都将是非常棒的。

Answer 1

您已在,之前添加了WHERE。这是UPDATE语句中语法错误的原因

尝试这个

sqlOrder.CommandText = "UPDATE StockSystem SET [Stock Price] =?, [Stock Size] =?, [Stock Quantity] =?, [Stock Category] =? WHERE [Stock ID] =?"

另一方面，我不确定您的意思。这空白行？

它只是在那里允许用户插入新行。它不是空行，因此 不需要使用 rowindex - 1引用行

更新:试图更正您的代码。我会这样想:

Try

    If sqlLink.State = ConnectionState.Closed Then
        sqlLink.Open()
    End If

    Dim rows = DataGridView1.Rows

    For Each row In rows

        Dim sqlOrder As New OleDbCommand("UPDATE StockSystem SET [Stock Price] =?, [Stock Size] =?, [Stock Quantity] =?, [Stock Category] =? WHERE [Stock ID] =?", sqlLink)            

        sqlOrder.Parameters.AddWithValue("@StockPrice", row.Cells(1).Value)
        sqlOrder.Parameters.AddWithValue("@StockSize", row.Cells(2).Value)
        sqlOrder.Parameters.AddWithValue("@StockQuantity", row.Cells(3).Value)
        sqlOrder.Parameters.AddWithValue("@StockCategory", row.Cells(4).Value)
        sqlOrder.Parameters.AddWithValue("@row", row.Cells(0).Value)

        sqlOrder.ExecuteNonQuery()

    Next

    MsgBox("Data Updated.")

Catch ex As Exception
    MsgBox(ex.Message)
Finally
    sqlLink.Close()
End Try