Return Value = 0, should be newly inserted primary key

Question

Post reply

Return Value = 0, should be newly inserted primary key

geedeearr

SSC Eights!

Points: 800
More actions
July 20, 2009 at 9:45 pm

#202693

Hi All,
I'm extremely new at Powershell and not even close to an expert in ADO.NET, but I'm learning.
In the following script, the Insert statement works, but the return value of the newly inserted Primary Key is not.
Would someone be so kind to point out what I'm missing?
Thank you.
gdr
# ====================================================================================================
# Win32_ComputerSystem.ps1
# =====================================================================================================
param (
[string]$ComputerName = "MyRemoteComputer"
,[int]$NewCompPKID
)
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null
$CompSys = get-wmiobject -class "Win32_ComputerSystem" -namespace "root\CIMV2" -computername $ComputerName
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=MyServer; Database=MyDatabase; Integrated Security=true"
$conn.Open()
foreach ($property in $CompSys) {
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "INSERT INTO ComputerInformation(FullComputerName, `
ComputerDescription, `
ComputerSystemType, `
ComputerManufacturer, `
ComputerModel, `
NumberProcessors, `
TotalPhysicalMemory, `
CompInfoEntryDate)
VALUES (@Name, `
@ComputerDescription, `
@ComputerSystemType, `
@ComputerManufacturer, `
@ComputerModel, `
@NumberProcessors, `
@TotalPhysicalMemory, `
@CompInfoEntryDate); `
SET @NewCompPKID = SCOPE_IDENTITY()";
$cmd.Connection = $conn
$CompInfoEntryDate = Get-Date
$cmd.Parameters.AddWithValue("@Name", $property.Name) | Out-Null
$cmd.Parameters.AddWithValue("@ComputerDescription", $property.Description) | Out-Null
$cmd.Parameters.AddWithValue("@ComputerSystemType", $property.SystemType) | Out-Null
$cmd.Parameters.AddWithValue("@ComputerManufacturer", $property.Manufacturer) | Out-Null
$cmd.Parameters.AddWithValue("@ComputerModel", $property.Model) | Out-Null
$cmd.Parameters.AddWithValue("@NumberProcessors", [Int32]$property.NumberOfProcessors) | Out-Null
$cmd.Parameters.AddWithValue("@TotalPhysicalMemory", [Int64]$property.TotalPhysicalMemory) | Out-Null
$cmd.Parameters.AddWithValue("@CompInfoEntryDate", $CompInfoEntryDate) | Out-Null
$cmd.Parameters.AddWithValue("@NewCompPKID", "Int").Direction = [System.Data.ParameterDirection]::Output
$cmd.ExecuteNonQuery() | Out-Null
$NewCompPKID = $cmd.Parameters.("@NewCompPKID").Value
}
$conn.Close()
Return $NewCompPKID

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

I've never used PowerShell but I'm interested in it... anyone got an idea on this problem? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

geedeearr SSC Eights! Points: 800 More actions · Answer 2

Wow, I forgot all about this.

Turns out it was a very easy fix. I changed the Output parameter from AddWithValue to Add (not certain that that really did anything) but I also made the notated changed below:

$cmd.Parameters.Add("@NewCompID", [System.Data.SqlDbType]"Int").Direction = [System.Data.ParameterDirection]::Output

$cmd.Connection = $conn

# Execute Stored Procedure

$cmd.ExecuteNonQuery() | Out-Null

--#$NewCompID = $cmd.Parameters.("@NewCompID").Value ---- original

$NewCompID = $cmd.Parameters["@NewCompID"].Value

Thanks.

gdr