CODEWITHSUNDEEP
mysqlvb.net
Kismet Agbasi
Kismet Agbasi

Reputation: 567

Parametized MySQL Insert Command Isn't Working

I tried to parametize my code on my own and I think I may have broken it. Now I can get my application to insert records into my database. Can anyone look through this code and tell me what I'm missing?

EDIT: I modified my code to remove the dbCmd.Dispose() and dbConn.Close() methods as suggested. Now VB is throwing the following exception during debug @ the dbCmd.ExecuteNonQuery() line:

Column count doesn't match value count at row 1

HERE'S MY CODE:

    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

MODIFIED CODE - NOW THROWING EXCEPTION:

    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

Upvotes: 0

Views: 370

Answers (1)

Kismet Agbasi
Kismet Agbasi

Reputation: 567

I figured out the solution to the problem. I was missing a comma at the end of @ccAuthorizedUseEnd in the query. I added it and viola, the error is gone and the query is working now.

Thanks.

Upvotes: 1

Related Questions