Trying to delete a record from my access database in visual basic studio 2010

Question

Private Function CreatePlayerAdapter(ByVal playerDBconnection As OleDbConnection) As OleDbDataAdapter

// Initiating instances for the function

    Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter()
    Dim myCommand As OleDbCommand
    Dim parameter As OleDbParameter

// establishing the string to tell where to delete record from and how to find the record i want. // PlayerIDTextBox.Text is a text on a form that is populated from the database after selecting a list of name (this works correctly) // connection is already open and is directed to correct place

    Dim sql As String = "DELETE * FROM Players WHERE ID ='" & CInt(PlayerIDTextBox.Text) & "'"
    myCommand = New OleDbCommand(sql, playerDBconnection)

    parameter = myCommand.Parameters.Add("ID", OleDbType.Char, 3, "ID")
    parameter.SourceVersion = DataRowVersion.Original

    dataAdapter.DeleteCommand = myCommand

    Return dataAdapter
End Function

// i call this function after executing a button click. //ListPlayerComboBox.Text is populated with the names and needs it a name to fill PlayerIDTextBox.Text(works correctly)

Private Sub RemovePlayerButton_Click(sender As System.Object, e As System.EventArgs) Handles RemovePlayerButton.Click

        If ListPlayerComboBox.Text = " " Then
            MsgBox("Please Select a Player.")
    Else


        Me.CreatePlayerAdapter(playerDBConnection)

        End If


End Sub

// no errors occur. However, nothing is done in the database. help please?

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

Notes:

1)Never leave your OleDbConnection Open. Only allow it to be opened when you actually need it. This will save you from a lot of headaches later on. The reasons why can be found on following stackoverflow question.

2) There is no reason to return an OleDbDataAdapter if you don't intend on using it.

3) Use your parameters correctly : see below example2

4) Keep in mind that there are some restricted keywords in Access. Luckely for you ID isn't one. The restrictedKeywords can be found here: Keywords

I'm probably missing some further points here. Anyone should be free to add em.

Why not adjust your Function CreatePlayerAdapter to the following:

1) Without parameters

Private Sub CreatePlayerAdapter(ByVal playerDBconnection As OleDbConnection) 

    Dim myCommand As OleDbCommand

    Dim sql As String = "DELETE * FROM Players WHERE ID =" & CInt(PlayerIDTextBox.Text) 
    myCommand = New OleDbCommand(sql, playerDBconnection)

    playerDBconnection.Open()
    myCommand.ExecuteNonQuery()
    playerDBconnection.Close()
End Sub

2) With parameters

Private Sub CreatePlayerAdapter(ByVal playerDBconnection As OleDbConnection) 

    Dim myCommand As OleDbCommand

    Dim sql As String = "DELETE * FROM Players WHERE ID = @playerId"
    myCommand = New OleDbCommand(sql, playerDBconnection)


    Dim param As New OleDb.OleDbParameter(@playerId", CInt(PlayerIDTextBox.Text))
    myCommand.Add(param)

    playerDBconnection.Open()
    myCommand.ExecuteNonQuery()
    playerDBconnection.Close()
End Sub

The method ExecuteNonQuery executes the query passed to the command on the specified OleDbConnection and returns the number of rows affected. More info Here

edited May 23 '17 at 11:50

Community

1
1

answered Dec 02 '14 at 08:24

User999999

2,500
7
37
63

Well, you have good points here, but why forgetting the (I wish to be) mandatory "USE A PARAMETERIZED QUERY" one? – Steve Dec 02 '14 at 08:35
@Steve was adding it. give me a second. So much wrong here – User999999 Dec 02 '14 at 08:36
Thanks for your input. I edited my code to utilize no parameters and looks like number 1 in your reply, but now i get a "data type mismatch in criteria expression" error. Any idea? – Chris Robinson Dec 02 '14 at 13:16
Is `ID`in your database if of type Integer (Number) or String(Text)? – User999999 Dec 02 '14 at 13:26
@N55PEC ID is of type Integer(Number). It is AutoNumber though. I researched and saw removing the apostrophes from the sql string works, but it still did not work. Again, my code looks just like number 1 and your comment above. – Chris Robinson Dec 02 '14 at 14:46
`data type mismatch in criteria expression`usually means that you're comparing to the wrong type of data. Like in this case your trying to compare a string with an Integer. I've modified the example in the first solution to exclude the `'`. If you still encouter a problem, feel free post a comment. – User999999 Dec 02 '14 at 14:57
@N55PEC ok, the data type mismatch error went away. There are no errors at all and it still will not delete from the database. I'm kind of stuck. – Chris Robinson Dec 02 '14 at 15:11
That can only signify 1 thing: The value contained in `PlayerIDTextBox.Text` has no corresponding `ID` in the database. – User999999 Dec 02 '14 at 15:20

Trying to delete a record from my access database in visual basic studio 2010

1 Answers1