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
Highlighted
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!

1 Reply
Highlighted
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
}