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, Commented Dec 18, 2014 at 18:01
@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, Commented Dec 18, 2014 at 19:42
@JasonMArcher yes indeed, there is no constructor in the SqlDataReader so that code cannot be correct, thanks — Steve
– Steve, Commented Dec 18, 2014 at 19:56

Steve · Accepted Answer · 2014-12-18 20:02:01Z

1

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.

edited Dec 18, 2014 at 20:02

answered Dec 18, 2014 at 19:56

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related