VB.NET无法更新MSACCESS数据库记录

Question

 Private Sub btnPurchaseSave_Click(sender As Object, e As EventArgs) Handles btnPurchaseSave.Click
    Dim InvNo As Integer
    Dim ItmName As String
    Dim ItmType As String
    Dim ItmQuantity As Double
    Dim ItmPurPrice As Double
    Dim itmTotalCost As Double
    Dim DateFormat = "dd/MM/yyyy"
    Dim InvTotal As Double
    InvNo = CInt(txtInvoiceNo.Text)
    InvTotal = TotalCost()
    Dim InvDate = Format(CDate(txtInvoiceDate.Text), DateFormat)
    ConnectDatabse()
    If blnPurchNew = True Then
        myConnection.Open()
        ObjCommand.CommandText = "Insert into Invoice (Invoice_No,Invoice_Date,Supplier_Name,Invoice_Total) values (@Invoice_No, @Invoice_Date,@Supplier_Name,@Invoice_Name)"
        ObjCommand.Connection = myConnection
        ObjCommand.Parameters.AddWithValue("Invoice_No", InvNo)
        ObjCommand.Parameters.AddWithValue("Invoice_Date", InvDate)
        ObjCommand.Parameters.AddWithValue("Supplier_Name", txtSupplierName.Text)
        ObjCommand.Parameters.AddWithValue("Invoice_Total", InvTotal)
        ObjCommand.ExecuteNonQuery()
        ObjCommand.Dispose()
        ' Try
        For i = 0 To DGPurchase.Rows.Count - 2
            ObjCommand.CommandText = "Insert into Item_Invoice (Invoice_No,Item_Name,Item_Type,Item_Quantity,Item_Total_Cost,Item_Purchase_Price) values (@Invoice_No,@Item_Name,@item_Type, @Item_Quantity,@Item_Total_Cost,@Item_Purchase_Price)"
            ObjCommand.Connection = myConnection
            ItmName = DGPurchase.Rows(i).Cells(0).Value.ToString
            ItmType = DGPurchase.Rows(i).Cells(1).Value.ToString
            ItmQuantity = CDbl(DGPurchase.Rows(i).Cells(2).Value)
            itmTotalCost = CDbl(DGPurchase.Rows(i).Cells(3).Value)
            ItmPurPrice = CDbl(DGPurchase.Rows(i).Cells(4).Value)
            ObjCommand.Parameters.AddWithValue("Invoice_No", InvNo)
            ObjCommand.Parameters.AddWithValue("Item_Name", ItmName)
            ObjCommand.Parameters.AddWithValue("Item_Type", ItmType)
            ObjCommand.Parameters.AddWithValue("Item_Quantity", ItmQuantity)
            ObjCommand.Parameters.AddWithValue("Item_Total_Cost", itmTotalCost)
            ObjCommand.Parameters.AddWithValue("Item_Purchase_Price", ItmPurPrice)
            ObjCommand.ExecuteNonQuery()
            ObjCommand.Dispose()
        Next i
        ' Catch ex As Exception
        'MsgBox(ex.Message)
        ' End Try
        MessageBox.Show("تم حفظ فاتوة المشتريات")
        lblTotalCost.Text = " الاجمالي : "
        clear()
        blnPurchNew = False
    Else
        If blnPurchModify = True Then
            myConnection.Open()
            ObjCommand.CommandText = "UPDATE Invoice SET Invoice_Date=@Invoice_Date,Supplier_Name=@Supplier_Name,Invoice_Total=@Invoice_Total WHERE Invoice_No=" & CInt(txtInvoiceNo.Text) & "  "
            ObjCommand.Connection = myConnection
            ObjCommand.Parameters.AddWithValue("Invoice_Date", InvDate)
            ObjCommand.Parameters.AddWithValue("Supplier_Name", txtSupplierName.Text)
            ObjCommand.Parameters.AddWithValue("Invoice_Total", InvTotal)
            ObjCommand.ExecuteNonQuery()
            ObjCommand.Dispose()
            ' Try
            For i = 0 To DGPurchase.Rows.Count - 2
                ' ObjCommand.CommandText = "UPDATE Item_Invoice SET Item_Name=@Item_Name,item_Type=@item_Type, Item_Quantity=@Item_Quantity,Item_Total_Cost=@Item_Total_Cost,Item_Purchase_Price=@Item_Purchase_Price WHERE Invoice_No=@Invoice_NO"
                ObjCommand.Connection = myConnection
                ObjCommand.CommandText = "Update Item_Invoice Set Item_Name=@Item_Name Where Invoice_No=@Invoice_No"
                ItmName = DGPurchase.Rows(i).Cells(0).Value.ToString
                ItmType = DGPurchase.Rows(i).Cells(1).Value.ToString
                ItmQuantity = CDbl(DGPurchase.Rows(i).Cells(2).Value)
                itmTotalCost = CDbl(DGPurchase.Rows(i).Cells(3).Value)
                ItmPurPrice = CDbl(DGPurchase.Rows(i).Cells(4).Value)
                ObjCommand.Parameters.AddWithValue("Invoice_No", InvNo)
                ObjCommand.Parameters.AddWithValue("Item_Name", ItmName)
                ObjCommand.Parameters.AddWithValue("Item_Type", ItmType)
                ObjCommand.Parameters.AddWithValue("Item_Quantity", ItmQuantity)
                ObjCommand.Parameters.AddWithValue("Item_Total_Cost", itmTotalCost)
                ObjCommand.Parameters.AddWithValue("Item_Purchase_Price", ItmPurPrice)
                ObjCommand.ExecuteNonQuery()
                ObjCommand.Dispose()
            Next i

            MessageBox.Show("تم تعديل فاتوة المشتريات")
            lblTotalCost.Text = " الاجمالي : "
            clear()
            blnPurchModify = False
        End If
    End If
    myConnection.Close()
    myConnection.Dispose()
    ObjCommand.Dispose()

