I’m trying to pull a username from a database as a string, I’m using the below code, it works on the first one but then throws an error shown below.
So table1 contains a GUID for a user that is referenced in table2, this attempts to query table2 by table1’s GUID.
It works successfully on the first one, throwing it’s user name…why does it break on the others and how can I fix this?
$connectionstring = "Server = sql1; Database = db1; Integrated Security = True" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionstring $connection.Open() $query = "SELECT * FROM [db1].[dbo].[DocTypePermission_S]" $command = $connection.CreateCommand() $command.CommandText = $query $result = ($command.ExecuteReader()) $global:table = new-object "System.Data.DataTable" $table.Load($result) foreach ($object in $table) { $DocTypeId = ($object.DocTypeId).guid $GroupId = ($object.groupid).guid $UserId = ($object.Userid).guid #user or group $Type = $object.Type $UserQuery = "SELECT UserName FROM [db1].[dbo].[User] where id like '%$Userid%'" $Command = New-Object System.Data.SQLClient.SQLCommand $Command.Connection = $Connection $Command.CommandText = $SQLQuery $Reader = $Command.ExecuteReader() while ($Reader.Read()) { $Reader.GetValue($1) } } $connection.Close()
Exception calling "GetValue" with "1" argument(s): "Invalid attempt to read when no data is present." At line:20 char:1 + $global:table = new-object "System.Data.DataTable"$resultxx.Getvalue($1) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : InvalidOperationException Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The connection's current state is closed." At line:12 char:1 + $Reader = $Command.ExecuteReader() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : InvalidOperationException Exception calling "Read" with "0" argument(s): "Invalid attempt to call Read when reader is closed." At line:13 char:8 + while ($Reader.Read()) { + ~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : InvalidOperationException