Sql adapter update. Primary key constraint.

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 6 years and 2 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
dan.potter
Posts: 709
Last visit: Wed Nov 14, 2018 11:39 am

Sql adapter update. Primary key constraint.

Post by dan.potter »

I want to perform a datatable update in the shell and I'm unsure how to automatically choose the row similar to what the datagridview does. The eventual table has forty columns and I don't want to loop through each.

What I thought I could do is retrieve the sql table as a datatable. Form a new datatable object from a different source with all matching properties.

Then $sqladapter.update($newdt)
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Sql adapter update. Primary key constraint.

Post by jvierra »

Not enough information to understand what you are trying to ask.

The DGV just displays the rows attached to the DataSource. There is one table row attached to each grid row. Nothing is ever chosen.

To update a specific row in a DataTable you have to have a primary key.
User avatar
dan.potter
Posts: 709
Last visit: Wed Nov 14, 2018 11:39 am

Re: Sql adapter update. Primary key constraint.

Post by dan.potter »

I guess I naively thought I could replace a datatable with a matching datatable. I did figure an easy want to loop through all the properties without insanely long update or insert statements.

Though I'm stuck on this error. Any idea what's going on here?

PS C:\Users> $row.TotalSize.gettype()

IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Decimal System.ValueType


PS C:\Users> $volume.totalsize.gettype()

IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Decimal System.ValueType


PS C:\Users> $row.TotalSize = $volume.TotalSize
Exception setting "TotalSize": "Unable to cast object of type 'System.Management.Automation.PSObject' to type 'System.IConvertible'.Couldn't store <1073741824> in TotalSize Column. Expected
type is Decimal."
At line:1 char:1
+ $row.TotalSize = $volume.TotalSize
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
+ FullyQualifiedErrorId : CatchFromBaseAdapterSetValue
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Sql adapter update. Primary key constraint.

Post by jvierra »

There is no way to figure out what you are trying to do. What is $row? Where did it come from? What is it you are trying to do?
User avatar
dan.potter
Posts: 709
Last visit: Wed Nov 14, 2018 11:39 am

Re: Sql adapter update. Primary key constraint.

Post by dan.potter »

Unfortunately a heavily redacted $row would be as meaningfull as null :D

$row is a row from a sql table, $volume is an object from netapp storage. All types in sql and the object match but some are trying to convert for unknown reasons. To make it more confusing some of the decimals, booleans work without issue.

This is how I'm getting around it.
  1. $defaultprops = 1..14 | %{ $row.psobject.properties.name | select -Index $_}  
  2.     $expanded = 15..40 | %{ $row.psobject.properties.name | select -Index $_ }
  3.    
  4.     $defaultprops | %{
  5.        
  6.         $p = $_
  7.         $value = $volume.$_
  8.         $t = $row.$p.gettype().name
  9.        
  10.             switch($t){
  11.            
  12.                 Int32{ $row.$p = [Decimal]$value}
  13.                 String{ $row.$p = $value}
  14.                 Decimal{ $row.$p = [Decimal]$value}
  15.                 Boolean{ $row.$p = [boolean]$value}
  16.                 NCController{ $row.$p = $value}
  17.            
  18.             }
  19.        
  20.     }
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Sql adapter update. Primary key constraint.

Post by jvierra »

Wrong type: "Unable to cast object of type 'System.Management.Automation.PSObject' "?

"Couldn't store <1073741824> in TotalSize Column. Expected type is Decimal."

PsObject is not a decimal type.

Without code it is impossible to know what you are doing or why it is wrong.
This topic is 6 years and 2 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked