CODEWITHSUNDEEP
powershelldatatable
BlueBird
BlueBird

Reputation: 1466

Powershell Datatable.Find Method

I am trying to work with DataTables in powershell, more specifically I am trying to check to see if a row with a certain value exists in the datatable.

Here is the method I am using to create the dataTable.

function GetPropertyIDs()
{
    write-host "Retrieving all Property ID's from $DatabaseInstance.$DatabaseName..." -NoNewline

    $cn = new-object system.data.SqlClient.SqlConnection("Data Source=$DatabaseInstance;Integrated Security=SSPI;Initial Catalog=$DatabaseName");
    $cn.Open()

    $cmd = new-Object System.Data.SqlClient.SqlCommand("Select Distinct CAST(ID as varchar(5)) as ID, Name, Code from TableName Order by ID", $cn)
    $cmd.CommandType = [System.Data.CommandType]'Text'

    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $cmd 
    $dataTable = New-Object System.Data.DataTable

    $adapter.Fill($dataTable) | out-null

    $cn.Close() | out-Null
    $cn.Dispose()

    $count = $dataTable.rows.count;
    write-host "$count records retrieved."

    #Set Primary Key
    $pk = $dataTable.Columns["ID"]
    $dataTable.PrimaryKey = $pk

    return $dataTable
}

Here is the code where I am trying to check if a specific value exists in the datatable...

$PropertyIDs = GetPropertyIDs
foreach($PropertySite in $PropertySites)
{
    $id = ([System.String] $PropertySite).Replace("$SiteCollectionURL/property/", "")
         $found = $PropertyIDs.DefaultView.Find($id)
}

Currently I am getting an error:

"You cannot call a method on a null-valued expression."

I have also tried this with no luck either:

$found = $PropertyIDs.Rows.Contains($id)

Upvotes: 1

Views: 9416

Answers (1)

BlueBird
BlueBird

Reputation: 1466

It turns out that this was being caused by powershell unravelling the datatable into a collection of rows instead of a datatable like it was defined as, therefor the primary key which is required for .Rows.Contains(...) was not available and thus the error.

By adding the a ',' at the end of the GetPropertyIDs() method above I was able to get powershell to return a datatable with the primary keys intact. For Example:

return ,$dataTable

I ended up using the contains method because it returns a boolean value which is what I was looking for as a test...

Some References:

http://powershellcommunity.org/Forums/tabid/54/aft/6359/Default.aspx

Upvotes: 1

Related Questions