Ad

Compare Date Fields Into Two Datatables

- 1 answer

I have two DataTables and I need to compare three columns of them.

The first DataTable is created in vb.net with this code:

Using DT1 As DataTable = New DataTable
    With DT1
        .Columns.Add("Type", GetType(String))
        .Columns.Add("In_Date", GetType(Date))
        .Columns.Add("Out_Date", GetType(Date))
        .Columns.Add("RowNum", GetType(Byte))
        .PrimaryKey = {.Columns("RowNum")}
    End With
'...

The other DataTable is created by querying a MySql Database and so all fields are strings.

The query returns an empty string ('') when a date field is NULL:

SELECT IdEv, Type, 
       IF(In_Date='0000-00-00' 
          OR In_Date IS NULL,'',DATE_FORMAT(In_Date, '%d/%m/%Y')
          ) AS In_Date, 
       IF(Out_Date='0000-00-00' 
          OR Out_Date IS NULL,'',DATE_FORMAT(Out_Date, '%d/%m/%Y')
          ) AS Out_Date,
       IdLi 
FROM MyTable 
WHERE IdLi = 1

After many failed attempts, now I'm doing this way:

To make fields comparable I run a loop to change all empty strings into date fields to DBNull.Value :

For x As Short = 0 To DT2.Rows.Count - 1
    If DT2.Rows(x)("In_Date") = "" Then DT2.Rows(x)("In_Date") = DBNull.Value
    If DT2.Rows(x)("Out_Date") = "" Then DT2.Rows(x)("Out_Date") = DBNull.Value
Next

then I compare with this code:

If Not DT1.Rows(x)("Type").Equals(DT2.Rows(x)("Type")) OrElse Not _
   If(IsDBNull(DT1.Rows(x)("In_Date")), 0, CDate(DT1.Rows(x)("In_Date"))).Equals(If(IsDBNull(DT2.Rows(x)("In_Date")), 0, CDate(DT2.Rows(x)("In_Date")))) OrElse _
   If(IsDBNull(DT1.Rows(x)("Out_Date")), 0, CDate(DT1.Rows(x)("Out_Date"))).Equals(If(IsDBNull(DT2.Rows(x)("Out_Date")), 0, CDate(DT2.Rows(x)("Out_Date")))) Then

I made some test and it seems to work but I'm afraid of wrong results.

Can you say me if my code does its work and gives me right answers?

Ad

Answer

If You Have Two Tables , You Can Compare Like Below

For x As Short = 0 To DT2.Rows.Count - 1
    If DT1.Select("In_Date='" & DT2.Rows(x)("In_Date") & "'").Length >=1 Then 
    '' Existing 
     Else
     '' Not Existing
   End If
Next
Ad
source: stackoverflow.com
Ad