mysql - 参数化的MySQL插入命令不起作用

我试图自己对我的代码进行参数化，我认为我可能已经破坏了它。现在，我可以获取应用程序以将记录插入到数据库中。谁能浏览这段代码并告诉我我所缺少的内容？

编辑：我修改了我的代码以按建议删除dbCmd.Dispose（）和dbConn.Close（）方法。现在，VB在dbCmd.ExecuteNonQuery（）行的debug期间引发以下异常：


  列数与第1行的值数不匹配


这是我的代码：

    Private Sub addCard()
    Dim ConnectionString As String = String.Format("Server={0};Port={1};Uid={2};Password={3};Database=accounting", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password)
    Using dbConn As New MySqlConnection(ConnectionString)
        dbConn.Open()
        'PERFORM CARD ENCRYPTION
        Call encryptCard()

        'PERFORM DATABASE SUBMISSION
        Dim dbQuery As String = "INSERT INTO cc_master (ccType, cardholderFirstname, cardholderLastname, cardholderSalutation, ccLocation, " & _
                                 "ccNumber, ccExpireMonth, ccExpireYear, ccZipcode, ccCode, ccAuthorizedUseStart, ccAuthorizedUseEnd, " & _
                                 "dateAdded, addedBy, customer_accountNumber)" & _
                                 "VALUES(@ccType, @cardholderFirstname, @cardholderLastname, @cardholderSalutation, @ccLocation, " & _
                                 "@ccNumber, @ccExpireMonth, @ccExpireYear, @ccZipcode, @ccCode, @ccAuthorizedUseStart, @ccAuthorizedUseEnd " & _
                                 "@dateAdded, @addedBy, @accountNumber)"

        Using dbCmd As New MySqlCommand
            With dbCmd
                .Connection = dbConn
                .CommandType = CommandType.Text
                .CommandText = dbQuery
                .Parameters.AddWithValue("@ccType", ComboBoxCardType.Text)
                .Parameters.AddWithValue("@cardholderFirstname", TextBoxFirstName.Text)
                .Parameters.AddWithValue("@cardholderLastname", TextBoxLastName.Text)
                .Parameters.AddWithValue("@cardholderSalutation", ComboBoxSalutation.Text)
                .Parameters.AddWithValue("@ccLocation", TextBoxLocation.Text)
                .Parameters.AddWithValue("@ccNumber", encryptedCard)
                .Parameters.AddWithValue("@ccExpireMonth", TextBoxExpireMonth.Text)
                .Parameters.AddWithValue("@ccExpireYear", TextBoxExpireYear.Text)
                .Parameters.AddWithValue("@ccZipcode", TextBoxZipCode.Text)
                .Parameters.AddWithValue("@ccCode", TextBoxCVV2.Text)
                .Parameters.AddWithValue("@ccAuthorizedUseStart", Format(DateTimePickerStartDate.Value, "yyyy-MM-dd HH:MM:ss"))
                .Parameters.AddWithValue("@ccAuthorizedUseEnd", Format(DateTimePickerEndDate.Value, "yyyy-MM-dd HH:MM:ss"))
                .Parameters.AddWithValue("@dateAdded", Format(DateTime.Now, "yyyy-MM-dd HH:MM:ss"))
                .Parameters.AddWithValue("@addedBy", FormLogin.TextBoxUsername.Text)
                .Parameters.AddWithValue("@accountNumber", TextBoxAccount.Text)
            End With
            Try
                Dim affectedRow As Integer
                affectedRow = dbCmd.ExecuteNonQuery()
                If affectedRow > 0 Then
                    MsgBox("Credit/Debit Card Information Saved SUCCESSFULLY!", MsgBoxStyle.Information, "RECORD SAVED")
                    ButtonReset.PerformClick()
                Else
                    MsgBox("Payment Card Was Not Added!", MsgBoxStyle.Critical, "ATTENTION")
                End If
            Catch ex As Exception
                MessageBox.Show("A DATABASE ERROR HAS OCCURED" & vbCrLf & vbCrLf & ex.Message & vbCrLf & _
                                    vbCrLf + "Please report this to the IT/Systems Helpdesk at Ext 131.")
            End Try
            dbCmd.Dispose()
        End Using
    End Using
    dbConn.Close()

