SOLVED

Unable to cast object of type System.DBNull

%3CLINGO-SUB%20id%3D%22lingo-sub-475399%22%20slang%3D%22en-US%22%3EUnable%20to%20cast%20object%20of%20type%20System.DBNull%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-475399%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EIn%20pulling%20data%20from%20SQL%2C%20some%20of%20the%20columns%20in%20the%20row%20are%20null.%20I%20receive%20an%20exception%20message%3A%3C%2FP%3E%3CP%3EException%20calling%20%22GetString%22%20with%20%221%22%20argument(s)%3A%20%22Unable%20to%20cast%20object%20of%20type%20'System.DBNull'%20to%20type%20'System.String'.%22%3C%2FP%3E%3CP%3EHow%20can%20I%20handle%20this%3F%20It%20seems%20to%20basically%20just%20move%20on%20to%20the%20next%20row%2C%20but%20I%20need%20all%20rows%20and%20all%20columns.%20If%20the%20column%20is%20null%2C%20ok%20with%20me%20but%20I%20do%20need%20to%20set%20it%20as%20an%20empty%20string%2C%20I%20guess.%20This%20data%20will%20be%20used%20to%20set%20details%20for%20users%20in%20AD.%20When%20I%20use%20the%20set-aduser%20command%2C%20I%20will%20need%20to%20be%20able%20to%20use%20the%20variable%20even%20if%20it%20is%20null.%20I%20don't%20want%20Powershell%20to%20not%20execute%20the%20set-aduser%20command%20because%20the%20particular%20variable%20is%20empty.%20The%20one%20it%20fails%20on%20the%20most%20is%20the%20supr_username.%3C%2FP%3E%3CP%3E%24result%20%3D%20%24SelectDataTable%20%7C%20foreach-object%20%7B%3CBR%20%2F%3E%5Bpscustomobject%5D%40%7B%3CBR%20%2F%3EIdentity%20%3D%20%24_.GetString(2)%3CBR%20%2F%3ETitle%20%3D%20%24_.GetString(3)%3CBR%20%2F%3EDepartment%20%3D%20%24_.GetString(4)%3CBR%20%2F%3EMSC%20%3D%20%24_.GetString(5)%3CBR%20%2F%3EOffice_Location%20%3D%20%24_.GetString(6)%3CBR%20%2F%3EOffice_Phone%20%3D%20%24_.GetString(7)%3CBR%20%2F%3ESupr_username%20%3D%20%24_.GetString(10)%3C%2FP%3E%3CP%3E%7D%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-475399%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESystem.DBNull%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-479565%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20cast%20object%20of%20type%20System.DBNull%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-479565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324009%22%20target%3D%22_blank%22%3E%40bvi2006%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20try%20the%20below%20line%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%24Supr_username%20%3D%20if%20(%24_.IsDbNull(10))%20%7B%20''%20%7D%20Else%20%7B%20%24_.GetString(10)%20%7D%3C%2FPRE%3E%3CP%3EComplete%20script%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%24result%20%3D%20%24SelectDataTable%20%7C%20foreach-object%20%7B%3CBR%20%2F%3E%3CBR%20%2F%3E%24Supr_username%20%3D%20if%20(%24_.IsDbNull(10))%20%7B%20''%20%7D%20Else%20%7B%20%24_.GetString(10)%20%7D%3CBR%20%2F%3E%3CBR%20%2F%3E%5Bpscustomobject%5D%40%7B%3CBR%20%2F%3EIdentity%20%3D%20%24_.GetString(2)%3CBR%20%2F%3ETitle%20%3D%20%24_.GetString(3)%3CBR%20%2F%3EDepartment%20%3D%20%24_.GetString(4)%3CBR%20%2F%3EMSC%20%3D%20%24_.GetString(5)%3CBR%20%2F%3EOffice_Location%20%3D%20%24_.GetString(6)%3CBR%20%2F%3EOffice_Phone%20%3D%20%24_.GetString(7)%3CBR%20%2F%3ESupr_username%20%3D%20%24Supr_username%3CBR%20%2F%3E%7D%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1672508%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20cast%20object%20of%20type%20System.DBNull%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1672508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F38365%22%20target%3D%22_blank%22%3E%40Kevin%20Morgan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20(%24_.GetType().Name%20-eq%20'DBNull')%20%7B%20%24returnValue%20%3D%200%20%7D%20else%20%7B%20%24returnValue%20%3D%20%24_%20%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eaka%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%24conn%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E.%3C%2FSPAN%3EOpen()%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s4%22%3Etry%3C%2FSPAN%3E%20%7B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%24reader%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s2%22%3E%24cmd%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E.%3C%2FSPAN%3EExecuteReader()%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%7D%20%3CSPAN%20class%3D%22s4%22%3Ecatch%3C%2FSPAN%3E%20%7B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s4%22%3EThrow%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s2%22%3E%24Error%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E%5D%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%7D%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p3%22%3E%3CSPAN%20class%3D%22s6%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22s4%22%3Eforeach%3C%2FSPAN%3E%3CSPAN%20class%3D%22s6%22%3E%20(%3C%2FSPAN%3E%24read%20%3CSPAN%20class%3D%22s4%22%3Ein%3C%2FSPAN%3E%20%24reader%3CSPAN%20class%3D%22s6%22%3E)%20%7B%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s4%22%3Eif%3C%2FSPAN%3E%20(%3CSPAN%20class%3D%22s2%22%3E%24read%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E%5D.%3C%2FSPAN%3EGetType()%3CSPAN%20class%3D%22s3%22%3E.%3C%2FSPAN%3EName%20%3CSPAN%20class%3D%22s3%22%3E-eq%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s7%22%3E'DBNull'%3C%2FSPAN%3E)%20%7B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%24networkCharge%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s5%22%3E0%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%7D%20%3CSPAN%20class%3D%22s4%22%3Eelse%3C%2FSPAN%3E%20%7B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%24networkCharge%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s2%22%3E%24read%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E%5B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E%5D.%3C%2FSPAN%3EToDecimal(%3CSPAN%20class%3D%22s2%22%3E%24null%3C%2FSPAN%3E)%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%7D%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%7D%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%24conn%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E.%3C%2FSPAN%3EClose()%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p3%22%3E%3CSPAN%20class%3D%22s6%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22s4%22%3Ereturn%3C%2FSPAN%3E%20%24networkCharge%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
best response confirmed by bvi2006 (New Contributor)
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
}

@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