Results 1 to 6 of 6

Thread: VB.NET OleDb dataadapter update not updating

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2007
    Posts
    4

    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.

  2. #2
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: VB.NET OleDb dataadapter update not updating

    Try updating your datatable rather than your datagrid. Also read this about using GC.Collect: http://codebetter.com/blogs/steve.he...21/129634.aspx
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2007
    Posts
    4

    Re: VB.NET OleDb dataadapter update not updating

    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

  4. #4
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: VB.NET OleDb dataadapter update not updating

    I would create a strongly typed dataset, and setup keys and relationships, rather than using a generic dataset.
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: VB.NET OleDb dataadapter update not updating

    GetChanges will return the set of rows that need to be updated.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    New Member
    Join Date
    May 2007
    Posts
    4

    Re: VB.NET OleDb dataadapter update not updating

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width