Asked by:
Exception calling "ExecuteNonQuery" with "0" argument(s): "The parameterized query 'IN' expects the parameter which was not supplied.

Question
-
Hello Everyone,
I am having a problem with a script I am developing.
I am using ADO connection to Insert data into MS SQL Server 2016 from a PowerShell output from an API.
My script below:$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=XYZ; Initial Catalog=Z; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$JSONResult |Select id,surname,managingDeanery
foreach($obj in $JSONResult) {
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $dbConnection
$cmd.CommandText = "INSERT Client (id,surname,managingDeanery) VALUES (@id,@surname,@managingDeanery)"
$cmd.Parameters.AddWithValue("@id",$obj.id);
$cmd.Parameters.AddWithValue("@surname",$obj.surname);
$cmd.Parameters.AddWithValue("@managingDeanery",$obj.managingDeanery);
$cmd.ExecuteNonQuery()
$cmd.Parameters.clear();
}
$conn.Close()
The error I get is:
Exception calling "ExecuteNonQuery" with "0" argument(s): "The
parameterized
query '(@id int,@surname nvarchar(9),@managingDeanery
nvarchar(4000))IN'
expects the parameter '@managingDeanery', which was not supplied."
At line:30 char:5
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [],
MethodInvocationException
+ FullyQualifiedErrorId : SqlExceptionI have tried using :
$cmd.Parameters.AddWithValue("@managingDeanery",'$obj.DBNull.Value');
But this makes all the empty fields and non empty fields as $obj.DBNull.Value in the managingDeanery column which is incorrect. Not all the data in the column is empty.
Any help would be appreciated.
Thanks.
Sunday, October 22, 2017 3:14 PM
All replies
-
You already have a command ($cmd). Why create a new command ($command). That won't work.
If you create a parametrized command then just adding values to the parameters and execute. Do not keep recreating the command and adding then re-adding the parameters.
\_(ツ)_/
Sunday, October 22, 2017 4:58 PM -
Add post code like this using code posting tool on edit bar. "<>"
$conn = New-Object System.Data.SqlClient.SqlConnection('Data Source=XYZ; Initial Catalog=Z; Integrated Security=SSPI') $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = 'INSERT Client (id,surname,managingDeanery) VALUES (@id,@surname,@managingDeanery)' $p1 = $cmd.Parameters.Add('@id',[string]) $p2 = $cmd.Parameters.Add('@surname',[string]) $p3 = $cmd.Parameters.Add('@managingDeanery',[string])) $JSONResult | ForEach-Object { $p1.Value = $_.id $p2.Value = $_.surname $p3.Value = $_.managingDeanery $cmd.ExecuteNonQuery() } $conn.Close()
Be careful of null handling.
\_(ツ)_/
- Edited by jrv Monday, October 23, 2017 6:57 PM
- Proposed as answer by Albert LingMicrosoft contingent staff Tuesday, October 24, 2017 2:37 AM
Sunday, October 22, 2017 5:06 PM -
@ jfv I get an error when I use your script:
The error I get is:
Exception calling "Add" with "1" argument(s): "The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects."
The property 'Value' cannot be found on this object. Verify that the property exists and can be set.
At C:\ :20 char:1
+ $p1 = $cmd.Parameters.Add('@id')
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidCastException
At C:\ 1:27 char:7
+ $p1.Value = $_.id
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFoundMonday, October 23, 2017 6:42 PM -
Sorry. You have to add a type to each parameter like this:
$p1 =$cmd.Parameters.Add('@id',[string])
\_(ツ)_/
- Proposed as answer by Albert LingMicrosoft contingent staff Tuesday, October 24, 2017 2:37 AM
Monday, October 23, 2017 6:58 PM