🚑 🆘 [FirstAid] Handling of The source contains no DataRows exception

[ppr (Peter)]

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled.

Introduction

Let’s have a look at the following filter data table scenario:

Name	CCode
Tom	US
Charlotte	FR
Seema	IN
Jean	FR

Assignment:

• Filter all rows on a particular Country Code like FR, UK, ES, UK…
• Ensure that all CCode data column values are trimmed
• Ensure that the Filter check is case insensitive

we can implement it e.g. with the help of a LINQ statement:

dtData.AsEnumerable.Where(Function (x) x("CCode").toString.Trim.ToUpper.Equals(strCCFilter.ToUpper)).CopyToDataTable

Visuals

Result and Exception

Success

When the data table will be filtered for “fr” the following result will be received:

Failure

When the data table will be filtered for “uk” a the source contains no DataRows EXCEPTION will be thrown

Reason: when the filter result will be empty, there are no rows for copying it to the resulting data table.

Defensive Handling

Strategy

The main idea for defensive handling is about collecting all filtered rows to a list. Depending on the list count the CopyToDataTable method will be called or the original data table will be cloned

Fixing Actions - LINQ

Variables

For the pre-step result, a variable Result of the datatype: List(of DataRow) will be created

Details

then click OK, OK

LINQ Statement & Assign Change

instead of CopyToDatatable a ToList will be used
The return of the LINQ will be assigned to the Variable: Result

Defensive Check Block

Within an IF Activity following will be modeled:

Condition: Result.Count > 0
Then Branch: dtResult = Result.CopyToDataTable
Else Branch: dtResult = dtData.Clone

dtData is the origin datatable. With the Clone Method an empty data table with the same data column structure of dtData will be created and assigned to dtResult

Visual

Fixing Actions - DataTable.Select Method

Similar to the above-described steps (Fixing Actions - LINQ) a defensive handling can also be
implemented for the DataTable.Select Method approach:

Variables

For the pre-step result, a variable Result of the datatype: List(of DataRow) will be created

Details

then click OK, OK

Assignment

Result = dtData.Select("..YourSelectFilterExpression..").ToList()

Defensive Check Block

Within an IF Activity following will be modeled:

Condition: Result.Count > 0
Then Branch: dtResult = Result.CopyToDataTable
Else Branch: dtResult = dtData.Clone

Visual

dtData is the origin datatable. With the Clone Method an empty data table with the same data column structure of dtData will be created and assigned to dtResult

Conclusion

with the implementation of the above an empty filter result can be handled defensively and a the source contains no DataRows EXCEPTION can be avoided.

Downloads

Find starter help here:
ppr_FirstAid_NoSourceRows.xaml (8.4 KB)

Questions

For questions on your specific case open a new topic and get individual support

[ashwin.ashok ((TheCodingTheory))]

A post that was very much needed in this community, thank you for taking the time to write this for us, we appreciate it!

Kind Regards,
Ashwin A.K

[ptrobot]

Great post @ppr! Just wanted to add that you can also merge it all in one expression if you don’t want to use several activities.

(From i In {0}
	Let matchingRows = dtData.AsEnumerable.Where(Function (x) x("CCode").toString.Trim.ToUpper.Equals(strCCFilter.ToUpper))
	Select If (matchingRows.Count > 0, matchingRows.CopyToDataTable, dtData.Clone)
).First

[jose.ordonez1 (Jose Ordonez)]

Hi Peter!
Thank you for this post. I was really looking for something like that!
Cheers,

[MitheshBolla (Mithesh Reddy Bolla)]

This was cool, But in my scenario in filtering datatable, if i had 2 rows, and those 2 are filtered and my datatable is beoming blank, how to handle this scenario ,

I am deleliting all filtered data, so my filterdatatable is trowing error.

[ptrobot]

If you use my LINQ-expression you should have a an empty data table (with correct headers but no rows.) That is was the dtData.Clone does.

How do you filter your datatable? Could you show us your LINQ-expression?

[ppr (Peter)]

@MitheshBolla @ptrobot

@ppr

Questions

For questions on your specific case open a new topic and get individual support

we would recommend to open a new topic for this discussion