function ConvertTo-DataTable - Please use DBNull instead

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 4 years and 2 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
lontru
Posts: 103
Last visit: Thu Mar 07, 2024 2:00 pm

function ConvertTo-DataTable - Please use DBNull instead

Post by lontru »

PowerShell Studio 2018 v5.5.154

Getting this error:

Exception setting "Item": "Exception calling "set_Item" with "2" argument(s): "Cannot set Column 'LastActiveTime' to be null. Please use DBNull instead.""

Code: Select all

	$Members = Get-CMCollectionMember -CollectionName $TargetCollection | Select-Object -Property name, ResourceID, LastActiveTime, UserName
	$datagridview_devices.DataSource = ConvertTo-DataTable $Members
	$datagridview_devices.AutoSizeColumnsMode = 'Fill'
from this function "function ConvertTo-DataTable"
line that fail in the function:

$row.Item($prop.Name) = $prop.Value

How do i fix it so when the value is null it fill out ### instead of something blank
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: function ConvertTo-DataTable - Please use DBNull instead

Post by jvierra »

Try this:

Code: Select all

[array]$Members = Get-CMCollectionMember -CollectionName $TargetCollection | Select-Object -Property name, ResourceID, LastActiveTime, UserName
$datagridview_devices.DataSource = ConvertTo-DataTable $Members
$datagridview_devices.AutoSizeColumnsMode = 'Fill'
User avatar
lontru
Posts: 103
Last visit: Thu Mar 07, 2024 2:00 pm

Re: function ConvertTo-DataTable - Please use DBNull instead

Post by lontru »

still get error because it null
ERROR: Exception setting "Item": "Exception calling "set_Item" with "2" argument(s): "Cannot set Column 'LastActiveTime' to be null. Please use DBNull instead.""
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: function ConvertTo-DataTable - Please use DBNull instead

Post by jvierra »

You are likely using an old copy of the function. Her eis the newest version that has adjusted some things.

Code: Select all

function ConvertTo-DataTable{
	<#
		.SYNOPSIS
			Converts objects into a DataTable.
	
		.DESCRIPTION
			Converts objects into a DataTable, which are used for DataBinding.
	
		.PARAMETER  InputObject
			The input to convert into a DataTable.
	
		.PARAMETER  Table
			The DataTable you wish to load the input into.
	
		.PARAMETER RetainColumns
			This switch tells the function to keep the DataTable's existing columns.
		
		.PARAMETER FilterWMIProperties
			This switch removes WMI properties that start with an underline.
	
		.EXAMPLE
			$DataTable = ConvertTo-DataTable -InputObject (Get-Process)
	#>
	[OutputType([System.Data.DataTable])]
	param(
	$InputObject, 
	[ValidateNotNull()]
	[System.Data.DataTable]$Table,
	[switch]$RetainColumns,
	[switch]$FilterWMIProperties)
	
	if($null -eq $Table)
	{
		$Table = New-Object System.Data.DataTable
	}
	
	if ($null -eq $InputObject)
	{
		$Table.Clear()
		return @( ,$Table)
	}
	
	if ($InputObject -is [System.Data.DataTable])
	{
		$Table = $InputObject
	}
	elseif ($InputObject -is [System.Data.DataSet] -and $InputObject.Tables.Count -gt 0)
	{
		$Table = $InputObject.Tables[0]
	}
	else
	{
		if (-not $RetainColumns -or $Table.Columns.Count -eq 0)
		{
			#Clear out the Table Contents
			$Table.Clear()
			
			if ($null -eq $InputObject) { return } #Empty Data
			
			$object = $null
			#find the first non null value
			foreach ($item in $InputObject)
			{
				if ($null -ne $item)
				{
					$object = $item
					break
				}
			}
			
			if ($null -eq $object) { return } #All null then empty
			
			#Get all the properties in order to create the columns
			foreach ($prop in $object.PSObject.Get_Properties())
			{
				if (-not $FilterWMIProperties -or -not $prop.Name.StartsWith('__')) #filter out WMI properties
				{
					#Get the type from the Definition string
					$type = $null
					
					if ($null -ne $prop.Value)
					{
						try { $type = $prop.Value.GetType() }
						catch { Out-Null }
					}
					
					if ($null -ne $type) # -and [System.Type]::GetTypeCode($type) -ne 'Object')
					{
						[void]$table.Columns.Add($prop.Name, $type)
					}
					else #Type info not found
					{
						[void]$table.Columns.Add($prop.Name)
					}
				}
			}
			
			if ($object -is [System.Data.DataRow])
			{
				foreach ($item in $InputObject)
				{
					$Table.Rows.Add($item)
				}
				return @( ,$Table)
			}
		}
		else
		{
			$Table.Rows.Clear()
		}
		
		foreach ($item in $InputObject)
		{
			$row = $table.NewRow()
			
			if ($item)
			{
				foreach ($prop in $item.PSObject.Get_Properties())
				{
					if ($table.Columns.Contains($prop.Name))
					{
						$row.Item($prop.Name) = $prop.Value
					}
				}
			}
			[void]$table.Rows.Add($row)
		}
	}
	
	return @(,$Table)
}
User avatar
lontru
Posts: 103
Last visit: Thu Mar 07, 2024 2:00 pm

Re: function ConvertTo-DataTable - Please use DBNull instead

Post by lontru »

got same error with the posted function.

modified the code a bit to get away with the warnings

Code: Select all

if ($($prop.Value))
{
	$row.Item($prop.Name) = $prop.Value
}
This topic is 4 years and 2 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked