[RESOLVED] DataTable Adapter update very slow

I have an ADO DataSet with a table of a few hundred rows. I created a DataGridView with some custom code to prevent the user from selecting an invalid combination of values. It worked great and then I decided I would move the selections column to the SQL Db for use elsewhere ion the program. VS is amazing how the wizards set everything up for me in normal circumstances. I don't know how to do all this myself and my ignorance is causing me fits. I struggled though it all today making some other examples and analyzing what all the wizards do and created a setup that works now that meshes with my old code. But now when I use the TableAdapter Update method against the table it takes about 30 seconds to push the data back to the SQL server. The SQL server is over the internet but I have a fast and reliable connection. In most case only about a dozen rows change. But as I'm looking at the progress from SSMS it seems the method is updating all the rows in the table regardless of whether they have changed or not. The MSDN says it will only execute changes on rows which have been modified. I followed the instructions on this page. Is this behavior and performance normal? Am I doing something wrong?

The first thing to do would be to confirm the RowState of each DataRow. Make sure that the rows you expect to have changed have a value of Modified and the others are Unchanged.

Sensible. I don't know how to make that determination however. I'll research that now. But if you can tell me how to check the row states I'd appreciate it. I looked at the DataTabble in the debugger but it only shows me the normal columns.

Learned it. Without doing anything in the DGV all 207 rows have the RowState of Modified. Any ideas how this could happen? I'll start sleuthing to see if I can figure out where this is happening.

This is bizarre. I added debug lines to each line of this method and every time the count of modified rows is zero until "cbVendor.SelectedIndex = 0". What the heck? How does changing a ComboBox modify all the rows of an disassociated DataTable?

vn.net Code:

Private Sub frmJdxSelection_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    JurisdictionsTableAdapter.Fill(DsMain.Jurisdictions)
    Dim strVendors As New List(Of String)
    For Each rowTemp As dsMain.JurisdictionsRow In DsMain.Jurisdictions
        If Not strVendors.Contains(rowTemp.Vendor) Then
            strVendors.Add(rowTemp.Vendor)
        End If
    Next
    strVendors.Sort()
    strVendors.Insert(0, "All")
    cbVendor.Items.AddRange(strVendors.ToArray)
    cbVendor.SelectedIndex = 0
    blnLoaded = True
End Sub

Do you have a handler for the SelectedIndexChanged event of that ComboBox?

By the way, you can simplify that code like this:

vb.net Code:

Dim distinctVendors = DsMain.Jurisdictions.Select(Function(row) row.Vendor).Distinct()
 
cbVendor.DataSource = {"All"}.Concat(distinctVendors.OrderBy(Function(s) s)).ToArray()

Oh cool! Nice code. I had something like that but I didn't have the "All". Good show.

I do have a handler for the SelectedIndexChanged.

Quote:

---

Originally Posted by cory_jackson

I do have a handler for the SelectedIndexChanged.

---

So what's in it? I suspect that that is where your RowStates are being changed.

You're a genius. I had a loop to clear any selections but it wasn't conditional so it was setting each to false. Thank you very much.