I have 2 datatable.
first datatabe columns: id, startdate, enddate
second datatable columns: id, date
i want to delete row if ids match and secondDt’s date between startdate and enddate.
(All dates type is DateTime)
ids can be more than one for different dates.
How can i use remove data row activity for this?
i trying something like this:
BinarySearch in id lists.
if id found SelectDataRow= secondDt.Select(“[id] = '”+cint(item).ToString+“'”)
if (startdate<=date and enddate>=date)
Remove Data Row Activity using SelectDataRow
you can use either LINQ or Lambda to filter the DT.
Dim dt1 As DataTable = New DataTable() 'Data table 1 initialization
Dim dt1Col As DataColumn() = {New DataColumn("Id"), New DataColumn("StartDate", Type.[GetType]("DateTime")), New DataColumn("EndDate"), Type.[GetType]("DateTime")}
dt1.Columns.AddRange(dt1Col)
Dim dt2 As DataTable = New DataTable() 'Data table 2 initialization
Dim dt2Col As DataColumn() = {New DataColumn("Id"), New DataColumn("Date", Type.[GetType]("DateTime"))}
dt2.Columns.AddRange(dt2Col)
'Filter the rows to delete
Dim filteredRows = From dataRows1 In dt1.AsEnumerable() Join dataRows2 In dt2.AsEnumerable() On dataRows1.Field(Of Integer)("Id") Equals dataRows2.Field(Of Integer)("Id") Where dataRows2.Field(Of DateTime)("Date") >= dataRows1.Field(Of DateTime)("StartDate") AndAlso dataRows2.Field(Of DateTime)("Date") <= dataRows1.Field(Of DateTime)("EndDate") Select dataRows2
'Formatted for better view
'Dim filteredRows = From dataRows1 In dt1.AsEnumerable()
' Join dataRows2 In dt2.AsEnumerable() On dataRows1.Field(Of Integer)("Id") Equals dataRows2.Field(Of Integer)("Id")
' Where dataRows2.Field(Of DateTime)("Date") >= dataRows1.Field(Of DateTime)("StartDate") AndAlso dataRows2.Field(Of DateTime)("Date") <= dataRows1.Field(Of DateTime)("EndDate") Select dataRows2
'Delete the rows from source
For Each drToDelete As DataRow In filteredRows
dt2.Rows.Remove(drToDelete)
Next
for each from which you are comparing the value inside this for each use another for each and have if condition then you can use remove data roe inside of if condition.