Sturgus Sturgus - 6 months ago 62
Vb.net Question

Issue updating rows on a sorted DataGridView with a DataTable DataSource

I have a Win Forms app in VB.NET 4.5. In it, I use a

DataGridView
with a
DataTable
as its
DataSource
and a well-defined
SqlDataAdapter
to keep everything concurrent. The
DataTable
is first filled by a Sql Server command and then bound like so:

Dim dt_Fishes AS New DataTable
Dim da_Fishes = New SqlDataAdapter("SELECT FishesKey, NetKey, b, c, d, e, (etc) FROM tblFishes WHERE NetKey = @NetKey;", conn)
da_Fishes.SelectCommand.Parameters.AddWithValue("@NetKey", intNetKey)
Dim cmdbld_Fishes As New SqlCommandBuilder(da_Fishes)
dt_Fishes.Locale = System.Globalization.CultureInfo.InvariantCulture

Try
conn.Open()
da_Fishes.Fill(dt_Fishes)
Catch ex As Exception
' etc
Finally
conn.close()
End Try
da_Fishes.DeleteCommand = New SqlCommand("DELETE FROM dbo.tblFishes WHERE FishesKey = @FishesKey;", conn)
da_Fishes.DeleteCommand.Parameters.Add("@FishesKey", SqlDbType.Int)
dgv_Fishes.DataSource = dt_Fishes
FormatDGV(dgv_Fishes) ' adds DataGridViewComboBoxColumns and colours, hides keys, etc


This is all working fine when the data are not sorted - inserts, updates, and deletes are functioning as they should while going through a row-level validation. Row-level validation fires once the user leaves the row they're working on.

When the user clicks on a column header, the
DataGridView
visually sorts the data as you would expect (great!). My issue is that when the user then proceeds to edit a row in the sorted
DataGridView
, and leaves that row (via the enter or down keyboard keys), no validation event is fired. Curiously, if the user then keys to a cell of the row-number in the
DataGridView
that normally belongs to the edited data row, and then leaves that, then validation fires and the data update successfully occurs.

How do I force the
RowValidating
event to happen when the user leaves the edited row as normal when the
DataGridView
is sorted?

Answer

Apologies for the red herring:

On further investigation, the RowValidating event is indeed firing when leaving a row. Then the RowValidated event fires, but my problem seems to occur when I check the DataTable's row-state. After sorting the DataGridView, it and the underlying DataTable no longer have similar row numbers. Which is why this if-statement is the culprit of my problems:

Private Sub dgv_RowValidated(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_Main.RowValidated
  Dim dgv_Sender As DataGridView = sender
  QueryAction = dt_Sender.Rows(e.RowIndex).RowState
  If dt_Sender.Rows(e.RowIndex).RowState <> DataRowState.Unchanged Then
    da_Fishes.Update(dt_Fishes)

And so, I need an alternate way to check the row states (the purpose of the if-statement is to minimize unnecessary updates).

Given this, my original question of

Issue updating rows on a sorted DataGridView with a DataTable DataSource

has been resolved, as I'll now switch gears to sorting out a different way to perform the above check on a sorted DataGridView...

Comments