SQL Calls

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 1 week, 5 days ago.

  • Author
    Posts
  • #66631
    Profile photo of Aaron
    Aaron
    Participant

    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
    
  • #66633
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

You must be logged in to reply to this topic.