Return Value = 0, should be newly inserted primary key

  • 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.


    # ====================================================================================================

    # Win32_ComputerSystem.ps1

    # =====================================================================================================

    param (

    [string]$ComputerName = "MyRemoteComputer"



    [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"


    foreach ($property in $CompSys) {

    $cmd = New-Object System.Data.SqlClient.SqlCommand

    $cmd.CommandText = "INSERT INTO ComputerInformation(FullComputerName, `

    ComputerDescription, `

    ComputerSystemType, `

    ComputerManufacturer, `

    ComputerModel, `

    NumberProcessors, `

    TotalPhysicalMemory, `


    VALUES (@Name, `

    @ComputerDescription, `

    @ComputerSystemType, `

    @ComputerManufacturer, `

    @ComputerModel, `

    @NumberProcessors, `

    @TotalPhysicalMemory, `

    @CompInfoEntryDate); `


    $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



    Return $NewCompPKID

  • 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)

  • 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



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

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