Re: Looping through dataset
Why are you using DataSets at all? You create two DataSets and then just put one DataTable in each. Why not just create two DataTables? In fact, if it's all the same data then just use one DataTable. Here's an example of finding "matching" records from within the same DataTable:
Code:
For Each row As DataRow In myDataTable.Rows
Dim matchingRows = myDataTable.Select(String.Format("SomeColumn = '{0}'", row("SomeColumn")))
For Each matchingRow In matchingRows
If matchingRow IsNot row Then
MessageBox.Show(String.Format("Row {0} matches row {1}.", matchingRow("ID"), row("ID")))
End If
Next
Next
If you do something like that then your current NullReferenceException will presumably go away but, if you want to address that, tell us exactly what line the exception is thrown on and, once you know that, test each reference on that line. Also, check the stack trace of the exception to see whether it's actually thrown by your line of code or deeper in the Framework code called by your line.
Re: Looping through dataset
Thank you for the answer. Actually your answer triggered another thought. Couldn't I just use an update statement inside the loop to put an "X" in each record where it matches for the three columns and is older than the current datarow? I think that might solve all my problems. Again, thank you.
HTML Code:
for each row in dsFound
'create an update statement and execute
.commandtext="update table set field1 = 'X' where name = '" & row("name") & "' and date1 = '" & row("date1") & "' and costcenter = '" & row("costcenter") & "' and hdate < #" & row("hdate") & "#"
'execute the update command
next
That seems like a more straightforward approach.
Re: Looping through dataset
Quote:
Originally Posted by
JMM427
Thank you for the answer. Actually your answer triggered another thought. Couldn't I just use an update statement inside the loop to put an "X" in each record where it matches for the three columns and is older than the current datarow? I think that might solve all my problems. Again, thank you.
HTML Code:
for each row in dsFound
'create an update statement and execute
.commandtext="update table set field1 = 'X' where name = '" & row("name") & "' and date1 = '" & row("date1") & "' and costcenter = '" & row("costcenter") & "' and hdate < #" & row("hdate") & "#"
'execute the update command
next
That seems like a more straightforward approach.
You could do that but then the data in your DataTable would not match what's in the database. In my opinion, it would be better to edit the contents of the DataTable and then save that.
Re: Looping through dataset
Right. So, what I really want to do is reverse the logic and say "if the current row finds a match that's newer without an X, put an X in the current datarow" and then save the results using the data adapter update method with the dataset as the parameter after all the changes? Seems more efficient too because you're not using processing power to do unnecessary update queries to the actual database until the end. Let me know what your thoughts are on that. I'm always looking to learn new things. Thanks.
Re: Looping through dataset
By the way, this is not an active database that's having records added to it while this process is going on, otherwise this idea wouldn't work. There's about 110,000 records that it has to go through. If any records were added during this, the results wouldn't be accurate.