mysql，vb.net - 保存事务不起作用

Question

我不知道真正的问题是什么，因为没有报告错误。所以我想要这些代码做的是将事务记录插入数据库，但没有返回任何内容。以下是与此相关的代码:

MainForm

Private Sub PayButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PayButton.Click
        Dim payment As New Payment
        payment.Show()
        AddHandler payment.PaymentEvent, AddressOf paymentSuccess
        payment.PaymentAmount = TransactionTotal
    End Sub

Public Sub paymentSuccess(ByVal sender As Object, ByVal e As Payment.PaymentMadeEventArgs)
            mydbcon = New MySqlConnection
            mydbcon.ConnectionString = "server=localhost;userid=root;password=;database=sdudb"
            Dim reader As MySqlDataReader
            Try
                mydbcon.Open()
                Dim Query As String
                Query = "select * from inventory"
                COMMAND = New MySqlCommand(Query, mydbcon)
                reader = COMMAND.ExecuteReader()
                While reader.Read
                    Dim itId As Integer = reader.GetString("itemid")
                    Dim itName As String = reader.GetString("itemname")
                    If e.PaymentSuccess = True Then
                        paymentSuccessQuery(itId, itName)
                    End If
                End While
                reader.Close()
                mydbcon.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

 Private Sub paymentSuccessQuery(ByVal itemid, ByVal itemname)
        mydbcon = New MySqlConnection
        mydbcon.ConnectionString = "server=localhost;userid=root;password=;database=sdudb"
        Dim reader As MySqlDataReader
        Try
            mydbcon.Open()
            Dim Query As String
            Query = "INSERT INTO transaction (itemid, itemname) VALUES('" & itemid & "', '" & itemname & "')"
            COMMAND = New MySqlCommand(Query, mydbcon)
            reader = COMMAND.ExecuteReader()
            If reader.Read Then
                MessageBox.Show("Unable to save transaction!")
            Else
                MessageBox.Show("Transaction Saved!")
            End If
            reader.Close()
            mydbcon.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

Transactionform

Public Class Payment
    Public Delegate Sub PaymentMadeEvent(ByVal sender As Object, ByVal e As PaymentMadeEventArgs)
    Public Event PaymentEvent As PaymentMadeEvent

    Private _paymentAmount As Decimal
    Public Property PaymentAmount As Decimal
        Get
            Return _paymentAmount
        End Get
        Set(ByVal value As Decimal)
            _paymentAmount = value
            AmountBox.Text = String.Format("{0:c}", _paymentAmount)
        End Set
    End Property

    Private Sub PayButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PayButton.Click
        Dim total As Decimal = 0

        Try
            total = Decimal.Parse(AmountBox.Text.TrimStart("₱")) - Decimal.Parse(PaymentBox.Text)
        Catch
            MessageBox.Show("Error Occured, please enter a valid amount!")
            Return
        End Try

        If (total > 0) Then
            AmountBox.Text = total.ToString()
        Else
            MessageBox.Show("Please give " + String.Format("{0:c}", -total))
            RaiseEvent PaymentEvent(Me, New PaymentMadeEventArgs() With {.PaymentSuccess = True})
        End If

    End Sub

    Public Class PaymentMadeEventArgs
        Inherits EventArgs
        Private _paymentSuccess As Boolean
        Public Property PaymentSuccess As Boolean
            Get
                Return _paymentSuccess
            End Get
            Set(ByVal value As Boolean)
                _paymentSuccess = value
            End Set
        End Property
    End Class
End Class

Answer 1

ExecuteReader 执行命令(插入)，但它被构建为返回由 SELECT 命令提取的行。
在这种情况下，调用 Read 来发现 INSERT 是否成功是没有意义的。

您应该调用ExecuteNonQuery，捕获返回值，如果它不等于0，则说明您已经插入了记录。

Private Sub paymentSuccessQuery(ByVal itemid, ByVal itemname)
    Using mydbcon = New MySqlConnection("server=localhost;userid=root;password=;database=sdudb"
    Try
        mydbcon.Open()
        Dim Query As String
        Query = "INSERT INTO transaction (itemid, itemname) " & _ 
                "VALUES(@id, @name)"
        Using COMMAND = New MySqlCommand(Query, mydbcon)
           COMMAND.Parameters.Add("@id", MySqlDbType.VarChar).Value =  itemid 
           COMMAND.Parameters.Add("@name", MySqlDbType.VarChar).Value =  itemname
           Dim rowsAdded = COMMAND.ExecuteNonQuery()
           if rowsAdded = 0 Then
               MessageBox.Show("Unable to save transaction!")
           Else
              MessageBox.Show("Transaction Saved!")
           End If
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    End Using
End Sub

还请注意，我已经更改了您的代码，以在连接和命令等一次性对象周围使用适当的Using语句，最重要的是，我已经更改了您的查询以使用更安全的参数化查询方法(不确定ID 参数的 MySqlDbType，它似乎是一个整数，但在原始查询中，您将它像字符串一样放在单引号之间)