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?

Answer 1

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