End Sub

修改后的代码-现在抛出异常：

    Private Sub addCard()
    Dim ConnectionString As String = String.Format("Server={0};Port={1};Uid={2};Password={3};Database=accounting", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password)
    Using dbConn As New MySqlConnection(ConnectionString)
        'PERFORM CARD ENCRYPTION
        Call encryptCard()

        'PERFORM DATABASE SUBMISSION
        Dim dbQuery As String = "INSERT INTO cc_master (ccType, cardholderFirstname, cardholderLastname, cardholderSalutation, ccLocation, " & _
                                 "ccNumber, ccExpireMonth, ccExpireYear, ccZipcode, ccCode, ccAuthorizedUseStart, ccAuthorizedUseEnd, " & _
                                 "dateAdded, addedBy, customer_accountNumber)" & _
                                 "VALUES(@ccType, @cardholderFirstname, @cardholderLastname, @cardholderSalutation, @ccLocation, " & _
                                 "@ccNumber, @ccExpireMonth, @ccExpireYear, @ccZipcode, @ccCode, @ccAuthorizedUseStart, @ccAuthorizedUseEnd " & _
                                 "@dateAdded, @addedBy, @accountNumber)"

        Using dbCmd As New MySqlCommand
            With dbCmd
                .Connection = dbConn
                .CommandType = CommandType.Text
                .CommandText = dbQuery
                .Parameters.AddWithValue("@ccType", ComboBoxCardType.Text)
                .Parameters.AddWithValue("@cardholderFirstname", TextBoxFirstName.Text)
                .Parameters.AddWithValue("@cardholderLastname", TextBoxLastName.Text)
                .Parameters.AddWithValue("@cardholderSalutation", ComboBoxSalutation.Text)
                .Parameters.AddWithValue("@ccLocation", TextBoxLocation.Text)
                .Parameters.AddWithValue("@ccNumber", encryptedCard)
                .Parameters.AddWithValue("@ccExpireMonth", TextBoxExpireMonth.Text)
                .Parameters.AddWithValue("@ccExpireYear", TextBoxExpireYear.Text)
                .Parameters.AddWithValue("@ccZipcode", TextBoxZipCode.Text)
                .Parameters.AddWithValue("@ccCode", TextBoxCVV2.Text)
                .Parameters.AddWithValue("@ccAuthorizedUseStart", Format(DateTimePickerStartDate.Value, "yyyy-MM-dd HH:MM:ss"))
                .Parameters.AddWithValue("@ccAuthorizedUseEnd", Format(DateTimePickerEndDate.Value, "yyyy-MM-dd HH:MM:ss"))
                .Parameters.AddWithValue("@dateAdded", Format(DateTime.Now, "yyyy-MM-dd HH:MM:ss"))
                .Parameters.AddWithValue("@addedBy", FormLogin.TextBoxUsername.Text)
                .Parameters.AddWithValue("@accountNumber", TextBoxAccount.Text)
            End With
            Try
                dbConn.Open()
                dbCmd.ExecuteNonQuery()
                Dim affectedRow As Integer
                affectedRow = dbCmd.ExecuteNonQuery()
                If affectedRow > 0 Then
                    MsgBox("Credit/Debit Card Information Saved SUCCESSFULLY!", MsgBoxStyle.Information, "RECORD SAVED")
                    ButtonReset.PerformClick()
                Else
                    MsgBox("Payment Card Was Not Added!", MsgBoxStyle.Critical, "ATTENTION")
                End If
            Catch ex As Exception
                MessageBox.Show("A DATABASE ERROR HAS OCCURED" & vbCrLf & vbCrLf & ex.Message & vbCrLf & _
                                    vbCrLf + "Please report this to the IT/Systems Helpdesk at Ext 131.")
            End Try
        End Using
    End Using
End Sub

最佳答案

我想出了解决问题的办法。我在查询中@ccAuthorizedUseEnd的末尾缺少逗号。我添加了它和中提琴，错误消失了，查询现在可以正常工作了。

谢谢。

关于mysql - 参数化的MySQL插入命令不起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14024549/