How to Handle Error In Linq

Hi, I wanna ask question about linq, I have linq to filter datatable from list, and in the linq I will convert a Date to string, with specific format, but there is a chance in the datatable format is not a Date, how can I skip that data and continue for the remaining data.
I use this linq
(From row In dtMasterJadwal.AsEnumerable()
Where listTanggalNotFound.Contains(DateTime.ParseExact(row(“Date1”).ToString.Split(" “c).first, “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”))
Select row).Select(Function(row) dtRegisterRoya.Clone().LoadDataRow(New Object() {”“,row(“No. ILS”), row(“Nama Debitur”), DateTime.ParseExact(row(“Date2”).ToString.Split(” “c).first, “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”), DateTime.ParseExact(row(“Date3”).ToString.Split(” "c).first, “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”), row(“Operator”)}, False)).CopyToDataTable()

Thank you :slight_smile:

Hello

Can you provide a sample data, I need to test it.

thank you
Luciano

1 Like

Hi @Hendaryie_Tjoeng

I have made changes in your LINQ Expression, check the below one,

(From row In dtMasterJadwal.AsEnumerable() 
Where DateTime.TryParseExact(row("Date1").ToString().Split(" "c).First(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, Nothing) AndAlso DateTime.TryParseExact(row("Date2").ToString().Split(" "c).First(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, Nothing) AndAlso DateTime.TryParseExact(row("Date3").ToString().Split(" "c).First(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, Nothing) 
Select dtRegisterRoya.Clone().LoadDataRow(New Object() {"", row("No. ILS"), row("Nama Debitur"), DateTime.ParseExact(row("Date2").ToString().Split(" "c).First(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy"), DateTime.ParseExact(row("Date3").ToString().Split(" "c).First(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy"), row("Operator")}, False)).CopyToDataTable()

Hope it helps!!

1 Like

DummyData.xlsx (10.2 KB)
This is the dummy data, thank you

Thank you for reply, I will test it, have a nice day :slight_smile:

1 Like

I want to format the date to string, but when I add .ToString the linq is error, how I can format the date to string format?

Do you want to convert the Date1, Date2 and Date3 columns to String format. Am I right… @Hendaryie_Tjoeng

You shared the Input excel file share the expected output file.

1 Like
(From row In dtMasterJadwal.AsEnumerable() Where listTanggalNotFound.Contains(If(String.IsNullOrEmpty(row("Date1").ToString().Split(" "c).FirstOrDefault()), "", DateTime.ParseExact(row("Date1").ToString().Split(" "c).FirstOrDefault(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy"))) Select row).Select(Function(row) dtRegisterRoya.Clone().LoadDataRow(New Object() {"", row("No. ILS"), row("Nama Debitur"), If(String.IsNullOrEmpty(row("Date2").ToString().Split(" "c).FirstOrDefault()), "", DateTime.ParseExact(row("Date2").ToString().Split(" "c).FirstOrDefault(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy")), If(String.IsNullOrEmpty(row("Date3").ToString().Split(" "c).FirstOrDefault()), "", DateTime.ParseExact(row("Date3").ToString().Split(" "c).FirstOrDefault(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy")), row("Operator") }, False)).CopyToDataTable()

Please try this code i have try to add the if before each Parse, i don’t know if you need only For date2 and date3 but i have done it for all.

i couldn’t testit because it ask for a variable listTanggalNotFound which i don’t know the type and for what it is in the end

Please let me know

1 Like

Thank you very much for the help, I use regex to validate if the string is a valid date format or not.
(From row In dtMasterJadwal.AsEnumerable()
Where listTanggalNotFound.Contains(
If(System.Text.RegularExpressions.Regex.IsMatch(row(“Tgl Ambil Dok”).ToString().Split(" “c)(0), “^(0[1-9]|1[0-2])/(0[1-9]|[12][0-9]|3[01])/([0-9]{4})$”),
DateTime.ParseExact(row(“Tgl Ambil Dok”).ToString().Split(” “c)(0), “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”),
row(“Tgl Ambil Dok”).ToString().Split(” “c)(0)
)
)
Select dtRegisterRoya.Clone().LoadDataRow(New Object() {
“”,
row(“No. ILS”).ToString,
row(“Nama Debitur”).ToString,
If(System.Text.RegularExpressions.Regex.IsMatch(row(“Tgl Lunas KPR”).ToString().Split(” “c)(0), “^(0[1-9]|1[0-2])/(0[1-9]|[12][0-9]|3[01])/([0-9]{4})$”),
DateTime.ParseExact(row(“Tgl Lunas KPR”).ToString().Split(” “c)(0), “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”),
row(“Tgl Lunas KPR”).ToString
),
If(System.Text.RegularExpressions.Regex.IsMatch(row(“Tgl Ambil Dok”).ToString().Split(” “c)(0), “^(0[1-9]|1[0-2])/(0[1-9]|[12][0-9]|3[01])/([0-9]{4})$”),
DateTime.ParseExact(row(“Tgl Ambil Dok”).ToString().Split(” "c)(0), “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”),
row(“Tgl Ambil Dok”).ToString
),
row(“Operator”).ToString
}, False)).CopyToDataTable()

Hi, thank you for the reply, I already test your linq and its work, but at the end I use regex to determine if the string is valid date or not. Have a nice day :slight_smile:

1 Like