SQL Calls

PowerShell Help
1

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
2

The error indicates that your connection is being closed by the time you get back to your second read attempt. You might try running through your code in a debugger, so you can see where the logic is going wrong on you. You’re probably falling out of you ForEach loop sooner than you thought.