June 6, 2018 at 5:16 pm #101933
I'm working with DataTable.
I'm bringing my data from SQL Server Table, I need to iterate through some rows in the table based on specific criteria and modify records accordingly.
The table has multiple primary key columns.
I know the syntax for one primary key column but not multiple
For simplicity, I have my syntax below
can anyone please tell me how to write the syntax for multiple primary key column?
Here is my script:
[System.Data.DataTable]$dtGL = New-Object System.Data.DataTable("GL") #Schemas $dtGL.Columns.Add("Account", "String") | Out-Null $dtGL.Columns.Add("Property", "String") | Out-Null $dtGL.Columns.Add("Date", "DateTime") | Out-Null $dtGL.Columns.Add("Amount", "Decimal") | Out-Null #Records $dtGL.Rows.Add('00001','1000','1/1/2018','185') | Out-Null $dtGL.Rows.Add('00001','1000','1/2/2018','486') | Out-Null $dtGL.Rows.Add('00001','1001','1/1/2018','694') | Out-Null $dtGL.Rows.Add('00001', '1001', '1/2/2018', '259') | Out-Null #Set Primary Key on Columns: Account|Property|Date [System.Data.DataColumn] $KeyColumn = New-Object System.Data.DataColumn $KeyColumn = $dtGL.Columns["Account"] $KeyColumn = $dtGL.Columns["Property"] $KeyColumn = $dtGL.Columns["Date"] $dtGL.PrimaryKey = $KeyColumn
If you run it on your end you should get the same error that I'm getting:
New-Object : Cannot find type [System.Data.DataColumn]: verify that the assembly containing this type is loaded.
At C:\Users\jabou-ghannam\OneDrive – Island Hospitality\Island Hospitality\PowerShell\Begining PowerShell Scripting for Developers\Working With
+ ... .Data.DataColumn] $KeyColumn = New-Object System.Data.DataColumn
+ CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
+ FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
June 6, 2018 at 5:38 pm #101935
So... you're -really- just using .NET, here, not PowerShell per se. StackOverflow.com might net you a happier answer :).
Perusing https://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey(v=vs.110).aspx, it would appear you can pass an array.
June 6, 2018 at 5:53 pm #101938
Found the answer after multiple trial and error 🙂
[System.Data.DataColumn]$KeyColumn = ($dtGL.Columns["Account"],$dtGL.Columns["Property"],$dtGL.Columns["Date"]) $dtGL.PrimaryKey = $KeyColumn
But now I need to query the datatable and I know how to do it in .NET, I just can't figure out how to do it in Powershell 🙁
[String]$RowsToFind = '00001', '1001', '01/01/2018' $FoundRows = $dtGL.Rows.Find($RowsToFind) $FoundRows
I'm getting the following error
Exception calling "Find" with "1" argument(s): "Expecting 3 value(s) for the key being indexed, but received 1 value(s)." At C:\Users\myuser\OneDrive - mycompany\companyname\PowerShell\Samples\Working With DataTable.ps1:38 char:1 + $FoundRows = $dtGL.Rows.Find($RowsToFind) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) , MethodInvocationException + FullyQualifiedErrorId : ArgumentException
June 6, 2018 at 6:00 pm #101944
I mean, as I'm reading it, that should work.
I might have done:
$RowsToFind = @('00001', '1001', '01/01/2018')
Instead. That's how you'd normally do an array in PowerShell. At least how I'd do it. But if it's not working, then I dunno. You might ping Robert Cain since you're watching his course :).
June 6, 2018 at 6:14 pm #101945
For what it's worth I watched your courses too on Pluralsight 🙂
I posted my question on Stackoverflow
June 6, 2018 at 6:50 pm #101950
$FoundRows = $dtGL.Rows.Find(@('00001', '1001', '01/01/2018'))
I had to pass it this way
It did work Don 🙂
thank you so much
June 6, 2018 at 7:06 pm #101951
how do I mark your answer as an "answer"?
June 6, 2018 at 7:14 pm #101954
The forums don't offer that.
You must be logged in to reply to this topic.