3

I've seen the different discussions of using Parameters.Add vs Parameters.AddWithValue (one is explicit, the other is not), but that isn't my issue.

I'm defining a command, which will be used to do multiple inserts into the database. I figured I'd do something like the following:

[define command]
[add parameters (including a batchid not known at this time)]

[enter loop]
   [create new batchid]
   [set parameter value]
   [execute command]
[next]

This would mean my parameters are neatly defined in advance, and I set their values within the loop. The code for that looks like this:

$SqlCmdAdd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdAdd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmdAdd.CommandText = "AddBatch"
$SqlCmdAdd.Parameters.Add("@RunId", [Data.SQLDBType]::UniqueIdentifier)
$SqlCmdAdd.Parameters.Add("@RunType", [Data.SQLDBType]::NVarChar, 50)
$SqlCmdAdd.Connection = $SqlConnection

Within the loop I figured I could do something like this:

# Generate GUID to be used as a BatchID
$batchID = [guid]::NewGuid()

# I tried both using the parameter name, as well as their numeric order. Neither worked
$SqlCmdAdd.Parameters["@RunId"].Value = $batchID | Out-Null
$SqlCmdAdd.Parameters["@RunType"].Value = "HDD" | Out-Null
# $SqlCmdAdd.Parameters[0].SqlValue = $batchID | Out-Null
# $SqlCmdAdd.Parameters[1].SqlValue = "HDD" | Out-Null

However, if I read out the values of $SqlCmdAdd.Parameters[0] and [1], I see the proper names and data types, but their values are null. Thus, I'll get an error that no values were specified for the parameters.

What did work was:

$SqlCmdAdd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdAdd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmdAdd.CommandText = "AddBatch"
$SqlCmdAdd.Connection = $SqlConnection

Within the loop doing this:

# Add parameters
$SqlCmdAdd.Parameters.AddWithValue("@RunId", $batchID) | Out-Null
$SqlCmdAdd.Parameters.AddWithValue("@RunType", "HDD") | Out-Null
$SqlCmdAdd.ExecuteNonQuery()
$SqlConnection.Close()

# Clear parameter collection for next run
$SqlCmdAdd.Parameters.Clear()

What irritates me about this is the need to clear the parameter collection for the next run, and then recreating them. While that runs my code, I'm interested in finding out why attempting to set the value of the earlier created parameters in the first code block doesn't have any effect.

I tried googling and read countless articles.

Itchydon
  • 2,572
  • 6
  • 19
  • 33
SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
  • 2
    Try and remove ` | Out-Null` from assingment statements: `...Value = $batchID` – vonPryz Jan 03 '20 at 12:49
  • @vonPryz I'll be damned. I added them because earlier things weren't working, and I saw a ton of other articles defining them as such. If you add your comment as an answer, I'll accept it and get you some extra points :) – SchmitzIT Jan 03 '20 at 13:29
  • 2
    Just saying thanks, many links out there about how to *generally* write this in powershell (or just C# lite) don't work... – downwitch Jun 10 '21 at 01:24
  • Thanks -- this helped me: $SqlCmdAdd.CommandType = [System.Data.CommandType]::StoredProcedure Couldn't find how to set the SqlCmd commandType elsewhere. Now I can see how to use C# namespaces to get to types in powershell. – raddevus Oct 12 '21 at 18:57

1 Answers1

7

This happens, as piping the variable to Out-Null happens before assignment. Thus,

$SqlCmdAdd.Parameters["@RunId"].Value = $batchID | Out-Null

Will first pass $batchID to Out-Null, and the result - $null - is assigned into .Value.

As for a fix, just remove Out-Null like so,

$SqlCmdAdd.Parameters["@RunId"].Value = $batchID
vonPryz
  • 22,996
  • 7
  • 54
  • 65