[RESOLVED] Using a Databound DGV Find and update a record or insert it

I have a data-bound DataGridView with a BindingSource and a TableAdapter all together it makes two column leaderboard linking back to an Access DB

I need to be able to check whether a particular record exists and, if it does exist, then update it with new info. If it does not exist then add it.

My code so far after many permutations and still not working is:

Code:

---

  Private Sub UpdateLeaderboard(ByVal jName As String, jWins As Integer)
        Dim test As Boolean = False
        'Check whether the name already exists
        Dim jRow As DataRow = SsrDataSet.JockeyLeaderboard.NewRow
        For Each row In JockeyLeaderboardDataGridView.Rows
            If jName = row.Cells(0).Value Then
                jWins = row.Cells(1).Value
                test = True
                jWins += 1
                Exit For
            End If
        Next

        If test = True Then
            'update the recoed with new info

        End If
        If test = False Then
            'the name is not present so add it
            Dim MyNewRow As DataRow
            MyNewRow = SsrDataSet.JockeyLeaderboard.NewRow
            With MyNewRow
                .Item(0) = jName
                .Item(1) = jWins
            End With
            SsrDataSet.JockeyLeaderboard.Rows.Add(MyNewRow)
            SsrDataSet.JockeyLeaderboard.AcceptChanges()
        End If
        Try
            Me.Validate()
            Me.JockeyLeaderboardBindingSource.EndEdit()
            Me.JockeyLeaderboardTableAdapter.Update(Me.SsrDataSet.JockeyLeaderboard)
            'MsgBox("Update successful")
            My.Computer.Audio.Play(My.Resources.notify, AudioPlayMode.Background)
        Catch ex As Exception
            MsgBox(ex)
        End Try
    End Sub

---

At present it will add a new record to the DGV but not save it to the database and I haven't found a reliable way of updating the dataset/table


Please point out the error of code:)

Thanks

One thing you need to do is remove the AcceptChanges. If you call that then there will be nothing changed when you call the Update method. The Update method will basically call AcceptChanges after it updates the database.

To elaborate on what wes4dbt posted, each DataRow in a DataTable has a RowState property that determines how it is treated when you save your changes.

When you call Fill on your table adapter, what happens internally is that a data reader is created to execute the query and retrieve the data. A loop will read a row from the result set, create a DataRow, populate it and add it to the DataTable. Because it is a new row that has just been added, its RowState is set to Added. Once all the data has been read and the DataRows added to the DataTable, the adapter calls AcceptChanges to set every RowState to Unchanged. The idea is that the DataTable represents the current state of the database so the DataRows are all unchnaged from that state. There are times where you might want the RowStates to remain Added, in which case you can set the AcceptChangesDuringFill property to False.

As you edit your data, the RowStates of the DataRows indicate what has been done to those rows. If you add a new row then its RowState will be Added. If you edit an existing row then its RowState will be Modified. If you delete an existing row then its RowState will be Deleted.

When you call Update on your table adapter, it basically loops through the DataRows and determines what to do with each one. If the RowState is Unchanged then that row is ignored, which is why you DO NOT call AcceptChanges before calling Update. If the RowState is Added then the InsertCommand of the adapter is executed to insert that row. If the RowState is Modified then the UpdateCommand is executed to update the corresponding record. If the RowState is Deleted then the DeleteCommand is executed to delete the corresponding record. Once all the changes have been saved, the adapter calls AcceptChanges to set every RowState to Unchanged, which also removes deleted rows from the DataTable altogether. The idea is that the DataTable is again in the same state as the database so everything is unchanged. There are times where you might want the RowStates to remain as they were, in which case you can set the AcceptChangesDuringUpdate property to False.

So, if you are populating the DataTable by calling Fill on your table adapter in the first place then the only issue is that you're calling AcceptChanges when you shouldn't be. If you're not retrieving data from your database but rather adding every row manually then every RowState will be Added and the InsertCommand will be executed for evenry row. In that case, you're basically screwed as far as simply calling Update if you're using an Access database. That's because the solution would be to change the CommandText of your InsertCommand to an "upsert", which means testing whether a record exists and performing an update if it does and an insert if it doesn't. That's not especially difficult but it requires multiple SQL statements and that's not supported with Access. The alternative is to actually query the database yourself to see whether there's a matching record and then set the RowState accordingly. For each DataRow, call SetModified if a record already exists and then, when you call Update on the table adapter, the rows will be inserted or updated as they should.

Thank you so much for this brilliant and full explanation:)

My final solution thanks to your input. Items added or modified by the program are now saved to the database.

Code:

---

  Private Sub UpdateLeaderboard(ByVal jName As String, jWins As Integer)
        Dim test As Boolean = False
        'Check whether the name already exists
        Dim jRow As DataRow = SsrDataSet.JockeyLeaderboard.NewRow
        For XX = 0 To JockeyLeaderboardDataGridView.RowCount - 2
            If jName = JockeyLeaderboardDataGridView.Rows(XX).Cells(1).Value.ToString Then
                jWins = JockeyLeaderboardDataGridView.Rows(XX).Cells(2).Value
                test = True
                jWins += 1
                selRow = XX
                Exit For
            End If
        Next

        If test = True Then
            'update the record with new info
            jWins += 1
            Me.JockeyLeaderboardDataGridView.Rows(selRow).Cells(1).Value = jName
            Me.JockeyLeaderboardDataGridView.Rows(selRow).Cells(2).Value = jWins
        Else
            'the name is not present so add it
            Dim MyNewRow As DataRow
            MyNewRow = SsrDataSet.JockeyLeaderboard.NewRow
            With MyNewRow
                .Item(1) = jName
                .Item(2) = jWins
            End With
            SsrDataSet.JockeyLeaderboard.Rows.Add(MyNewRow)
        End If
        Try
            Me.Validate()
            Me.JockeyLeaderboardBindingSource.EndEdit()
            Me.JockeyLeaderboardTableAdapter.Update(Me.SsrDataSet.JockeyLeaderboard)
            'MsgBox("Update successful")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        'just added to make sure it is reading the database
        Me.JockeyLeaderboardTableAdapter.Dispose()
        Me.JockeyLeaderboardTableAdapter.Fill(Me.SsrDataSet.JockeyLeaderboard)
    End Sub

---