A positional parameter cannot be found that accepts argument System.Data.SqlClient.SqlDataReader

Question

I'm attempting to author a PowerShell script that simply executes a stored proc from my administration database but am running into this:

New-Object : A positional parameter cannot be found that accepts argument 'System.Data.SqlClient.SqlDataReader'.
At C:\Scripts\Deployment\SPROCTest.ps1:19 char:15
+     $reader = New-Object -TypeName System.Data.SqlClient.SqlDataReader  = $GetEn ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidArgument: (:) [New-Object], ParameterBindingException
+ FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

Code would seem to be pretty straight forward like this:

$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=bhbrf95xva.database.windows.net;Database=MY.DATABASE;Integrated Security=False;User=theuser;Password=xxx^xxx"
$connection.Open()

$GetEnvironment = New-Object System.Data.SqlClient.SqlCommand
$GetEnvironment.CommandText = "Select_Environment_ByEnvironmentName"
$GetEnvironment.CommandType = [System.Data.CommandType]::StoredProcedure
$GetEnvironment.Connection = $connection

$ParamGetEnvironment = New-Object -TypeName System.Data.SqlClient.SqlParameter
$ParamGetEnvironment.ParameterName = "@EnvironmentName"
$ParamGetEnvironment.SqlDbType = [System.Data.SqlDbType]::VarChar
$ParamGetEnvironment.Direction = [System.Data.ParameterDirection]::Input
$ParamGetEnvironment.Value = 'TheValue'

$GetEnvironment.Parameters.Add($ParamGetEnvironment)


$reader = New-Object -TypeName System.Data.SqlClient.SqlDataReader  = $GetEnvironment.ExecuteReader()

$connection.Close()

I'm not too concerned (yet) with the actual format of this script, I'm just trying to get it to work first, but I'm wondering why and how I get this to recognize System.Data.SqlClient.SqlDataReader?

Do I need to add a plugin?

I can't try it now, but an SqlDataReader is not intialized by you. You just get the return value from the SqlCommand.ExecuteReader. Did you try $reader = $GetEnvironment.ExecuteReader() — Steve
@Steve Post that as an answer, I believe it to be correct. The OP is probably thinking they need to declare the type, but they don't. — JasonMArcher
@JasonMArcher yes indeed, there is no constructor in the SqlDataReader so that code cannot be correct, thanks — Steve

Steve Steve · Accepted Answer · 2014-12-18T19:56:22

An SqlDataReader cannot be intialized by you. Infact, if you look at the documentation of SqlDataReader you will find that there is no public constructor for it.

You get an SqlDataReader only as return value from a call to an SqlCommand.ExecuteReader. You just need to write

 $reader = $GetEnvironment.ExecuteReader()

Also, keep in mind that, closing the connection used by the SqlDataReader effectively cripples the ability of the reader to read data from the database. So, if you plan to make something useful with the reader, don't close the connection until you have finished to read data.

A positional parameter cannot be found that accepts argument System.Data.SqlClient.SqlDataReader

1 Answers