Link to home
Start Free TrialLog in
Avatar of Tony Gardner
Tony GardnerFlag for United States of America

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:
User generated image
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

Open in new window


As always, I am entirely open to new ideas and suggestions.
Avatar of Geert G
Geert G
Flag of Belgium image

why not write to the database and refresh the grid afterwards ?
Avatar of Tony Gardner

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.
Quick Code Update:
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

Open in new window

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
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.
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.
@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:
    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

Open in new window


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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Tony Gardner
Tony Gardner
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial