VB.NET - Looping through datatables and compare row values

Question

4.00/5 (1 vote)

See more:

I'm using visual studio (vb.net) and have two datatables (dt and dt2). I want to compare each row of datatables only with each other --> row1 in dt with row1 in dt2, row2 in dt with row2 in dt2, row3 in dt with row3 in dt2 and so on.

My code now looks like this and the program compares each row in dt with each row in dt2, which is not ok.

VB

For Each row As DataRow In dt.Rows
    For Each row As DataRow In dt2.Rows

        Dim PTP_zakl As Date = row(1).ToString
        Dim AX_prevz As Date = row(1).ToString

        If PTP_zakl < AX_prevz Then
            sendMail
        Else
            Do nothing
        End If

    Next
Next

Please help me.
Thanks

Posted 25-Jul-15 8:48am

mrUR

Add a Solution

3 solutions

Solution 1

Advice: Learn about debugger and look what append to row.

You need something like:

Go to first row of Dt
Go to first row of Dt2
repeat
  do your stuff here

  go to next row of Dt
  go to next row of Dt2
until End of Dt or End of Dt2

You also need to learn how to access your data

Posted 25-Jul-15 9:23am

Patrice T

Updated 25-Jul-15 13:15pm

v2

Comments

mrUR 25-Jul-15 17:40pm

I do use debugger, but all i see is that the program loops through all of possible row combinations.

Your hint is good, but still don't know exactly how to do it :/

Solution 2

The code you posted would compare each row from dt1 with each other row from dt2 (if it's coded right). What you have to do is :
- get the maximum number of rows (either in dt1 or dt2).

VB

NumberOfRows = Math.Max(dt1.rows.count , dt2.rows.count)

- make a loop with this variable, but check if the rows are assigned and instanced

VB

for i as integer = 0 to NumberOfRows -1
   ' check and compare here
next

- compare the entries and if not equal do your action

Posted 25-Jul-15 10:04am

Ralf Meier

Updated 25-Jul-15 11:00am

v3

Comments

Patrice T 25-Jul-15 16:52pm

No, its code is bugged to the core.
For each row of Dt, it read each row of Dt2, and store the first field from Dt2 in both PTP_zakl and AX_prevz
it then compare both vars for a difference which always fail

Ralf Meier 25-Jul-15 16:59pm

You are right - but if the code is written correctly it would work like I described (I improved my answer). The actual code only compares both rows(1) - completly without sense. My intension was to help to find the right Solution - not to work with the inquirers code-snipped - I think that was your intension too ...

Patrice T 25-Jul-15 17:05pm

Sure, that is why I propose to learn about debugger :)

mrUR 25-Jul-15 17:33pm

Ok, i've taken your part of code Ralf, but i'm not sure if i did it right way.

Hide Copy Code
Dim NumberOfRows = Math.Max(dt.Rows.Count, dt2.Rows.Count)

<pre>
For i As Integer = 0 To NumberOfRows - 1

If dt.Rows.Count <= dt2.Rows.Count Then
sendMail
Else
Do nothing
End If
Next</pre>

As a result for example i get "dt.rows.count 4 < dt2.rows.count 4" and this is not ok because it should check a value inside rows.

All rows are in DATE format and if row1(dt) has value 5/5/2015 and row1(dt2) has value 7/5/2015, program should send an email (because dt < dt2) and so on for all rows.

Patrice T 25-Jul-15 22:35pm

You really need to understand what your code is doing. Go to Documentation, abuse Help, use debugger.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-25T18:37:00

Solution 4

As already pointed out you compare all of the rows with each other. You wrote that the requirement is to compare rows only with each other but the first question is: Is the index of the row really defining the rows to compare?

What I mean is that:
- do you have the same amount of rows in both tables?
- are the rows added to the tables so that row 2 in dt is really the same thing as row 2 in dt2 and not the row 3?

Having that said, of you have the same amount of rows then simply loop through one table and fetch corresponding row. Something like:

VB

For counter As Integer = 1 To dt.Rows.Count

   Dim PTP_zakl As Date = dt.Rows(counter)(1).ToString
   Dim AX_prevz As Date = dt2.Rows(counter)(1).ToString

   If PTP_zakl < AX_prevz Then
      sendMail()
   End If
Next counter

As a side note, if the column 1 contains a date I'd use CType to convert the object to the date, not ToString and implicit conversion.

ADDITION:

If the comparison can be done based on a column to find a matching row the loop could be simplified to something like the following:

VB

For Each dt2_row As System.Data.DataRow In dt2.Rows
   If (dt.Select("Manjkajoči_MA <'" & dt2_row("Prevzeti_MA").ToString & "'").Count = 0) Then
      sendMail()
   End If
Next dt2_row

Posted 25-Jul-15 18:37pm

Wendelius

Updated 26-Jul-15 5:17am

v4

Comments

mrUR 26-Jul-15 5:39am

I should explain all that earlier, sorry:
- No dt table has more rows than dt2 (seen bellow)
- both tables get rows from sql query, from different database. I guess that's why some rows are not present in dt2.

I'll try to provide how the output looks like...

Wendelius 26-Jul-15 5:41am

Ok, so if dt2 has rows 1,2,3 and 4 and dt has rows 1,2,3 how do you know that is't number 4 missing and not some other row? In my opinion you should have some column to identify the row and not to rely on the index number of the row.

mrUR 26-Jul-15 5:47am

This is the first couple of rows for dt:

Manjkajoči_MA; Datum_konca
MA31623; 19.5.2015 0:00
MA11702; 11.6.2015 0:00
MA05840; 22.6.2015 0:00
MA05840; 22.6.2015 0:00
MA05840; 22.6.2015 0:00
MA01366; 2.7.2015 0:00
MA01366; 2.7.2015 0:00
MA31623; 7.7.2015 0:00
MA31887; 13.7.2015 0:00
MA20017; 13.7.2015 0:00
MA05840; 13.7.2015 0:00
MA31389; 13.7.2015 0:00
MA05485; 13.7.2015 0:00
MA32347; 15.7.2015 0:00
MA05485; 15.7.2015 0:00
MA06170; 15.7.2015 0:00
MA32917; 15.7.2015 0:00
MA32917; 15.7.2015 0:00
MA01546; 16.7.2015 0:00
MA01546; 16.7.2015 0:00
MA01546; 16.7.2015 0:00
MA01546; 16.7.2015 0:00
MA01546; 16.7.2015 0:00
MA01546; 16.7.2015 0:00
MA01546; 16.7.2015 0:00
MA01546; 16.7.2015 0:00
MA01546; 16.7.2015 0:00
....

and this is first couple of rows for dt2

Prevzeti_MA; Datum_prevzema
MA31623; 5.5.2015 0:00
MA11702; 16.3.2015 0:00
MA05840; 4.6.2015 0:00
MA05840; 4.6.2015 0:00
MA05840; 4.6.2015 0:00
MA01366; 11.3.2015 0:00
MA01366; 11.3.2015 0:00
MA31623; 5.5.2015 0:00
MA31887; 17.3.2015 0:00
MA05840; 4.6.2015 0:00
MA31389; 5.3.2015 0:00
MA05485; 8.6.2015 0:00
MA32347; 2.6.2015 0:00
MA05485; 8.6.2015 0:00
MA32917; 19.3.2015 0:00
MA32917; 19.3.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
MA01546; 24.7.2015 0:00
....

If we look at row10 there is "MA20017" but it's only present in dt. In dt2 row10 should have value NULL or something, otherwise further row comparison on MA is impossible. Hope it's clear enough :)

Wendelius 26-Jul-15 5:58am

Have a look at the modified answer. Is that what you're after?

mrUR 26-Jul-15 6:29am

