如何通过PowerShell在SQL查询中传递参数 [英] How to pass the parameter in SQL query from PowerShell

问题描述

我在PowerShell中具有以下代码，该代码执行SQL查询以更新我的表:

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

但是当我执行脚本时，错误日志显示$ ActID [$ i]中没有传递值

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

还有其他方法可以在Powershell查询中传递参数(变量)吗?

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

推荐答案

可能是什么错误:

$i -le $ActID.Length;

应该是

$i -lt $ActID.Length;

---

您还可以使用简化代码的管道:

---

You could also use piping which simplifies the code:

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

除了您使用的属性是Values-真的是您想要的吗?价值观看起来像是某种东西的集合.也许您想使用一个值.

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.

这篇关于如何通过PowerShell在SQL查询中传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！