Overwriting Table Data While Looping Through Same Table

Help Studio
, ,
1

I have seen some things on here saying that this is not possible but I swear I have done it before. However, now I am having issues.

I would like to use a For Loop to go through a Datatable and overwrite the data in each row of one particular column with a new value.

I have tried “myDT.Rows(index)(16) = newValue” but I get an “Exception has been thrown by the target of an invocation”.

Please help! Thanks!

2

Kindly check before your assign if column 16 exists in data table
Use varDT1.Columns.Count

If this is not an issue then let me know. We can think through something else.

3

Yes it exists, there are 30 columns. I tried using the name of the column as well and that did not work either.

I read somewhere that you can’t update a table that you are also looping through but I swear I have done this before…I just can’t remember how

4

We can always update the value in same table.

5

Do you have any other ideas?

6

@jpreziuso
maybe you are mixing up two things:

for each / for each row loop and changing the sequence e.g. deleting within the loop some rows will fail. We will get the typically sequence changed exception

Using a for each row and change values will work e.g.
grafik

7

One more reason can be if you are assigning the wrong data type of value to the item.
Example : If it is Int and you are passing String

Check if you have preserve format option selected while reading the data

8

@Pravin_Patil1 @ppr

Capture
Capture330×732 22.2 KB

I am using a For Each Row, I misspoke. The Datatable is coming from the output of a SQL query. I do not see a “preserve format” option

9

Before updating or assigning the value use below check

myDT.Rows(index)(16) = DBNull.Value or
If yes then skip
or else
Use assign update “myDT.Rows(index)(16) = newValue”

If it works then we will concentrate on updating the row item having DBNull value

10

I am slightly confused. Go through the table and check if the value is NULL?

11

As you are reading it from database where DB can have DBNull type.
So if you are trying to assign the value to DBNull then it will not allow you to update.

Reason being you can have check if there is db null value

13

It turns out that the values in each row are NOT null for this column

14

Can you try using row directly.

row(16).ToString inside for each row

15

This does not seem to be working either. Same “target of invocation” exception