-
May 14th, 2018, 10:16 AM
#1
Thread Starter
Lively Member
[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
-
May 14th, 2018, 12:49 PM
#2
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.
-
May 14th, 2018, 09:40 PM
#3
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.
Last edited by jmcilhinney; May 15th, 2018 at 03:02 AM.
-
May 15th, 2018, 02:56 AM
#4
Thread Starter
Lively Member
Re: Using a Data=bound DGV Find and update a record or insert it
Thank you so much for this brilliant and full explanation
-
May 15th, 2018, 08:16 AM
#5
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|