Hi,
I have columns in my datatable with 10,000 rows. I would like to change the values of a column
called “Standard” which is “X” (a string) to int32 value i.e. 1. I tried below Linq query but it is giving error “Option strict on disallows implicit conversion from “string” to “boolean””.
dt_tempSql.AsEnumerable().Where(Function(a) a.Field(Of String)(“Standard”).ToString.Replace(“X”,“1”).ToString).CopyToDatatable()
I am using assign activity. Any idea how to resolve this?
Hi @WASEEM_KHAN ,
Is column sequence is same in this case or it’s dynamic ?
If sequence remain same then you can use this
(From r In dtInput Select dtOutput.LoadDataRow(New Object() { r.Field(of Object)(“Standard”)},False)).CopyToDataTable
You can try this as well!!
In_DT (Your Input Datatable)
(From d In In_DT.AsEnumerable()
Let r1 = d.ItemArray.Select(Function (x) x.toString.Replace(“X”, ”1”)).toArray
Select r2 =Out_DT.Rows.Add(r1)).CopyToDataTable
The column has the constant value “X” at some rows which I would like to replace with 1.
I am sorry but I could not understand your solution. You are not replacing “X” value with 1 in your expression.
Sorry i misunderstood your question i thought you want to change column name, now i get it.
it would be great If you could show us, how the column looks like …i.e. sample data.
Try with your query just change string to object
dt_tempSql.AsEnumerable().Where(Function(a) a.Field(Of Object)(“Standard”).ToString.Replace(“X”,“1”).ToString).CopyToDatatable()
OR
(From r In dtInput Select dtOutput.LoadDataRow(New Object() { r.Field(of Object)(“columnName”).ToString.Replace(“X”,“1”) },False) ).CopyToDataTable
The change from string to object does not make difference. It is producing the same error.
For second solution: Should I write it like this:
It gives me error that “No compiled code to run error BC30035:syntax error at line 1”
Instead of using invoke code use directly Assign activity.
To the left just use dt_out and to the right linq query.
For Reference
Thanks for replying. First, the clone method is only copying the structure(i.e. names of column). I used dt.copy afterwards, so that it copies all the column values. Now your query seems to replace “X” with 1 but the problem is, it is updating values in first column not under “Standard” column and all the other column’s value are gone as well. I need complete datatable with only X values replaced by 1. Here is the output:
You need to declare all column in same query just like we declare for first column.
@WASEEM_KHAN - please check , whether this meets your expectation…
My Build Datatable
Invoke Code
dtinput.Select.ToList.ForEach(Sub(Row) Row("Standard") = Row("Standard").tostring.Replace("X","1"))
invoke Code Arguments
Hope this helps…
Hi,
@prasath17 I tried your solution and it’s working. Thanks for your effort. I would also like to replace empty cells with “0”. In my case, the data type of standard column is object. I tried your query for example:
dtinput.Select.ToList.ForEach(Sub(Row) Row(“Standard”) = Row(“Standard”).tostring.Replace(“”,“0”))
but It gives an exception i.e. “Invoke code: Exception has been thrown by the target of an invocation.”
Any solution in this regard?
we can chain replace statements:
Row(“Standard”).tostring.Replace(“X”,“1”).Replace(“”,“0”)
Thanks for replying:). It raised the same exception “Invoke code: Exception has been thrown by the target of an invocation.”
is not coming from the replace chain.
Inspect the used datatable and check the arguments from the invoke code.
The origin replace was working, right?
Yoichi
Anil_G
jast1631
Gokul001
yedukondaluaregala
Jon_Smith
adi.mehare