Conditionally delete DataTable Row in powershell?

Question

I am trying to remove a handful of rows from a .NET DataTable using Powershell, based on a list that I have. Here is my condensed code:

Here is my datatable declaration:

$Global:dt = New-Object System.Data.Datatable
$dt.Columns.Add("Name")
$dt.Columns.Add("AccountID")

I know I am populating my datatable correctly. Here is my code where I try and conditionally delete something:

$testname = "John Smith"

foreach ($id in $DeleteIDs)
{
     $dt.Select("Name = '" + $testname + "' AND AccountId = '" + $id + "'").Delete()
}

I may be crazy but I sware this worked for me the first time I tried it. But now I am receiving the following error:

Method invocation failed because [System.Data.DataRow[]] doesn't contain a method named 'Delete'.
At P:\My Documents\Scripts\myLoc\myScript.ps1:152 char:9
+         $dt.Select("Name= '" + $testname+ "' AND AccountId = '" + $id + "' ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

This says System.Data.DataRow does not contain a Delete method. I know this isn't try. When I place a breakpoint and run a $dt | gm I get the following.

 PS C:\WINDOWS\system32>> $dt | gm


   TypeName: System.Data.DataRow

It says System.Data.DataRow[] doesn't contain a delete method, subtle but important difference - you're dealing with an array of DataRows at this point. You need to iterate the collection to get access to the delete method of each object. — arco444
– arco444, Commented Jan 16, 2015 at 16:06

Siavas · Accepted Answer · 2020-06-19 11:41:54Z

2

There are two ways to delete rows from a DataTable:

With $dataTable.Rows.Remove($row) (or RemoveAt()) method, which deletes the row directly
With $row.Delete(), which changes the state of the row to Deleted. After the loop, you must call dataTable.AcceptChanges() to ensure these rows are effectively deleted.

The documentation from Microsoft explaining exactly this can be found here.

edited Jun 19, 2020 at 11:41

answered Jun 19, 2020 at 11:08

Siavas

5,1202 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Johnrad · Accepted Answer · 2015-01-16 20:55:54Z

1

Thanks to @arco444 I was able to pinpoint the issue I was having. And I was not crazy.

My dataset that I am trying to remove from is constantly changing.

At the time it ran correctly, where I searched for $testname there was only 1 instance of that name in the entire dataset.

Today when I ran the script, there where multiple instances of $testname so it took it as an array of rows. The following fixed everything:

foreach ($id in $DeleteIDs)
{
     $rows = $dt.Select("Name = '" + $testname + "' AND AccountId = '" + $id + "'").Delete()

     foreach($row in $rows)
     {
          row.Delete()
     }
}

A variable will store an array of pointers to each row, so I can manually go through each one and delete it.

answered Jan 16, 2015 at 20:55

Johnrad

2,65518 gold badges62 silver badges103 bronze badges

1 Comment

JohnG Over a year ago

In your answer, I'm guessing you shouldn't have the .Delete() on the assignment and you should have a dollar sign on the row.Delete().

Collectives™ on Stack Overflow

Conditionally delete DataTable Row in powershell?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related