Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2013
    Posts
    91

    Resolved [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
    Last edited by Penfound; May 15th, 2018 at 08:15 AM. Reason: Correct Title

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Using a Data=bound DGV Find and update a record or insert it

    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.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Using a Data=bound DGV Find and update a record or insert it

    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.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Sep 2013
    Posts
    91

    Re: Using a Data=bound DGV Find and update a record or insert it

    Thank you so much for this brilliant and full explanation

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2013
    Posts
    91

    Re: Using a Databound DGV Find and update a record or insert it

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width