You cannot call a method on a null-valued expression. Error in Powershell script RRS feed

  • Question

  • Hi,

      Below is my query to retrieve data from   DB and store it in array variable, it executing properly and the required result is obtained but while running it throws an error  "

    You cannot call a method on a null-valued expression.
    At line:13 char:21
    + $SqlConnection.Close <<<< ()
        + CategoryInfo          : InvalidOperation: (Close:String) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull


    Here is my query

    $fdPath = split-path -parent $MyInvocation.MyCommand.Definition
    Write-Host $fdPath
    $files = Get-ChildItem -Path $fdPath -Filter "*.txt"
    $ConnectionString = "server= ip ;Initial Catalog= Adventureworks;User ID= **;Password= ****;"
    $Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = 'SELECT COl1 as FindString,COL2 as ReplaceString FROM Table'
    $SqlCmd.Connection = $Connection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet) > $null
    $DataSet.Tables[0] | format-table -auto
    $findReplaceList =  @()
    $findReplaceList =  @($table)
    #$findReplaceList = $DataSet.Tables[0] 

    foreach ($file in $files)
    $content = Get-Content $file.PSPath

        ForEach ($findReplaceItem in $findReplaceList)

        $content = $content -replace "$($findReplaceitem.FindString)", "$($findReplaceitem.ReplaceString)"
        Set-Content $file.PSPath  -Value $content
        Write-Host $file.PSPath " modified"

    Why Does it shows this error can anybody give me solution.

    • Edited by pradapg Friday, December 1, 2017 10:13 AM
    • Moved by Bill_Stewart Friday, January 26, 2018 2:59 PM This is not "fix/debug/rewrite my script for me" forum
    Friday, December 1, 2017 10:12 AM

All replies

  • Don't use an adapter or a dataset.  Use  a DataTable and a reader.

    If there is an error in "filling" the dataset the connection can be invalid.  You must check the output of the load or fill to be sure it has executed properly.


    Friday, December 1, 2017 10:51 AM