Looping through dataset

[JMM427]

I need to take all the data in a dataset and, for each record, I need to find if there are other records that match it based on three fields and if there are, I need to mark the oldest one based on a date field. The only way I could think of to do this is to loop through the dataset and create a new select statement for each record and fill a new dataset with all the matches for that record, then process and repeat. Does that make sense? or is there a much easier way to do it? I have the following code so far, but I keep getting an error after the second fill method that says "object reference not set to instance of an object....". That makes no sense to me since I declare it with "new" above. I commented out the rest of the second select statement just to see if it was the search that was coming up with no data.

 Dim dsENY As New DataSet
        Dim dsFound As New DataSet

        Dim daENY As New OleDb.OleDbDataAdapter

        daENY.SelectCommand = New OleDb.OleDbCommand
        daENY.UpdateCommand = New OleDb.OleDbCommand
       
        With daENY.SelectCommand
            .Connection = OleDbConnection1
            .CommandText = "SELECT * FROM ENY1THRU4"
            .CommandType = CommandType.Text
        End With

        With daENY.UpdateCommand
            .Connection = OleDbConnection1
            .CommandType = CommandType.Text
        End With
       
        dsENY.Clear()
        Try
            daENY.Fill(dsENY, "ENY1THRU4")
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "ERROR LOADING DATA", MessageBoxButtons.OK)

        End Try
        Label1.Text = dsENY.Tables("ENY1THRU4").Rows.Count.ToString & " PNRs to move..."
        ProgressBar1.Minimum = 1
        ProgressBar1.Maximum = dsENY.Tables("ENY1THRU4").Rows.Count
        Dim dr As DataRow

        For Each dr In dsENY.Tables("ENY1THRU4").Rows

            daENY.SelectCommand.CommandText = "SELECT * FROM ENY1THRU4" 'WHERE PNRNO = '" & dr("PNR") & "' AND HPROP_NO = '" & dr("HOD") & "'"
            dsFound.Clear()
            Try
                daENY.Fill(dsFound, "ENY1TRHU4")
            Catch ex As Exception
                MessageBox.Show(ex.ToString, "ERROR FOUND", MessageBoxButtons.OK)
            End Try
            Label1.Text = dsFound.Tables("ENY1THRU4").Rows.Count.ToString & " PNRs to move..." 'THIS IS WHERE I GET THE INSTANCE ERROR
            Application.DoEvents()
        Next

[jmcilhinney]

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:

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.

[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.

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.

[jmcilhinney]

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.

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.

[JMM427]

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.

[JMM427]

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.