VB.NET OleDb dataadapter update not updating

Hi all,

I have an application to re-sequence some items based on a an alphabetical ordering of the items from another table. I couldn't create an update statement to update the database when I pulled the information by a join query, So I pulled down both tables into a dataset with an XML Schema description where the two are related by the itm_id key as in the database. I then have two datagrids one with the items and one with the bound to the relationship this allows only the records associated with the itm to be displayed in the datagrid, I then run a loop that changes to sequence number for each corresponding record,this all works fine but then I go to update the back end database. The update from the dataadapter executes successfully, but then if I reload the dataset from the database back end I find that the update did not actually update the records on the back end. The only record that was updated was the very first record which updated with a 0 then all the others did not take, I have re-run it several times and they will not take. My code is below, can anyone think of a reason this is occurring?

Code:

---

                Me.Cursor = cursors.WaitCursor
               
                odbaCatItem.MissingSchemaAction = MissingSchemaAction.AddWithKey
        odbaCatItem.SelectCommand = odbsCatItem
        odbaCatItem.SelectCommand.Connection = odbConn
       
        odbaCatItem2.MissingSchemaAction = MissingSchemaAction.AddWithKey
        odbaCatItem2.SelectCommand = odbsCatItem2
        odbaCatItem2.SelectCommand.Connection = odbConn
       
        dsWebstore.ReadXmlSchema("dsWebstore.xsd")
       
        odbacat.MissingSchemaAction = MissingSchemaAction.AddWithKey
        odbacat.SelectCommand = odbscat
        odbacat.SelectCommand.Connection = odbConn
       
                me.odbConn.Open()
               
        odbaCatItem2.Fill(dsWebstore, "item")
        odbaCatItem.Fill(dsWebstore, "catalogitem")
       
        odbacat.Fill(dscat, "category")
       
        Me.odbConn.Close()
       
        GC.Collect()
        Me.Cursor = cursors.Default

                dgitems.DataSource = dsWebstore
                dgitems.DataMember = "item"
                dgcatitem.DataSource = dsWebstore
                dgcatitem.DataMember = "item.FK_item_catalogitem"
               
                Dim seq((dscat.Tables.Item(0).Rows.Count - 1)) As Int32
                Dim catid((dscat.Tables.Item(0).Rows.Count - 1)) as Int32
                Dim rec As Int32 = 0
                Dim item as Int32 = 0
                Dim iID As Int32 = 0
               
                For rec = 0 To (dscat.Tables.Item(0).Rows.Count - 1)
                        catid(rec) = dscat.Tables.Item(0).Rows.Item(rec).Item(0)
                        seq(rec) = 0
                Next
               
                For iID = 0 To (dsWebstore.Tables.Item("item").Rows.Count - 1)
                        lblRecord.Text = (iID + 1).ToString & " of " & dsWebstore.Tables.Item("item").Rows.Count.ToString
                        dgitems.CurrentRowIndex = iID
                        dgitems.Refresh()
                        dgcatitem.Refresh()
                        For rec = 0 To (dgcatitem.VisibleRowCount - 2)
                                dgcatitem.CurrentRowIndex = rec
                                'msgBox(dsWebstore.Tables.Item("item").ChildRelations.Item(0).ChildTable.Rows.Count.ToString)
                                'msgBox(seq.Length & " " & catid.Length)
                                dgcatitem.Item(rec,3) = seq(Array.IndexOf(catid, dgcatitem.Item(rec,2)))
                                seq(Array.IndexOf(catid, dgcatitem.Item(rec,2))) = (seq(Array.IndexOf(catid, dgcatitem.Item(rec,2))) + 10)
                                dgcatitem.Refresh()
                                'me.dsWebstore.AcceptChanges()
                               
                                GC.Collect()
                        Next
                        'Becuase I am testing I only go through 5 records of the 44000+ records
                        If iID > 5 then Exit For
                       
                       
                        Threading.Thread.Sleep(100)
                        Application.DoEvents
                        GC.Collect()
                Next
                odbConn.Open()
                Me.odbaCatItem.Update(dsWebstore,"catalogitem")
                odbConn.Close()

---

Thanks for any help you can all provide.

Try updating your datatable rather than your datagrid. Also read this about using GC.Collect: http://codebetter.com/blogs/steve.he...21/129634.aspx

I would except that the datatable has far more records in it than the filtered records and I am unsure how to efficiently locate the records that need to be updated

I would create a strongly typed dataset, and setup keys and relationships, rather than using a generic dataset.

GetChanges will return the set of rows that need to be updated.

I got it to work with Wild Bills advice on using the dataset. I did have an XML defined Schema with relationships setup that mirrored the exact schema of the Back End Database. THe issue I was having was getting that subset of rows without using a filter from the catalogitem datatable. The catalogitem datatable had a 3 column key and I ended up using the data from the subset datagrid to populate an array with the three key values then using a Find and a IndexOf to get the appropriate datatable row. It it running now and working beautifully if I do say so. Here is the modified code below

Code:

---

                  For iID = 0 To (dsWebstore.Tables.Item("item").Rows.Count - 1)
                       
                        Dim subkey(2) As String
                        lblRecord.Text = (iID + 1).ToString & " of " & dsWebstore.Tables.Item("item").Rows.Count.ToString
                        dgitems.CurrentRowIndex = iID
                        dgitems.Refresh()
                        dgcatitem.Refresh()
                        For rec = 0 To (dgcatitem.VisibleRowCount - 2)
                                dgcatitem.CurrentRowIndex = rec
                                subkey(0) = (dgcatitem.Item(rec,0))
                                subkey(1) = (dgcatitem.Item(rec,1))
                                subkey(2) = (dgcatitem.Item(rec,2))
                                                                                                dsWebstore.Tables.Item("catalogitem").Rows.Item(dsWebstore.Tables.Item("catalogitem").Rows.IndexOf(dsWebstore.Tables.Item("catalogitem").Rows.Find(subkey))).Item(3) = seq(Array.IndexOf(catid, dgcatitem.Item(rec,2)))
                                dgcatitem.Refresh()
                                seq(Array.IndexOf(catid, dgcatitem.Item(rec,2))) = (seq(Array.IndexOf(catid, dgcatitem.Item(rec,2))) + 10)
                               
                        Next

---