SQL Calls

Welcome Forums General PowerShell Q&A SQL Calls

This topic contains 1 reply, has 2 voices, and was last updated by

 
Keymaster
1 year, 8 months ago.

  • Author
    Posts
  • #66631

    Participant
    Points: 0
    Rank: Member

    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

    Keymaster
    Points: 1,625
    Helping HandTeam Member
    Rank: Community Hero

    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.

The topic ‘SQL Calls’ is closed to new replies.