Tony Gardner
asked on
Writing Data from a Non-databound DataGridView to a Databound Table
Hello Experts.
This little head scratcher is a bit more complicated that the other questions I've submitted, so it's definitely not for the faint of heart.
The primary objective is for me to learn the correct method for writing data from a non-databound DataGridView to a databound table.
Background:
I have a databound table called "Roster" which contains sports player names, and other statistical data. This constantly changing data is maintained by an international organized sports organization. My Roster table is used to retain a recent snap-shot of that data specific to my local division teams. To accomplish that, I have created a subroutine which accepts data directly from the clipboard. The user populates the CB by logging into the organization's website and manually selecting the data on the screen. Another subroutine converts the stream to a usable format. The next subroutine retrieves the new data and compares it with the currently stored values. Finally, all identified changes are presented to the user in a non-databound DataGridView object such as this:
Accepting this dialog initiates the table updating process for all rows which are checked as accepted. For now, I would just like to read the first row on the DGV and write the one change to the Roster table (in this case, changing David Rowe's Skill Level from 5 to 6).
Here is the code I put together, but it doesn't seem to be updating the database.
As always, I am entirely open to new ideas and suggestions.
This little head scratcher is a bit more complicated that the other questions I've submitted, so it's definitely not for the faint of heart.
The primary objective is for me to learn the correct method for writing data from a non-databound DataGridView to a databound table.
Background:
I have a databound table called "Roster" which contains sports player names, and other statistical data. This constantly changing data is maintained by an international organized sports organization. My Roster table is used to retain a recent snap-shot of that data specific to my local division teams. To accomplish that, I have created a subroutine which accepts data directly from the clipboard. The user populates the CB by logging into the organization's website and manually selecting the data on the screen. Another subroutine converts the stream to a usable format. The next subroutine retrieves the new data and compares it with the currently stored values. Finally, all identified changes are presented to the user in a non-databound DataGridView object such as this:
Accepting this dialog initiates the table updating process for all rows which are checked as accepted. For now, I would just like to read the first row on the DGV and write the one change to the Roster table (in this case, changing David Rowe's Skill Level from 5 to 6).
Here is the code I put together, but it doesn't seem to be updating the database.
Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
Me.DialogResult = System.Windows.Forms.DialogResult.OK
If MessageBox.Show("Proceed with updates for all checked items?") = vbOK Then
With dgvRosterChanges
Dim dgvRowCount As Integer = .RowCount()
For i = 0 To dgvRowCount - 1
If .Item(6, i).Value = True Then
Dim PlayerID As String = .Item(0, i).Value
Dim PlayerNm As String = .Item(1, i).Value
Dim TeamName As String = .Item(2, i).Value
Dim Val2Chng As String = .Item(3, i).Value
Dim NewValue As String = .Item(5, i).Value
Dim ChgValue As String = ""
Select Case Val2Chng
Case "Match Wins" : ChgValue = "Match_Wins"
Case "Match Losses" : ChgValue = "Match_Losses"
Case "Skill Level" : ChgValue = "SL"
Case "Team Change" : ChgValue = "Teams_Key"
Case Else : ChgValue = ""
End Select
Call UpdateRosterValue(PlayerID, ChgValue, NewValue)
End If
Next
End With
End If
Me.Validate()
RosterBindingSource.EndEdit()
RosterTableAdapter.Update(sData.Roster)
Me.Close()
End Sub
Public Sub [b]UpdateRosterValue[/b](ByVal RecID As String, ByVal FldID As String, ByVal NewVal As String)
Dim TableCount As Integer = sData.Roster.Count
If TableCount = 0 Then RosterTableAdapter.Fill(sData.Roster)
Dim DoUpdate =
(
From RD In sData.Roster
Where RD.APA_Player_ID = CInt(RecID)
).ToList()
For Each row In DoUpdate
row.Item(FldID) = NewVal
row.AcceptChanges()
Next
End Sub
As always, I am entirely open to new ideas and suggestions.
why not write to the database and refresh the grid afterwards ?
ASKER
@Geert G
Thanks for the comment, but it doesn't sound like you understand what I'm trying to accomplish. Think of the DataGridView object as a temporary scratchpad whose sole purpose is to give the end user an interface for approving data changes at a very granular level.
Once the user clicks OK, I just read my marching instructions from the DGV for any updates to the Roster table. So you see, I wouldn't refresh the DGV afterwards (although I suppose that could be another approach) -- I just dismiss it since my work is done.
As far as why I don't just write to the database... I'm trying to do just that, but for some reason unknown to me, the code I've written to do that is not updating the Rosters table. I am still hopeful that with a little help from E.E., I will be able to determine where the error is in my code.
Thanks for the comment, but it doesn't sound like you understand what I'm trying to accomplish. Think of the DataGridView object as a temporary scratchpad whose sole purpose is to give the end user an interface for approving data changes at a very granular level.
Once the user clicks OK, I just read my marching instructions from the DGV for any updates to the Roster table. So you see, I wouldn't refresh the DGV afterwards (although I suppose that could be another approach) -- I just dismiss it since my work is done.
As far as why I don't just write to the database... I'm trying to do just that, but for some reason unknown to me, the code I've written to do that is not updating the Rosters table. I am still hopeful that with a little help from E.E., I will be able to determine where the error is in my code.
ASKER
Quick Code Update:
I'm fairly certain that changing Subroutine UpdateRosterValue as shown below would be a better method for updating the Roster:
I'm fairly certain that changing Subroutine UpdateRosterValue as shown below would be a better method for updating the Roster:
Public Sub UpdateRosterValue(ByVal RecID As String, ByVal FldID As String, ByVal NewVal As String)
Dim TableCount As Integer = sData.Roster.Count
If TableCount = 0 Then taRS.Fill(sData.Roster)
Dim RosterRow As sData.RosterRow
Dim DoUpdate =
(
From RD In sData.Roster
Where RD.APA_Player_ID = CInt(RecID)
).ToList()
For Each row In DoUpdate
RosterRow = sData.Roster.FindByAPA_Player_ID(row.APA_Player_ID)
RosterRow.Item(FldID) = NewVal
RosterRow.AcceptChanges()
Next
End Sub
Also, I did try a quick test of creating a simple DGV directly bound to Roster and it updated the database just fine. Unfortunately, the code changes specified here did not solve the problem, but I'm fairly certain that it is better than what I had previously.
Hi Tony;
I am assuming that you created a DataTable object with the data you got from the web site and you are using this DataTable as the data source for the dgvRosterChanges DataGridView. What I would do is to create a Linq query to find the row you are wanting to modify. Get the key value of the record to be modify and the value to be modified as well as the value to modify the record to. Then Linq query the bound DataTable for the key value then check the value to be modified to make sure it is the same as before and then write the new value to that cell. Then when you are ready push the update to the database.
You have lots of code tha you are not using and hard to follow.
Fernando
I am assuming that you created a DataTable object with the data you got from the web site and you are using this DataTable as the data source for the dgvRosterChanges DataGridView. What I would do is to create a Linq query to find the row you are wanting to modify. Get the key value of the record to be modify and the value to be modified as well as the value to modify the record to. Then Linq query the bound DataTable for the key value then check the value to be modified to make sure it is the same as before and then write the new value to that cell. Then when you are ready push the update to the database.
You have lots of code tha you are not using and hard to follow.
Fernando
ASKER
Thanks Fernando. I'll definitely continue working on this one. I'm sooooooo close I can taste it!
I can watch everything via debugger and see that (as far I can tell) all is as it should be. I just don't understand why it doesn't WRITE to the database file, especially when I can do it from a test DGV object placed on that very same dialog -- which proves that the BindingSource and TableAdapter for the Roster table are setup correctly.
There's something very silly that I am overlooking -- I just haven't identified it yet.
Cheers,
Tony G.
I can watch everything via debugger and see that (as far I can tell) all is as it should be. I just don't understand why it doesn't WRITE to the database file, especially when I can do it from a test DGV object placed on that very same dialog -- which proves that the BindingSource and TableAdapter for the Roster table are setup correctly.
There's something very silly that I am overlooking -- I just haven't identified it yet.
Cheers,
Tony G.
Not sure but AFAIK when you call .ToList() LinqToSQL creates in-memory copy of datasource and all chages in your For each loop changes this list but not datasource.
ASKER
@Ark
I believe you may be onto something here. That would certainly explain why according to the debugger, everything appears to be working.
I've been trying to write directly to the Roster table using the BindingSource and TableAdapter, but there was no CurrencyManager in the mix.
I've already been working on writing the new values to a databound DGV hidden behind the Roster Changes DGV and have been getting some encouraging results. Here's what I came up with:
What puzzles me though is why I can't do the same with a bound table. It just feels like I'm having to take a detour!
I believe you may be onto something here. That would certainly explain why according to the debugger, everything appears to be working.
I've been trying to write directly to the Roster table using the BindingSource and TableAdapter, but there was no CurrencyManager in the mix.
I've already been working on writing the new values to a databound DGV hidden behind the Roster Changes DGV and have been getting some encouraging results. Here's what I came up with:
Private Sub FindDataGridViewRowById(ByVal RecID As String, ByVal FldID As String, ByVal NewVal As String)
Dim dgvObj As DataGridView = DataGridView1
Dim dgvCol As DataGridViewColumn = dgvObj.Columns.Item(0)
Dim rowIdx =
(
From dgvRow As DataGridViewRow In dgvObj.Rows,
dgvCell As DataGridViewCell In dgvRow.Cells
Where dgvCell.Value.ToString = RecID
Select dgvRow.Index()
).First
dgvObj.Item(FldID, rowIdx).Value = NewVal
End Sub
What puzzles me though is why I can't do the same with a bound table. It just feels like I'm having to take a detour!
Did you try just remove .ToList() and edit Linq query?
Dim DoUpdate =
(
From RD In sData.Roster
Where RD.APA_Player_ID = CInt(RecID)
)
For Each row As DataRow In DoUpdate
row(FldId) = NewVal
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.