Page 1 of 1

Sql adapter update. Primary key constraint.

Posted: Thu Dec 28, 2017 7:12 am
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)

Re: Sql adapter update. Primary key constraint.

Posted: Thu Dec 28, 2017 7:21 am
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.

Re: Sql adapter update. Primary key constraint.

Posted: Fri Dec 29, 2017 5:40 am
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

Re: Sql adapter update. Primary key constraint.

Posted: Fri Dec 29, 2017 6:22 am
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?

Re: Sql adapter update. Primary key constraint.

Posted: Fri Dec 29, 2017 6:37 am
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.     }

Re: Sql adapter update. Primary key constraint.

Posted: Fri Dec 29, 2017 7:00 am
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.