I am attempting to run a query against SQL using powershell and was successful until I attempted to throw parameters in the mix. My code is as follows:
$parameter="TEST"
$connectionString = "CONNECTION STRING";
$Connection = New-Object System.Data.OleDb.OleDbConnection $connectionString;
$Values = New-Object System.Data.DataSet;
$SelectCommand = "Select Value FROM dbo.Values WHERE Value=@param";
$Command = New-Object System.Data.OleDb.OleDbCommand $SelectCommand,$Connection;
$Command.Parameters.Add("@param", [System.Data.OleDb.OleDbType]::VarChar, 50)
$Command.Parameters["@param"].Value = $parameter;
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $Command;
[void] $adapter.Fill($Values);
foreach($a in $Values.tables[0].Rows )
{
#handle returned values
}
I have messed around with different ways of passing a parameter that i have found online and always get the same result:
Exception calling "Fill" with "1" argument(s): "Must declare the scalar variable "@try"."
At line:28 char:31
+ [void] $adapter.Fill <<<< ($Values);
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
Any help would be appreciated, finding good examples of powershell SQL queries has been rough. Using OLeDB is not necessary, if someone has an example using another provider that is fine as well, I have also been messing with System.Data.SqlClient.SqlConnection without success.
