updating or removing newly added record does not seem to work

[rageke]

Hi there,

for a serious amount of time i've been struggling with this same issue, I did find some answers but non sufficiant. Maybe someone could lend me a hand?

Allow me to illustrate my situation;

I'm pretty new to VB.net, a whole lot is left for me to learn. Most of the time I figure out any problems I encounter by myself. This time, no success... :(

I am trying to create a basic tournament management application (a basic one). I am using an external access db to store data.
In my table "tblPlayers" i managed to add players, edit en delete them form the database. Problem is when trying to edit or delete a newly added record. When, eg, I add a new player and try to edit this players name i keep getting a cncurrency violation. 0 of 1 row affected.
Other players, already added before can be edited or removed without any problem however..

problem is situated in the update command on this line -> dataadaptorpeople.Update(datasetpeople, "people")

I'll give a piece of my code and hope anyone could help me find a solution:

Dim connectie As New OleDb.OleDbConnection
    Dim datasetpeople As New DataSet
    Dim sql As String
    Dim dataadaptorpeople As OleDb.OleDbDataAdapter

    'FILL LISTBOX
    Public Sub fill()

        Dim myDataTable As DataTable = datasetpeople.Tables(0)
        Dim tempRow As DataRow
        lbPlayers.Items.Clear()
        For Each tempRow In myDataTable.Rows
        lbPlayers.Items.Add((tempRow("playerName") & " " & tempRow("playerFirstName")))
        Next

    End Sub

    ' LOAD DATABASE
    Private Sub Loaddb()
        connectie.Close()
        connectie.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source =   
        c:\XXX\people.mdb;"
        connectie.Open()
        sql = "SELECT * FROM tblPlayers"
        dataadaptorpeople = New OleDb.OleDbDataAdapter(sql, connectie)
        dataadaptorpeople.Fill(datasetpeople, "people")
        connectie.Close()
    End Sub


    'FORM LOAD
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'LOAD DB
        Loaddb()

    End Sub


     Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbPlayers.SelectedIndexChanged
        If lbPlayers.SelectedIndex < 0 Then
            Exit Sub
        Else
            txtName.Text = datasetpeople.Tables(0).Rows(lbPlayers.SelectedIndex).Item("playerName").ToString
            txtFirstName.Text = datasetpeople.Tables(0).Rows(lbPlayers.SelectedIndex).Item("playerFirstName").ToString
            txtNickName.Text = datasetpeople.Tables(0).Rows(lbPlayers.SelectedIndex).Item("playerNickName").ToString
            txtRanking.Text = datasetpeople.Tables(0).Rows(lbPlayers.SelectedIndex).Item("playerRanking").ToString
            cmbCountry.Text = datasetpeople.Tables(0).Rows(lbPlayers.SelectedIndex).Item("playerCountry").ToString
        End If
    End Sub

 

    'EDIT RECORD

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If lbPlayers.SelectedIndex < 0 Then
            Exit Sub
        Else
            Dim builder As New OleDb.OleDbCommandBuilder(dataadaptorpeople)

            datasetpeople.Tables("people").Rows(lbPlayers.SelectedIndex).Item("playerName") = txtName.Text
            datasetpeople.Tables("people").Rows(lbPlayers.SelectedIndex).Item("playerFirstName") = txtFirstName.Text
            datasetpeople.Tables("people").Rows(lbPlayers.SelectedIndex).Item("playerNickName") = txtNickName.Text
            datasetpeople.Tables("people").Rows(lbPlayers.SelectedIndex).Item("playerRanking") = txtRanking.Text
            datasetpeople.Tables("people").Rows(lbPlayers.SelectedIndex).Item("playerCountry") = cmbCountry.Text

            'UPDATE

            dataadaptorpeople.Update(datasetpeople, "people")

            'REMOVE ITEMS LISTBOX
            lbPlayers.Items.Clear()

            'REFILL LISTBOX WITH NEW VALUES
            fill()
        End If
    End Sub

    'NEW RECORD
    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click


        'CLEAR FIELDS
        txtFirstName.Clear()
        txtName.Clear()
        txtNickName.Clear()
        txtRanking.Clear()
        cmbCountry.SelectedItem = "Belgium"
        playerFlag.Image = My.Resources.Belgium
        increment = 0

    End Sub

    'COMMIT NEW RECORD
    Private Sub btnAddPlayer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddPlayer.Click


            Dim builder As New OleDb.OleDbCommandBuilder(dataadaptorpeople)
            Dim nieuwerij As DataRow

            nieuwerij = datasetpeople.Tables("people").NewRow()

            nieuwerij.Item(1) = txtName.Text
            nieuwerij.Item(2) = txtFirstName.Text
            nieuwerij.Item(3) = txtNickName.Text
            nieuwerij.Item(4) = txtRanking.Text
            nieuwerij.Item(5) = cmbCountry.Text

            datasetpeople.Tables("people").Rows.Add(nieuwerij)
            dataadaptorpeople.Update(datasetpeople, "people")

            fill()
            Loaddb()
        End If
    End Sub

    'DELETE RECORD
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

        'IF NO SELECTION -> CANCEL ACTION
        If lbPlayers.SelectedIndex < 0 Then
            Exit Sub
        Else

            'REMOVE SELECTED INDEX OF TOURNEY DATASET
            Dim builder As New OleDb.OleDbCommandBuilder(dataadaptorpeople)
            datasetpeople.Tables("people").Rows(lbPlayers.SelectedIndex).Delete()

            'UPDATE DATASET AND DATABASE
            dataadaptorpeople.Update(datasetpeople, "people")

            'REMOVE ITEMS LISTBOX
            lbPlayers.Items.Clear()

            'REFILL LISTBOX WITH NEW VALUES
            fill()

            'CLEAR TEXTBOXES ZODAT OOK HIER GEEN SPOOR MEER IS VAN HET VERWIJDERDE ITEM
            txtFirstName.Clear()
            txtName.Clear()
            txtNickName.Clear()
            txtRanking.Clear()
            cmbCountry.SelectedItem = "Belgium"
            playerFlag.Image = My.Resources.Belgium
        End If
    End Sub