End Sub

我能够保存表并更新第一个"发票"，但我不知道与项目发票更新相关的第二部分有什么问题。我没有错误..我收到了成功更新的消息，而表数据库中没有数据更改。

对不起，我想清楚地表明。

事先感谢您的帮助。

Answer 1

更新中的问题是参数的定义顺序。
OLEDB不查看参数的名称，而是按添加参数添加到集合的顺序。
在更新SQL文本中，您首先添加Invoice_No参数，然后添加Item_name。但是在查询中，第一个参数占位符用于Item_Name，而Invoice_No的最后一个参数占位符。

添加参数时，您应该遵循占位符订单。

For i = 0 To DGPurchase.Rows.Count - 2
    .....
    ObjCommand.Connection = myConnection
    ObjCommand.CommandText = "Update Item_Invoice 
          Set Item_Name=@Item_Name 
          Where Invoice_No=@Invoice_No"
    .... 
    ObjCommand.Parameters.AddWithValue("Item_Name", ItmName)
    ' the Invoice_No should be the last parameter added not the first
    ObjCommand.Parameters.AddWithValue("Invoice_No", InvNo)
    .....
    ObjCommand.ExecuteNonQuery()

说，我重复我的建议。您始终使用相同的OLEDBCOMMAND，然后在使用它之前不要将其处置。
另外，您应该清除每个执行字节之间的参数集合。否则，您将继续添加参数，并且可能最终会在数据库中插入错误的值或复制记录。

 Private Sub btnPurchaseSave_Click(sender As Object, e As EventArgs) Handles btnPurchaseSave.Click
    ....
    ConnectDatabse()
    If blnPurchNew = True Then
        myConnection.Open()
        .... 
        ObjCommand.ExecuteNonQuery()
        ' Do not destroy the objCommand
        ' ObjCommand.Dispose()
        ' Try
        For i = 0 To DGPurchase.Rows.Count - 2
            ObjCommand.Parameters.Clear()
            ObjCommand.CommandText = .....
            ObjCommand.ExecuteNonQuery()
            ' No destroy 
            ' ObjCommand.Dispose()
        Next i
        ....
    Else
        If blnPurchModify = True Then
            myConnection.Open()
            ObjCommand.CommandText = "UPDATE ...."
            ....
            ObjCommand.ExecuteNonQuery()
            ' ObjCommand.Dispose()
            For i = 0 To DGPurchase.Rows.Count - 2
                ObjCommand.Parameters.Clear()
                ObjCommand.CommandText = "UPDATE Item_Invoice ...."
                ....
                ObjCommand.ExecuteNonQuery()
            Next i
            ....
        End If
    End If
    myConnection.Close()
    myConnection.Dispose()
    ObjCommand.Dispose()
End Sub