Im getting this error in LINQ query

Help Activities
1

how to fix this issue .. it was working orchestrator.. now it is not working

image
image1327×176 10.1 KB

i have modified the column names with numbers - (From row In io_dt_ExtractedReport.AsEnumerable() Let r0=DateTime.ParseExact(row(0).ToString.Trim,“MM/dd/yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture) Let r1=row(1).ToString.Trim Let r2=if(string.IsNullOrEmpty(row(2).ToString),Nothing,CDbl(row(“Fiscal_Year”).ToString)) Let r3=row(3).ToString.Trim Let r4=row(4).ToString.Trim Let r5=row(5).ToString.Trim Let r6=row(6).ToString.Trim Let r7=row(7).ToString.Trim Let r8=row(8).ToString.Trim Let r9=row(9).ToString.Trim Let r10=row(10).ToString.Trim Let r11=if(string.IsNullOrEmpty(row(11).ToString),nothing,CDbl(row(11).ToString)) Let r12=if(row(12).ToString.Trim=“”,Nothing,DateTime.ParseExact(DateTime.ParseExact(row(12).ToString.Split(" “c).ToArray(0).Trim,{“M/d/yyyy”,“MM/dd/yyyy”},System.Globalization.CultureInfo.InvariantCulture).tostring(“dd.MM.yyyy 00:00:00”).Split(” “c).ToArray(0).Trim,“dd.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture)) Let r13=if(string.IsNullOrEmpty(row(13).ToString),Nothing,CDbl(row(13).ToString)) Let r14=if(string.IsNullOrEmpty(row(14).ToString),Nothing,CDbl(row(14).ToString)) Let r15=if(string.IsNullOrEmpty(row(15).ToString),Nothing,CDbl(row(15).ToString)) Let r16=if(String.IsNullOrEmpty(row(16).ToString),New Nullable(of DateTime),DateTime.ParseExact(DateTime.ParseExact(row(16).ToString.Split(” “c).ToArray(0).Trim,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture).tostring(“dd.MM.yyyy”).Split(” “c).ToArray(0).Trim,“dd.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture)) Let r17=if(string.IsNullOrEmpty(row(17).ToString),New Nullable(of DateTime),DateTime.ParseExact(DateTime.ParseExact(row(17).ToString.Split(” “c).ToArray(0).Trim,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture).tostring(“dd.MM.yyyy”).Split(” “c).ToArray(0).Trim,“dd.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture)) Let r18=if(string.IsNullOrEmpty(row(18).ToString),New Nullable(of DateTime),DateTime.ParseExact(DateTime.ParseExact(row(18).ToString.Split(” “c).ToArray(0).Trim,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture).tostring(“dd.MM.yyyy”).Split(” “c).ToArray(0).Trim,“dd.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture)) Let r19=if(string.IsNullOrEmpty(row(19).ToString),Nothing,CDbl(row(19).ToString)) Let r20=row(20).ToString.Trim Let r21=row(21).ToString.Trim Let r22=row(22).ToString.Trim Let r23=row(23).ToString.Trim Let r24=if(string.IsNullOrEmpty(row(24).ToString),Nothing,CDbl(row(24).ToString)) Let r25=row(25).ToString.Trim Let r26=row(26).ToString.Trim Let r27=if(string.IsNullOrEmpty(row(27).ToString),”“,row(27).ToString) Let r28=if(string.IsNullOrEmpty(row(28).ToString),”“,row(28).ToString) Let r29=if(string.IsNullOrEmpty(row(29).ToString),”“,row(29).ToString) Let r30=if(string.IsNullOrEmpty(row(30).ToString),”“,row(30).ToString) Let r31=if(string.IsNullOrEmpty(row(31).ToString),”“,row(“Counter”).ToString) Let r32=row(32).ToString.Trim Let r33=row(33).ToString.Trim Let r34=row(34).ToString.Trim Let r35=if(string.IsNullOrEmpty(row(35).ToString),”“,row(35).ToString) Let r36=row(36).ToString.Trim Let r37=if(string.IsNullOrEmpty(row(37).ToString),”“,row(37).ToString) Let r38=row(38).ToString.Trim Let r39=if(string.IsNullOrEmpty(row(39).ToString),”",row(39).ToString) Let r40=row(40).ToString.Trim Let ra = New Object(){r0,r1,r2,r3,r4,r5,r6,r7,r8,r9,r10,r11,r12,r13,r14,r15,r16,r17,r18,r19,r20,r21,r22,r23,r24,r25,r26,r27,r28,r29,r30,r31,r32,r33,r34,r35,r36,r37,r38,r39,r40} Select dt_RPATable.Rows.Add(ra)).CopyToDataTable()

2

@Sathish_Kumar5,

There could be due to this code

row(12).ToString.Split(" "c).ToArray(0).Trim

.ToArray(0) is invalid VB.NET syntax. You’re probably trying to get the first element of the array, which you can get by this code

row(12).ToString.Split(" "c)(0)

Try changing you query as per this.

3

Thank you for the reply, i did changed it but still getting the same error!

image
image1914×248 16.4 KB

image
image1267×207 11.6 KB

4

Hi, @Sathish_Kumar5

The error means your values don’t match the DataTable column types. Check that each value in your array matches the column’s expected type (String, Double, DateTime, etc.)otherwise, it won’t allow the assignment. Fix any mismatched field in your mapping and the issue will go away

5

if possible could you pls share some sample code.. i tried it didn’t work for me!

6

In general it is recomended to condense LINQ statements, also to avoid side effects

we would question several parts

  • RowArray construction with 40 items
  • all Split acrobatics
  • Double Parsing of Dates → ParseExact(Of a ParseExact…toString) construct
  • … and more

However with reference to the Docu of ParsExact Method the Signatures for using a format Array are:

grafik
grafik391×546 14.5 KB

taken form DateTime.ParseExact Method (System) | Microsoft Learn

And we can see that 4 parameters are needed.

On an isolated statement, we can also verify

Failing:

grafik
grafik1411×283 94.1 KB

Passing:

grafik
grafik1015×292 48.7 KB

1 Like
7

Thanks for the effort man.. but facing one more issue while doing this, when rows are empty - Let r12=if(row(12).ToString.Trim=“”,Nothing,DateTime.ParseExact(DateTime.ParseExact(row(12).ToString.Split(" “c).ToArray(0).Trim,{“M/d/yyyy”,“MM/dd/yyyy”},System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None).tostring(“dd.MM.yyyy 00:00:00”).Split(” "c).ToArray(0).Trim,“dd.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None)) - it is returning as Write Range Workbook: Unable to set cell value to 01/01/0001 00:00:00 - so any fix for this pls?

8

We expect this and other issues as we also mentioned:

We do not have sufficient information about the overall goal and sample data. Therefore, we can only provide general assistance. At first glance, however, there are indications that the implementation deviates from typical expectations. For example, we initially pointed out the questionable double parsing with ParseExact and other issues.

Here and in other parts, it appears that the default value of data types is being misjudged in its use. Which is why we also refer to

like a String set to Nothing results to “” (Empty String), DateTime set tonothing results to the Min Date (01/01/0001 00:00:00) …etc.

In such cases, it is often helpful to look at the individual lines of code in isolation, examine them further in prototyping, and optimize them.