Cannot use parameter in SET clause of UPDATE query (Powershell, MS Access SQL)

Question

Given this trivial Access database table:

ID   fruit   count
--   -----   -----
1    apples  10
2    oranges 2

My aim is to write a Powershell cmdlet (let's call it Set-FruitCount) that, given the name of a fruit, can set the count of that fruit. For example, the command Set-FruitCount -Fruit Apples -Count 1 should update the row with ID 1 to set the count to 1. Of course my use case is more complicated, but I've tried to simplify the problem as far as possible for the sake of a clear question.

However, when trying to perform a parametrized update query, it does not work. However, hard-coding a specific value in almost the same query, just parametrizing the actual WHERE clause as opposed to the set clause, it does work.

This is my program:

$oConn = New-Object System.Data.OleDb.OleDbConnection "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\pvz\Documents\fruits.mdb"
$oConn.open()

function Get-Fruits {
    $TextInfo = (Get-Culture).TextInfo
    $oCmd = New-Object System.Data.OleDb.OleDbCommand("SELECT * FROM [fruits]", $oConn)
    $oReader = $oCmd.ExecuteReader()
    while ($oReader.Read()) {
        $obj = New-Object PSObject
        for ($i = 0; $i -lt $oReader.FieldCount; $i++) {
            $Name = $TextInfo.ToTitleCase($oReader.GetName($i))
            $Value = $oReader[$i]
            $obj | Add-Member -Type NoteProperty -Name $Name -Value $Value
        }
        $obj
    }
    $oReader.Close()
}

function Set-FruitCount {
    Param($Fruit, $Count)
    $strQuery = "UPDATE [fruits] SET [count] = @COUNT WHERE [fruit] = @FRUIT"
    $oCmd = New-Object System.Data.OleDb.OleDbCommand($strQuery, $oConn)
    [void]$oCmd.Parameters.Add("@FRUIT", $Fruit)
    [void]$oCmd.Parameters.Add("@COUNT", $Count)
    $ret = $oCmd.ExecuteNonQuery()
    Write-Host "Set-FruitCount affected $ret rows"
}

function Set-FruitCountToZero {
    Param($Fruit)
    $strQuery = "UPDATE [fruits] SET [count] = 0 WHERE [fruit] = @FRUIT"
    $oCmd = New-Object System.Data.OleDb.OleDbCommand($strQuery, $oConn)
    [void]$oCmd.Parameters.Add("@FRUIT", $Fruit)
    $ret = $oCmd.ExecuteNonQuery()
    Write-Host "Set-FruitCountToZero affected $ret rows"
}

Get-Fruits | Format-Table

Set-FruitCountToZero -Fruit "apples"
Get-Fruits | Format-Table

Set-FruitCount -Fruit "apples" -Count 1
Get-Fruits | Format-Table

$oConn.close()

And this is the output:

PS C:\Users\pvz\desktop> .\fruits.ps1

ID Fruit   Count
-- -----   -----
 1 apples     10
 2 oranges     2


Set-FruitCountToZero affected 1 rows

ID Fruit   Count
-- -----   -----
 1 apples      0
 2 oranges     2


Set-FruitCount affected 0 rows

ID Fruit   Count
-- -----   -----
 1 apples      0
 2 oranges     2


PS C:\Users\pvz\desktop>

As we can see, the Set-FruitCountToZero does work correctly, setting the count to zero, and affecting one row in the database. However, the Set-FruitCount function does not work, despite the only difference being one more parameter being added for the SET clause.

What am I missing?

(N.B. This question is regarding parametrized SQL queries, it has nothing to do with Powershell function parameters.)

Where are you setting the parameter value to zero? (You said the only different is the existence of the paramaters?) — ashleedawg, Mar 01 '18 at 15:02
Possible duplicate of [How do I pass multiple parameters into a function in PowerShell?](https://stackoverflow.com/questions/4988226/how-do-i-pass-multiple-parameters-into-a-function-in-powershell) — ashleedawg, Mar 01 '18 at 15:03
@JacobH You are correct. Reversing the parameters gave me the desired result! — Per von Zweigbergk, Mar 01 '18 at 15:04
@ashleedawg This question is regarding parametrized SQL queries, it has nothing to do with Powershell function parameters. — Per von Zweigbergk, Mar 01 '18 at 15:06

score 0 · Accepted Answer · answered Mar 01 '18 at 15:17

As @JacobH posted in a comment, it seems that OleDB is sensitive to the order parameters come in.

Therefore, here is the corrected version of ´Set-FruitCount` that does work:

function Set-FruitCount {
    Param($Fruit, $Count)
    $strQuery = "UPDATE [fruits] SET [count] = @COUNT WHERE [fruit] = @FRUIT"
    $oCmd = New-Object System.Data.OleDb.OleDbCommand($strQuery, $oConn)
    [void]$oCmd.Parameters.Add("@COUNT", $Count)
    [void]$oCmd.Parameters.Add("@FRUIT", $Fruit)
    $ret = $oCmd.ExecuteNonQuery()
    Write-Host "Set-FruitCount affected $ret rows"
}

Note that @COUNT and @FRUIT are now appearing in the same order as in the SQL query.

Cannot use parameter in SET clause of UPDATE query (Powershell, MS Access SQL)

1 Answers1