How to pass the parameter in SQL query from PowerShell

Question

I have this code in PowerShell, that executes SQL query to UPDATE my table:

$Connection=new-object data.sqlclient.sqlconnection "server=server;database=mydb;trusted_connection=true;"
$Connection.open()

For ( $i = 0; $i -le $ActID.Length; $i ++ ) { 
    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.Connection = $Connection
    $cmd.CommandText = 
    "
    update Table 
    set Note = @PATH
    "
    $cmd.Parameters.Add("@PATH", $ActID[$i].Values) | Out-Null

    $cmd.ExecuteNonQuery()
}

I tried to update the table with the variable defined in this string:

$cmd.Parameters.Add("@PATH", $ActID[$i].Values) | Out-Null

But when I execute the script the error log says that there is no value passed in $ActID[$i]

Are there other methods to pass parameters (variables) in powershell queries?

stej · Accepted Answer · 2010-02-15 15:29:13Z

2

What could be the mistake:

$i -le $ActID.Length;

it should be probably

$i -lt $ActID.Length;

You could also use piping which simplifies the code:

$actId | % { ..... $cmd.Parameters.Add("@PATH", $_.Values) | Out-Null .... }

Besides that the property you use is Values - is it really what you wanted? Values looks like a collection of something. Maybe you wanted to use a single value.

edited Feb 15, 2010 at 15:29

answered Feb 15, 2010 at 15:15

stej

29.5k11 gold badges76 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dugas Over a year ago

@Zshava - also beware you don't have a WHERE clause on that query - I don't know if that was your intention or not...

user273579 Over a year ago

@thedugas - it was my intention, my true query is more complex but it is not important in this qestion

Nathan McKaskle Over a year ago

This does not work, it basically took @PATH literally as what I was trying to insert.

mklement0 Over a year ago

Good point re -lt vs. -le, but I think the Parameters.Add() call (which you've taken from the question) won't work. The docs suggest that you must use something like: $cmd.Parameters.Add([System.Data.SqlClient.SqlParameter]::new("PATH", $_.Values))

Collectives™ on Stack Overflow

How to pass the parameter in SQL query from PowerShell

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related