locked
what exactly is the null part in this error? RRS feed

  • Question

  • I have 2 scripts

    script1 has the following:

    Function Query($Query) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $SqlConnection.ConnectionString = "Server=$DB_Server;Initial Catalog=$Database;Integrated Security=SSPI" 
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
    $SqlCmd.Connection = $SqlConnection 
    $SqlCmd.CommandText = $Query 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
    $SqlAdapter.SelectCommand = $SqlCmd 
    $DataSet = New-Object System.Data.DataSet 
    $a=$SqlAdapter.Fill($DataSet)
    $SqlConnection.Close() 
    $DataSet.Tables[0] }
    
    #create .net array object for csv export
    $exportObject = New-Object System.Collections.ArrayList
    #create ordered dictionary so column names come out in the ordered they were created
    $rowObject = [ordered]@{}
    
    $connection_string = "Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password12!553"
    
    $rowObject.'Connection Details' = $connection_string
    
    #INSERT connection string into Table
    
    Query "UPDATE [$someTable]
    SET [connection_string] = '$connection_string'
    WHERE [cname] = '$cinput' AND ([pserver] = '$pinput'"
    
    $exportObject.Add((New-Object PSObject -Property $rowObject)) | Out-Null
    
    $exportObject | Select-Object

    Now in script2, i call script1 and out-variable the object then convert it to a pscustomobject to use it with an HTML Table function (not relevant to this thread so wont include in code. more info on that here)

    script2:

    & ".\script1.ps1" -ViewConnection "$cinput" -OutVariable xprtOut | Format-Table -Wrap
    
    #converting xprtOut from Arraylist to pscustomobject to be used with ConvertTo-HTMLTable 
    $Arr = @()
    Foreach ($Object in $xprtOut) 
    {
        $i=-1
        $arrayListCount = -($Object | gm | Where-Object {$_.MemberType -like "noteproperty"}).count
    
        $customObj = New-Object pscustomobject
        do {
            $customObj | Add-Member -MemberType NoteProperty -Name (($Object | gm)[$($i)].Name) -Value ($Object."$(($Object | gm)[$($i)].Name)")
            $i--
        } while($i -ge $arrayListCount)
    
        $Arr += $customObj
    } 

    when i run script2, i get the following errors:

    gm : You must specify an object for the Get-Member cmdlet.
    
    $arrayListCount = -($Object | gm | Where-Object {$_.MemberType -l ...
    
    Cannot index into a null array.
    
    ... dd-Member -MemberType NoteProperty -Name (($Object | gm)[$($i)].Name) ...

    After some long debugging, i found the root problem: by removing the Update Query statement from script1, script2 stopped erroring and it started working just fine.

    so the question is, why would the Query statement in script1 be problematic? what does it have to do with the object conversion??

    Thursday, July 11, 2019 2:48 AM

All replies

  • Proper formatting of your script would make it easier to see your issues.  You need to follow standard indenting and naming conventions.

    Naming a function the same as a variable is not a good idea.

    To learn how to properly write a function or script read the following article: PowerShell Style Guidelines

    Also note that we would  not use a data adapter or dataset to return a sql select.  They are used when we are doing complex transactional updates.

    This would be how we do an update query:

    Function Update-Sql{ Param( $updateSQL, $DB_Server, $DataBase ) $connStr = "Server=$DB_Server;Initial Catalog=$Database;Integrated Security=SSPI" $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = $updateSQL $numRowsUpdated = $cmd.ExecuteNonQuery() $conn.Close() }

    $connection_string = 'Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password12!553' $sqlUpdate = @" UPDATE [$someTable] SET [connection_string] = '$connection_string' WHERE [cname] = '$cinput' AND ([pserver] = '$pinput' "@ Update-Sql -updateSQL $sqlUpdate -DB_Server '<your server>' -DataBase '<your database>'

    If you are doing this in a  loop it would not be correct to sue a function that creates a connection.  Just place the code inline and do the update step inside the loop.


    \_(ツ)_/








    Thursday, July 11, 2019 3:09 AM
  • Proper formatting of your script would make it easier to see your issues.  You need to follow standard indenting and naming conventions.

    Naming a function the same as a variable is not a good idea.

    To learn how to properly write a function or script read the following article: PowerShell Style Guidelines

    Also note that we would  not use a data adapter or dataset to return a sql select.  They are used when we are doing complex transactional updates.

    This would be how we do an update query:

    Function Update-Sql{ Param( $updateSQL, $DB_Server, $DataBase ) $connStr = "Server=$DB_Server;Initial Catalog=$Database;Integrated Security=SSPI" $conn = New-Object System.Data.SqlClient.SqlConnection($connStr) $cmd = $conn.CreateCommand() $cmd.CommandText = $updateSQL $rdr = $cmd.ExecuteNonQueryAsync() $conn.Close() }

    $connection_string = 'Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password12!553' $sqlUpdate = @" UPDATE [$someTable] SET [connection_string] = '$connection_string' WHERE [cname] = '$cinput' AND ([pserver] = '$pinput' "@ Update-Sql -updateSQL $sqlUpdate -DB_Server '<your server>' -DataBase '<your database>'

    If you are doing this in a  loop it would not be correct to sue a function that creates a connection.  Just place the code inline and do the update step inside the loop.


    \_(ツ)_/





    wow, that worked!!
    but i dont understand, is have a the adapter in the Query function in script1 that was causing the errors in script2?

    also, in your function, $rdr = $cmd.ExecuteNonQueryAsync(), i dont see $rdr used at all. is that a necessary line?

    Thursday, July 11, 2019 3:49 AM
  • wow, that worked!!
    but i dont understand, is have a the adapter in the Query function in script1 that was causing the errors in script2?

    also, in your function, $rdr = $cmd.ExecuteNonQueryAsync(), i dont see $rdr used at all. is that a necessary line?

    $rdr was to be deleted.

    You cannot do an update with an adapter the way you were trying to do it.

    For users who are not really technical and do not understand programming with the Net SQL classes you should be using the SQL CmdLet - "Invoke-SqlCmd"

    You will find it easier to use and more flexible.

    https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps


    \_(ツ)_/

    Thursday, July 11, 2019 3:53 AM
  • The line should have read:

        $numRowsUpdated = $cmd.ExecuteNonQuery()


    \_(ツ)_/

    Thursday, July 11, 2019 3:55 AM
  • wow, that worked!!
    but i dont understand, is have a the adapter in the Query function in script1 that was causing the errors in script2?

    also, in your function, $rdr = $cmd.ExecuteNonQueryAsync(), i dont see $rdr used at all. is that a necessary line?

    $rdr was to be deleted.

    You cannot do an update with an adapter the way you were trying to do it.

    For users who are not really technical and do not understand programming with the Net SQL classes you should be using the SQL CmdLet - "Invoke-SqlCmd"

    You will find it easier to use and more flexible.

    https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps


    \_(ツ)_/

    the reason im using the Query function as is is because i have found this a year ago and have been using it for everything--select, update, delete, it works for everything. it even works in script1 as well, i can see the entries in table get updated. it just seems to conflict with the conversion function in script2, which is what i found very strange and didnt know why
    Thursday, July 11, 2019 3:57 AM
  • The line should have read:

        $numRowsUpdated = $cmd.ExecuteNonQuery()


    \_(ツ)_/

    but where is it used?
    Thursday, July 11, 2019 4:02 AM
  • Proper formatting of your script would make it easier to see your issues.  You need to follow standard indenting and naming conventions.

    Naming a function the same as a variable is not a good idea.

    To learn how to properly write a function or script read the following article: PowerShell Style Guidelines

    Also note that we would  not use a data adapter or dataset to return a sql select.  They are used when we are doing complex transactional updates.

    This would be how we do an update query:

    Function Update-Sql{ Param( $updateSQL, $DB_Server, $DataBase ) $connStr = "Server=$DB_Server;Initial Catalog=$Database;Integrated Security=SSPI" $conn = New-Object System.Data.SqlClient.SqlConnection($connStr) $cmd = $conn.CreateCommand() $cmd.CommandText = $updateSQL $numRowsUpdated = $cmd.ExecuteNonQuery() $conn.Close() }

    $connection_string = 'Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password12!553' $sqlUpdate = @" UPDATE [$someTable] SET [connection_string] = '$connection_string' WHERE [cname] = '$cinput' AND ([pserver] = '$pinput' "@ Update-Sql -updateSQL $sqlUpdate -DB_Server '<your server>' -DataBase '<your database>'

    If you are doing this in a  loop it would not be correct to sue a function that creates a connection.  Just place the code inline and do the update step inside the loop.


    \_(ツ)_/







    i am getting an exception

    Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."

    +     $numRowsUpdated = $cmd.ExecuteNonQuery()

    i commented out $conn.Close() and i still receive the error

    if i comment out

    $numRowsUpdated = $cmd.ExecuteNonQuery()

    the update doesnt seem to be happening at all. the table entry inst getting updated...only with the adapter it was getting updated

    Friday, July 12, 2019 10:15 PM
  • Sorry - I copied and edited your code and forgot to add the required "Open" command. I fixed the original post so copy it again.


    \_(ツ)_/

    Friday, July 12, 2019 11:03 PM
  • Sorry - I copied and edited your code and forgot to add the required "Open" command. I fixed the original post so copy it again.


    \_(ツ)_/

    hmm, even with the open statement, select statements not doing anything it seems compared to if i had the adapter, then select statements work 

    Friday, July 12, 2019 11:14 PM
  • Sorry - I copied and edited your code and forgot to add the required "Open" command. I fixed the original post so copy it again.


    \_(ツ)_/

    hmm, even with the open statement, select statements not doing anything it seems compared to if i had the adapter, then select statements work 

    What select statement? There is no select statement. An update returns no information except for the count of records affected.  You cannot use a select statement in an update SQL script with the net class.


    \_(ツ)_/

    Friday, July 12, 2019 11:23 PM
  • Sorry - I copied and edited your code and forgot to add the required "Open" command. I fixed the original post so copy it again.


    \_(ツ)_/

    hmm, even with the open statement, select statements not doing anything it seems compared to if i had the adapter, then select statements work 

    What select statement? There is no select statement. An update returns no information except for the count of records affected.  You cannot use a select statement in an update SQL script with the net class.


    \_(ツ)_/

    even with update statement it wont work
    Friday, July 12, 2019 11:59 PM
  • How do you know it doesn't work? If it works there is no output.  If there is an error you will get the error.  If the update statement finds no matching records then it will have no output.

    An update statement produces no output ever.  Check the return value. If it is zero then no matching records were found and no update was performed.


    \_(ツ)_/

    Saturday, July 13, 2019 12:09 AM
  • How do you know it doesn't work? If it works there is no output.  If there is an error you will get the error.  If the update statement finds no matching records then it will have no output.

    An update statement produces no output ever.  Check the return value. If it is zero then no matching records were found and no update was performed.


    \_(ツ)_/

    because the table entry wasnt updated. but with sql adapter it gets updated
    Saturday, July 13, 2019 12:15 AM
  • The update statement is like incorrect.

    YOu an test the selectivity of an update like this:

    $sqlSelect = @"
    SELECT * FROM $someTable
    WHERE [cname] = '$cinput' AND ([pserver] = '$pinput'
    "@
    invoke-sqlcmd -Query $sqlSelect -Database $database -ServerInstance $DB_Server
    
    
    Don't forget to define the variables.


    \_(ツ)_/

    Saturday, July 13, 2019 12:25 AM
  • To simplify your debugging start by using this version of the code. Be sure to supply all values.

    Function Update-Sql {
        Param (
            [Parameter(Mandatory)]
            $updateSQL,
            [Parameter(Mandatory)]
            $DB_Server,
            [Parameter(Mandatory)]
            $DataBase
        )
        $connStr = "Server=$DB_Server;Initial Catalog=$Database;Integrated Security=SSPI"
        $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $updateSQL
        if($cmd.ExecuteNonQuery()){
            Write-Host 'Update succeeded' -fore green
        }else{
            Write-Host 'Update failed - no records found' -ForegroundColor red
        }
        $conn.Close()
    }
    
    $connection_string = 'Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password12!553'
    $someTable = 'your table name'
    $cinput = 'some value'
    $pinput = 'some value'
    $sqlUpdate = @"
    UPDATE [$someTable]
    SET [connection_string] = '$connection_string'
    WHERE [cname] = '$cinput' AND ([pserver] = '$pinput'
    "@
    Update-Sql -updateSQL $sqlUpdate -DB_Server '<your server>' -DataBase '<your database>'
    
    
    


    \_(ツ)_/

    Saturday, July 13, 2019 12:33 AM
  • The update statement is like incorrect.

    YOu an test the selectivity of an update like this:

    $sqlSelect = @"
    SELECT * FROM $someTable
    WHERE [cname] = '$cinput' AND ([pserver] = '$pinput'
    "@
    invoke-sqlcmd -Query $sqlSelect -Database $database -ServerInstance $DB_Server
    
    Don't forget to define the variables.


    \_(ツ)_/

    yes, thats the alternative way i tried. with Invoke-SqlCmd, i was able to see the entry in table updated. 

    however, just wanted to update you that the function wasnt working, and only works with sql adapter, but i have the Invoke-sqlcmd as the workaround for now :)

    Saturday, July 13, 2019 12:34 AM
  • The invoke command that you posted does not update anything. What did you use to update the table?

    \_(ツ)_/

    Saturday, July 13, 2019 12:38 AM
  • I tested all of my code and it works flawlessly as expected. I think your lack of experience with PowerShell and SQL are causing you to make bad assumptions.

    \_(ツ)_/

    Saturday, July 13, 2019 12:40 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Wednesday, July 31, 2019 8:19 AM