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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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 : SqlException 

    I 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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.

    \_(ツ)_/




    Sunday, October 22, 2017 5:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    @ 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."
    At C:\ :20 char:1
    + $p1 = $cmd.Parameters.Add('@id')
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : InvalidCastException

    The property 'Value' cannot be found on this object. Verify that the property exists and can be set.
    At C:\ 1:27 char:7
    +          $p1.Value = $_.id
    +          ~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : PropertyNotFound

    Monday, October 23, 2017 6:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sorry.  You have to add a type to each parameter like this:

    $p1 =$cmd.Parameters.Add('@id',[string])

    \_(ツ)_/

    Monday, October 23, 2017 6:58 PM