Question DataTables,XML,DataView

[Ken W]

This is a sampling of the xml with the incorrect Item_ID's.

[COLOR=#0000ff]version="1.0" standalone="yes" ?>[/COLOR] 
[URL="file:///C:/VB_DEBUG_TEXT_FILES/savedItems.xml#"][B][FONT=Courier New][COLOR=#ff0000]-[/COLOR][/FONT][/B][/URL] [COLOR=#0000ff]<[/COLOR][COLOR=#990000]DocumentElement[/COLOR][COLOR=#0000ff]>[/COLOR]
[URL="file:///C:/VB_DEBUG_TEXT_FILES/savedItems.xml#"][B][FONT=Courier New][COLOR=#ff0000]-[/COLOR][/FONT][/B][/URL] [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Saved_Items[/COLOR][COLOR=#0000ff]>[/COLOR]
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Name[/COLOR][COLOR=#0000ff]>[/COLOR][B]Sugar Bulk[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Name[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Count[/COLOR][COLOR=#0000ff]>[/COLOR][B]2[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Count[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Price[/COLOR][COLOR=#0000ff]>[/COLOR][B]34.20[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Price[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_ID[/COLOR][COLOR=#0000ff]>[/COLOR][B]371[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_ID[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Category[/COLOR][COLOR=#0000ff]>[/COLOR][B]3[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Category[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]</[/COLOR][COLOR=#990000]Saved_Items[/COLOR][COLOR=#0000ff]>[/COLOR]
 
 
[URL="file:///C:/VB_DEBUG_TEXT_FILES/savedItems.xml#"][B][FONT=Courier New][COLOR=#ff0000]-[/COLOR][/FONT][/B][/URL] [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Saved_Items[/COLOR][COLOR=#0000ff]>[/COLOR]
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Name[/COLOR][COLOR=#0000ff]>[/COLOR][B]Brown Sugar[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Name[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Count[/COLOR][COLOR=#0000ff]>[/COLOR][B]1[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Count[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Price[/COLOR][COLOR=#0000ff]>[/COLOR][B]23.86[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Price[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_ID[/COLOR][COLOR=#0000ff]>[/COLOR][B]48[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_ID[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Category[/COLOR][COLOR=#0000ff]>[/COLOR][B]3[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Category[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]</[/COLOR][COLOR=#990000]Saved_Items[/COLOR][COLOR=#0000ff]>[/COLOR]
 
 
[URL="file:///C:/VB_DEBUG_TEXT_FILES/savedItems.xml#"][B][FONT=Courier New][COLOR=#ff0000]-[/COLOR][/FONT][/B][/URL] [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Saved_Items[/COLOR][COLOR=#0000ff]>[/COLOR]
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Name[/COLOR][COLOR=#0000ff]>[/COLOR][B]Crushed Pineapple[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Name[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Count[/COLOR][COLOR=#0000ff]>[/COLOR][B]1[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Count[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Price[/COLOR][COLOR=#0000ff]>[/COLOR][B]35.21[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Price[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_ID[/COLOR][COLOR=#0000ff]>[/COLOR][B]121[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_ID[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Category[/COLOR][COLOR=#0000ff]>[/COLOR][B]3[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Category[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]</[/COLOR][COLOR=#990000]Saved_Items[/COLOR][COLOR=#0000ff]>[/COLOR]
 
 
[URL="file:///C:/VB_DEBUG_TEXT_FILES/savedItems.xml#"][B][FONT=Courier New][COLOR=#ff0000]-[/COLOR][/FONT][/B][/URL] [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Saved_Items[/COLOR][COLOR=#0000ff]>[/COLOR]
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Name[/COLOR][COLOR=#0000ff]>[/COLOR][B]Ice Tea[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Name[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Count[/COLOR][COLOR=#0000ff]>[/COLOR][B]1[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Count[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Price[/COLOR][COLOR=#0000ff]>[/COLOR][B]28.43[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Price[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_ID[/COLOR][COLOR=#0000ff]>[/COLOR][B]198[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_ID[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]<[/COLOR][COLOR=#990000]Item_Category[/COLOR][COLOR=#0000ff]>[/COLOR][B]8[/B][COLOR=#0000ff]</[/COLOR][COLOR=#990000]Item_Category[/COLOR][COLOR=#0000ff]>[/COLOR] 
 
 [COLOR=#0000ff]</[/COLOR][COLOR=#990000]Saved_Items[/COLOR][COLOR=#0000ff]>[/COLOR]

This is the database structure>>>




Below is a Sub, for the sake of getting it to work and to post on here. It doesn't matter what it's wrapped in. The problem is in the Comments at the top of the code and the solution I used to solve the problem. The Question, I'm asking, is there a better way to pull this off?

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] ProcessBadDataTable()[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Problem: XML file has information that is important but the Item_ID for each item is incorrect[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'and it can't be used to update the inventory table Item_Price column.[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Solution: Load 2 datatables 1 with the entire Inventory of Items and the other with the bad xml[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'items that must be used to update the Inventory Item_Price column[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Use the Item_Name from the "BAD" datatable to find the correct Item_ID on the Inventory Table[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'When the correct Item_ID is found write that value to the "BAD" datatables Item_ID column.[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'After all the Items have been corrected write the now "GOOD" datatable to a new xml file.[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'This XML file will be used by many employees[/COLOR][/SIZE]
 
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Read the xml file into a dataset[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ds_Inventory [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataSet()[/SIZE]
[SIZE=2]ds_Inventory.ReadXml([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"C:\VB_DEBUG_TEXT_FILES\savedItems.xml"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Table dt holds the bad records with the bad Item_ID's[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataTable[/SIZE]
[SIZE=2]dt = ds_Inventory.Tables(0)[/SIZE]
 
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Create connection & command[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Create dataset[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Create data adapter[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'connect to the inventory[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] conStr [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sirloin.mdb"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] conn2 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbConnection(conStr)[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Select * From tbl_Inventory"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], conn2)[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dsInventory [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataSet()[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] daInventory [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(cmd)[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Fill the dataset [/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]daInventory.Fill(dsInventory)[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'make datatable from tbl_Inventory[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'dtInventory holds the good ID's[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dtInventory [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataTable[/SIZE]
[SIZE=2]dtInventory = dsInventory.Tables(0)[/SIZE]
 
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Create DataView of the Inventory Table [/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'and sort on Item_Name column[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dv [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataView()[/SIZE]
[SIZE=2]dv.Table = dtInventory[/SIZE]
[SIZE=2]dv.Sort = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Item_Name"[/COLOR][/SIZE]
 
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Compare and change the Item_ID's[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] index [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As Integer[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Loop thru the bad datatable and use the Item_Name [/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'to get the correct Item_ID from the Inbventory[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = 0 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dt.Rows.Count - 1[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Get the Index of the matching row[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]index = dv.Find(dt.Rows(i).Item(0).ToString) [/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Item(0) is the Item_Name column[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] index = -1 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]MsgBox([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Item Not Found"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Else[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'write the correct Item_ID to the datatable[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]dt.Rows(i).Item(3) = dv(index)([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Item_ID"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End If[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'show the corrected datatable on the DataGridView[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]dgv1.DataSource = dt[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Write the corrected data to the new XML file[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]dt.WriteXml([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"C:\VB_DEBUG_TEXT_FILES\newSavedItems.xml"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End Sub[/COLOR][/SIZE][/COLOR][/SIZE]
[/COLOR][/SIZE][/COLOR][/SIZE]