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.Add("@NewCompPKID", [System.Data.SqlDbType]"Int").Direction = [System.Data.ParameterDirection]::Output
$cmd.ExecuteNonQuery() | Out-Null
$NewCompPKID = $cmd.Parameters.("@NewCompPKID").Value } 

$conn.Close()
Write-Host "------"
Return $NewCompPKID

I think the problem is this line:

$NewCompPKID = $cmd.Parameters.("@NewCompPKID").Value

should be

$NewCompPKID = $cmd.Parameters["@NewCompPKID"].value

Thank you, but that errors with this message:

Cannot convert value "" to type "System.Int32". Error: "Object cannot be cast from DBNull to other types.".

So it's still not returning the PK?

I have found that
$NewCompPKID = [Int32]$cmd.ExecuteScalar()
will return the proper PK and the Output parameter is not needed.

I have also changed
SET @NewCompPKID = SCOPE_IDENTITY()
to
SELECT SCOPE_IDENTITY()
But that doesn't change the error(s) though.

Thank you again.

gdr

Here's what I did to test and this works:

--Create test table with identity column in SQL Server Management Studio

CREATE TABLE [dbo].[test](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [c1] [nchar](10) NOT NULL
);

# Execute the following Powershell commands from Powershell
#My instance name is SQLEXPRESS an database name is dbutility
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$env:computername\SQLEXPRESS; Database=dbautility; Integrated Security=true"
$conn.Open()

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "INSERT INTO Test(c1) VALUES (@Name); SET @NewCompPKID = SCOPE_IDENTITY();"
$cmd.Connection = $conn
$Name = 'test'
$cmd.Parameters.AddWithValue("@Name", $Name)
$cmd.Parameters.Add("@NewCompPKID", [System.Data.SqlDbType]"Int").Direction = [System.Data.ParameterDirection]::Output
$cmd.ExecuteNonQuery()
$cmd.Parameters["@NewCompPKID"].Value
$conn.Close()

Thank you.
Sorry for the delay in getting back to this. I've had other "fires" and was out of "computer contact" for 4 glorious days.
I see the differences and will give this a try at first chance. Thanks again.

gdr