Powershell Datatable.Find Method

Question

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)

Run $PropertyIDs = GetPropertyIDs; $PropertyIDs -eq $null. Is the result true or false? If false, run this: $PropertySites | % { $_ -eq $null }. Does it return True? The error is saying that you calling the method on a null-object. — Frode F.
– Frode F., Commented May 13, 2013 at 16:25

BlueBird · Accepted Answer · 2013-05-13 18:18:05Z

1

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

answered May 13, 2013 at 18:18

BlueBird

1,4666 gold badges24 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

Powershell Datatable.Find Method

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related