-
Nov 6th, 2009, 11:11 AM
#1
Thread Starter
New Member
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.
-
Nov 6th, 2009, 11:57 AM
#2
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
-
Nov 6th, 2009, 12:43 PM
#3
Thread Starter
New Member
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
-
Nov 6th, 2009, 01:08 PM
#4
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
-
Nov 6th, 2009, 01:12 PM
#5
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
-
Nov 6th, 2009, 03:41 PM
#6
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|