Unable to update SQL Table

This topic contains 5 replies, has 3 voices, and was last updated by  Mohammed Sabuwala 4 years ago.

  • Author
    Posts
  • #9551

    Mohammed Sabuwala
    Participant

    Hi Don,

    I am going through your CBT nuggets for PowerShell tool making and I am stuck at updating the SQL Table. The function is able to get the computer names fine but just doesn't update the table.

    Keep getting the below error:
    Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near the keyword 'WHERE'."
    At C:\Users\mohammed\Documents\WindowsPowerShell\Modules\MOLDatabase\MOLDatabase.psm1:55 char:9
    + $command.ExecuteNonQuery() | Out-Null
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

    I have not made any changes to your MOLDatabase.psm1.

    I have attached my "MSTools.psm1" script for your reference.

  • #9559

    Don Jones
    Keymaster

    We don't support .PSM1 attachments; you need to change it to a TXT. You can also just paste your SQL query in your message, if you like. You've got an error in the WHERE portion of it.

    When I'm building a SQL statement with variables:

    $sql = "UPDATE x SET y = $y, z = $z WHERE a = '$a'"
    

    I'll usually output that string just so I can see the final query with the variables expanded:

    Write-Verbose $sql
    

    I find that often makes the error a lot more obvious. If you can do something similar, I can probably help you pin down the SQL error. You might need to do this in the MOLDatabase module, since it's building the query based on the data you're sending it.

  • #9581

    Mohammed Sabuwala
    Participant

    Hi Don,

    Thanks for your reply.

    I have attached the txt file for your reference.

    Regards,
    Mohammed

    The SQL Query:

    function Save-MSSystemInfo {
    param (
    [Parameter(Mandatory=$True,ValueFromPipeline=$True)]
    [Object]$InputObject
    )
    PROCESS {
    $query = @"
    UPDATE MSSystemInfo SET
    Manufacturer = '$($InputObject.Manufacturer.Replace("'","""))',
    RAM = $($InputObject.RAM),
    OSVersion = '$($InputObject.OSVersion.Replace("'","""))',
    SPVersion = $($InputObject.SPVersion),
    Model = '$($InputObject.Model.Replace("'","""))',
    OSBuild = $($InputObject.OSBuild),
    Sockets = $($InputObject.Sockets),
    WHERE ComputerName = '$($InputObject.ComputerName)'
    "@
    Write-Debug $query
    Invoke-MOLDatabaseQuery -isSQLServer -connectionString $MSConnectionString -query $query

    }
    }

  • #9583

    Dave Wyatt
    Moderator

    You have a trailing comma in your SET clause, right before WHERE (after Sockets = $($InputObject.Sockets) ).

  • #9584

    Don Jones
    Keymaster

    Also, you should add [CmdletBinding()] before your Param() block. Would make Write-Debug a lot more useful.

  • #9604

    Mohammed Sabuwala
    Participant

    Champion Guys...this is excellent stuff...makes a wonderful tool.

    I am currently working on building a tool with combination of PowerShell + PowerCLI + SQL and a web dashboard(Wavemaker and Tomcat)

    I will surely use this forum for all my queries....you guys are awesome 🙂

You must be logged in to reply to this topic.