Forum Discussion

bvi2006's avatar
bvi2006
Copper Contributor
Apr 19, 2019

Unable to cast object of type System.DBNull

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!

System.DBNull
  • Kevin_Morgan's avatar
    Kevin_Morgan
    Apr 22, 2019

    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
    }
  • Kevin_Morgan's avatar
    Kevin_Morgan
    Iron Contributor
    Apr 22, 2019

    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
    }
    • Gary_Broughton's avatar
      Gary_Broughton
      Copper Contributor
      Sep 16, 2020

      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

       

Resources