Unable to cast object of type System.DBNull

[bvi2006]

Hi,

In pulling data from SQL, some of the columns in the row are null. I receive an exception message:

Exception calling "GetString" with "1" argument(s): "Unable to cast object of type 'System.DBNull' to type 'System.String'."

How can I handle this? It seems to basically just move on to the next row, but I need all rows and all columns. If the column is null, ok with me but I do need to set it as an empty string, I guess. This data will be used to set details for users in AD. When I use the set-aduser command, I will need to be able to use the variable even if it is null. I don't want Powershell to not execute the set-aduser command because the particular variable is empty. The one it fails on the most is the supr_username.

$result = $SelectDataTable | foreach-object {
[pscustomobject]@{
Identity = $_.GetString(2)
Title = $_.GetString(3)
Department = $_.GetString(4)
MSC = $_.GetString(5)
Office_Location = $_.GetString(6)
Office_Phone = $_.GetString(7)
Supr_username = $_.GetString(10)

}

Any help would be appreciated!

[#2 · Accepted solution]

@bvi2006 

Can you try the below line :

$Supr_username = if ($_.IsDbNull(10)) { '' } Else { $_.GetString(10) }

Complete script :

$result = $SelectDataTable | foreach-object {

$Supr_username = if ($_.IsDbNull(10)) { '' } Else { $_.GetString(10) }

[pscustomobject]@{
Identity = $_.GetString(2)
Title = $_.GetString(3)
Department = $_.GetString(4)
MSC = $_.GetString(5)
Office_Location = $_.GetString(6)
Office_Phone = $_.GetString(7)
Supr_username = $Supr_username
}

[#3]

@Kevin Morgan 

if ($_.GetType().Name -eq 'DBNull') { $returnValue = 0 } else { $returnValue = $_ }

aka:

    $conn.Open()

    try {

        $reader = $cmd.ExecuteReader()

    } catch {

        Throw $Error[0]

    }

    foreach ($read in $reader) {

        if ($read[0].GetType().Name -eq 'DBNull') {

            $networkCharge = 0

        } else {

            $networkCharge = $read[0].ToDecimal($null)

        }

    }

    $conn.Close()

    return $networkCharge