When i copy ADDITION code in my program it gets underline saying 'Count' is not a member of 'System.Array' so i used .Lenght instead of .Count, i guess that's ok too?

But when i start the program i get this error: "Additional information: There is no row at position 1" and it refers to Dim PTP_zakl As Date = dt.Rows(counter)(1).ToString. But there is row1 in dt, so why the error??

Wendelius 26-Jul-15 7:17am

Length is fine. I often use LINQ additions but there's no need to use them.

What comes to the error, don't use the old variables anymore. Just use the last code I wrote, nothing else.

mrUR 26-Jul-15 9:10am

Ah i see. Ok i used new code but results are still not ok... Because when row value Prevzeti_MA (in dt2) is bigger than Manjkajoči_MA (in dt) program should send email, but it doesn't, it just moves forward like nothing happens.

Maybe it has something to do with "To String" conversion? Just guessing...

Wendelius 26-Jul-15 11:17am

Sorry, that's a mistake I made in the query. I used equality operator instead of greater than. Have a look at the modified example.

Wendelius 26-Jul-15 11:18am

However, keep in mind that this is string comparison, you're not comparing numbers. For example MB1 would be greater than MA9 since B is greater than A.

mrUR 26-Jul-15 11:50am

:) i already try code with greater than operator, but still not ok, because in my output there is always MA....., i don't have MB.....

Hmm when i looked at code again i wonder why didn't we compare "Datum_konca" (in dt) and "Datum_prevzema" (in dt2) for each rows? And if Datum_prevzema > Datum_konca --> send Email.
But Datum_konca and Datum_prevzema should be in DATE format (like i did in my code at the begining of this question, if u take a look) otherwise results are not ok.

Sorry if i'm complicating too much, i have so much to learn in coding :)

Wendelius 26-Jul-15 11:57am

Why didn't we use another field, well I have absolutely no knowledge what the fields or the data means or how is it organized :)

You can modify the comparison as you like and use the debugger to see the values when running the application. The point of my example is just to create a condition and check how many rows on the other datatable pass that condition. If the amount is greater than 0 then send the mail.

mrUR 26-Jul-15 15:08pm

Well your right, you really can't know that :) But yeah i should use the other fields, because i'm periodically checking which MA's has datum_prevzema bigger than datum_konca (both are datetime format) and send user an email with that MA's. If datum_konca is bigger than datum_prevzema then the program does nothing. That's the whole point :)

Wendelius 26-Jul-15 15:17pm

Ok, it sounds like comparing the dates would be a better option. Just change the columns in the comparison

Just note that when you add the date to compare with, format the string into ISO format. In other words use .ToString("yyyy-MM-dd hh:mm:ss")

mrUR 27-Jul-15 9:53am

I tried to change the code as you said, but i get error saying "Conversion from string "yyyy-MM-dd hh:mm:ss" to type 'Integer' is not valid"

But where did the type Integer come from??

By the way, i really thank you for all your help, explanation and a fast feedback :)

Wendelius 27-Jul-15 9:57am

What is the code you currently have?

mrUR 27-Jul-15 11:56am

I tried that, when i got error:

For Each dt2_row As System.Data.DataRow In dt2.Rows
If (dt.Select("Datum_konca < '" & dt2_row("Datum_prevzema").ToString("yyyy-MM-dd hh:mm:ss"). & "'").Length = 0) Then
MsgBox("OK")
Else
End If
Next dt2_row

Wendelius 27-Jul-15 12:50pm

Have a try with a little bit different code

      Dim Prevzeti_MA As Date
      Dim Prevzeti_MA_string As String

      For Each dt2_row As System.Data.DataRow In dt2.Rows
         Prevzeti_MA = dt2_row("Prevzeti_MA")
         Prevzeti_MA_string = Prevzeti_MA.ToString("yyyy-MM-dd hh:mm:ss")
         If (dt.Select("Manjkajoči_MA = '" & Prevzeti_MA_string & "'").Length = 0) Then
            MsgBox("OK")
         End If
      Next dt2_row

Do you get an error with that and if you do, which error and on which line.

mrUR 27-Jul-15 14:02pm

I modified it a little because i want to compare Datum_konca (in dt) and Datum_prevzema (in dt2)... But row comparison is still not ok :/ I also tried "If (dt.Select("Datum_konca < '" & Datum_prevzema_string..."

Dim Datum_prevzema As Date
Dim Datum_prevzema_string As String

For Each dt2_row As System.Data.DataRow In dt2.Rows
Datum_prevzema = dt2_row("Datum_prevzema")
Datum_prevzema_string = Datum_prevzema.ToString("yyyy-MM-dd hh:mm:ss")
If (dt.Select("Datum_konca = '" & Datum_prevzema_string & "'").Length = 0) Then
MsgBox("OK")
End If
Next dt2_row

Wendelius 27-Jul-15 14:06pm

Using debugger, is the value inside Datum_prevzema_string looking as it should?

mrUR 27-Jul-15 14:58pm

Yes Datum_prevzema_string is looking as it should

Wendelius 27-Jul-15 15:03pm

The only thing that comes in mind is that try using > instead of <, does the select then return rows? I'm unable to assist much more since I don't have the same sources and data you have so the best way to solve this to the end is to play with the debugger and investigate all variables and test different variations.

mrUR 27-Jul-15 15:36pm

Well you really helped me more than enough already. But just to tell i tried all that combinations with < and > andno luck.

Right now i tried the code you wrote yesterday and it's working exactly as i want to, the only problem is that dt and dt2 don't have the same number of rows (and at that point program throws an error "There is no row at position...")

For counter As Integer = 1 To dt.Rows.Count

Dim Datum_konca As Date = dt.Rows(counter)(1).ToString
Dim Datum_prevzema As Date = dt2.Rows(counter)(1).ToString

If Datum_konca < Datum_prevzema Then
MsgBox("OK")
Else
MsgBox("NI OK")
End If
Next counter

Is it possible that in dt2 where no MA from dt is present, we gave a "null" value or something, so that program doesn't give an error

Wendelius 27-Jul-15 15:47pm

I guess it would be possible to have a null row which would be generated at the time when the data table is filled but then again you should be able trust that each and every row is in the same order in both data tables. I'm afraid that is going to cause trouble.

This is the reason why I suggested that you use select to test if desired rows exist in another table.

One question, do you have two separate columns in both data tables? I mean that is the date in separate column than the MA... values?

mrUR 27-Jul-15 16:11pm

I posted answer as a solution so you can see my datatables :)

Wendelius 28-Jul-15 1:34am

It looks like there's still apostrophes left from the time we compared the string field. Try removing them or better yet replace with #

If (dt.Select("Datum_konca < #" & Datum_prevzema_string & "#").Length = 0) Then

mrUR 28-Jul-15 2:11am

Still not the results i want, when comparing "Datum_konca" and "Datum_prevzema"... What exactly does # sign do in our case?

Wendelius 28-Jul-15 2:17am

It identifies that the string is not a string but a date. You can try with more simple date format

Prevzeti_MA_string = Prevzeti_MA.ToString("dd/MM/yyyy")

mrUR 28-Jul-15 2:36am

I tried that before, but no difference.

What bothers me is that results are all mixed up - for example first row i get returns msgbox "NI OK", although dt2(Datum_prevzema) is bigger than dt(Datum_konca) and it should return "OK"....

Wendelius 28-Jul-15 2:42am

That could happen if the date and month get switched. To ensure that everything else is correct, try the comparison with the MA field and check that it works (remembering that it's a string comparison). If it does and you see correct amounts in Count then switch back working with the date.

mrUR 28-Jul-15 14:28pm

I've managed to deal with the problem, so my code is now working. Thank you Mika once again for all your help :)

Wendelius 29-Jul-15 2:22am

That's great news! :)