Sql adapter update. Primary key constraint.

Ask your Windows 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.
Locked
User avatar
dan.potter
Posts: 709
Joined: Wed Oct 08, 2014 6:02 am

Sql adapter update. Primary key constraint.

Post by dan.potter » Thu Dec 28, 2017 8:12 am

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)

User avatar
jvierra
Posts: 13507
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Sql adapter update. Primary key constraint.

Post by jvierra » Thu Dec 28, 2017 8:21 am

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
Joined: Wed Oct 08, 2014 6:02 am

Re: Sql adapter update. Primary key constraint.

Post by dan.potter » Fri Dec 29, 2017 6:40 am

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

User avatar
jvierra
Posts: 13507
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Sql adapter update. Primary key constraint.

Post by jvierra » Fri Dec 29, 2017 7:22 am

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
Joined: Wed Oct 08, 2014 6:02 am

Re: Sql adapter update. Primary key constraint.

Post by dan.potter » Fri Dec 29, 2017 7:37 am

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.

Code: Select all

$defaultprops = 1..14 | %{ $row.psobject.properties.name | select -Index $_}
$expanded = 15..40 | %{ $row.psobject.properties.name | select -Index $_ }

$defaultprops | %{

$p = $_
$value = $volume.$_
$t = $row.$p.gettype().name

switch($t){

Int32{ $row.$p = [Decimal]$value}
String{ $row.$p = $value}
Decimal{ $row.$p = [Decimal]$value}
Boolean{ $row.$p = [boolean]$value}
NCController{ $row.$p = $value}

}

}

User avatar
jvierra
Posts: 13507
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Sql adapter update. Primary key constraint.

Post by jvierra » Fri Dec 29, 2017 8:00 am

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.

Locked