Setting datatable columns to allow null values (AllowDBNull)

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 5 years and 10 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
iamtj69
Posts: 11
Last visit: Thu Jun 23, 2022 2:47 am

Setting datatable columns to allow null values (AllowDBNull)

Post by iamtj69 »

Hello,

I am hoping somebody can help me.

I have a datagridview control on a form which is populated with data from a datatable using the 'ConvertTo-DataTable' and then 'Update-DataGridView' functions.

The above works ok accept for one issue:

One of the columns contains null values and throws a bunch of errors : "Cannot set Column '<Column Name>' to be null. Please use DBNull instead"

Is there a way i can adapt the code in the 'ConvertTo-DataTable' function to set all the columns to allow null values? I am struggling.

I have tried similar to the following with little success.

Code: Select all

foreach ($col in $Table.Columns){ 
	Write-Host "column = $col"
	$col.AllowDBNull = $true
}
Can anyone help please?
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Setting datatable columns to allow null values (AllowDBNull)

Post by jvierra »

Please post all of the code where you load the grid. Without that it is not possible to understand why this may be happening.
User avatar
iamtj69
Posts: 11
Last visit: Thu Jun 23, 2022 2:47 am

Re: Setting datatable columns to allow null values (AllowDBNull)

Post by iamtj69 »

Code: Select all

#region Control Helper Functions
function Update-DataGridView
{
	<#
	.SYNOPSIS
		This functions helps you load items into a DataGridView.

	.DESCRIPTION
		Use this function to dynamically load items into the DataGridView control.

	.PARAMETER  DataGridView
		The DataGridView control you want to add items to.

	.PARAMETER  Item
		The object or objects you wish to load into the DataGridView's items collection.
	
	.PARAMETER  DataMember
		Sets the name of the list or table in the data source for which the DataGridView is displaying data.

	.PARAMETER AutoSizeColumns
	    Resizes DataGridView control's columns after loading the items.
	#>
	Param (
			[ValidateNotNull()]
			[Parameter(Mandatory = $true)]
			[System.Windows.Forms.DataGridView]$DataGridView,
			[ValidateNotNull()]
			[Parameter(Mandatory = $true)]
			$Item,
			[Parameter(Mandatory = $false)]
			[string]$DataMember,
			[System.Windows.Forms.DataGridViewAutoSizeColumnMode]$AutoSizeColumns = 'None'
	)
	$DataGridView.SuspendLayout()
	$DataGridView.DataMember = $DataMember
	
	if ($Item -is [System.Data.DataSet] -and $Item.Tables.Count -gt 0)
	{
		$DataGridView.DataSource = $Item.Tables[0]
	}
	elseif ($Item -is [System.ComponentModel.IListSource]`
			-or $Item -is [System.ComponentModel.IBindingList] -or $Item -is [System.ComponentModel.IBindingListView])
	{
		$DataGridView.DataSource = $Item
	}
	else
	{
		$array = New-Object System.Collections.ArrayList
		
		if ($Item -is [System.Collections.IList])
		{
			$array.AddRange($Item)
		}
		else
		{
			$array.Add($Item)
		}
		$DataGridView.DataSource = $array
	}
	
	if ($AutoSizeColumns -ne 'None')
	{
		$DataGridView.AutoResizeColumns($AutoSizeColumns)
	}
	
	$DataGridView.ResumeLayout()
}

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 (
			[ValidateNotNull()]
			$InputObject,
			[ValidateNotNull()]
			[System.Data.DataTable]$Table,
			[switch]$RetainColumns,
			[switch]$FilterWMIProperties)
	
	if ($null -eq $Table)
	{
		$Table = New-Object System.Data.DataTable
	}
	
	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)
					}
				}
			}
			
			foreach ($col in $Table.Columns) ###
			{ ###
				Write-Host "column = $col" ###
				$col.AllowDBNull = $true ###
			} ###
			
			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)
}
#endregion

$formMain_Load={
	#TODO: Initialize Form Controls here
	Import-Module RemoteDesktop
}

$buttonExit_Click={
	#TODO: Place custom script here
	$formMain.Close()
}

$buttonFilteredData_Click = {
	$datagridviewResults.DataSource = $null
	Write-Host "Start filtered data load..."
	$data = Get-RDUserSession -CollectionName "Collection 1" -ConnectionBroker "broker1.test1.com" | Select -Property Username, SessionState, CreateTime, DisconnectTime, IdleTime, CollectionName, HostServer
	$table = ConvertTo-DataTable -InputObject $data -FilterWMIProperties
	Update-DataGridView -DataGridView $datagridviewResults -Item $table
	Write-Host "Finished filtered data load."
}
Thanks for the reply.

I have posted all the basic code above. The lines I have added to the function (ConvertTo-DataTable) are post fixed with '###'

Regards,

Tom
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Setting datatable columns to allow null values (AllowDBNull)

Post by jvierra »

This is how to load a grid from a data table.

Code: Select all

Import-Module RemoteDesktop

$formMain_Load={
	#TODO: Initialize Form Controls here
}

$buttonExit_Click={
	#TODO: Place custom script here
	$formMain.Close()
}
$buttonFilteredData_Click = {
	Write-Host "Start filtered data load..."
	$data = Get-RDUserSession -CollectionName "Collection 1" -ConnectionBroker "broker1.test1.com" | 
        Select -Property Username, SessionState, CreateTime, DisconnectTime, IdleTime, CollectionName, HostServer
	$datagridviewResults.DataSource = ConvertTo-DataTable $data
	Write-Host "Finished filtered data load."
}
Don't use the Update-DataGrid and ConvertTo-DataTable. It is unnecessary.
User avatar
iamtj69
Posts: 11
Last visit: Thu Jun 23, 2022 2:47 am

Re: Setting datatable columns to allow null values (AllowDBNull)

Post by iamtj69 »

Thanks for the tip jvierra, i have adjusted as instructed.

I am still having issues working out how to set the columns in the datatable to allow null values. Any ideas on where and how to adjust the 'ConvertTo-DataTable' function to set the columns to AllowDBNull as the columns are created?

Many thanks,
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Setting datatable columns to allow null values (AllowDBNull)

Post by jvierra »

The ConvertTo-DataTable function adjusts for that correctly. I have never had an issue with it.

What version of PSS are you using
User avatar
iamtj69
Posts: 11
Last visit: Thu Jun 23, 2022 2:47 am

Re: Setting datatable columns to allow null values (AllowDBNull)

Post by iamtj69 »

Hello jvierra,

I am using the following version:

PowerShell Studio 2017 v5.4.143
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Setting datatable columns to allow null values (AllowDBNull)

Post by jvierra »

The old ConvertTo-DataTable may have some issues. You need to use the newer version.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Setting datatable columns to allow null values (AllowDBNull)

Post by jvierra »

Another thing to consider is that you cannot load complex objects into a DGV column or use any of the functions to convert them. NO property can be an array or an object. If your CmdLet is returning complex fields then no version of the functions will work.
User avatar
iamtj69
Posts: 11
Last visit: Thu Jun 23, 2022 2:47 am

Re: Setting datatable columns to allow null values (AllowDBNull)

Post by iamtj69 »

jvierra wrote: Mon Apr 30, 2018 3:07 am The old ConvertTo-DataTable may have some issues. You need to use the newer version.
Thanks jvierra, I will update to the latest version ASAP.

This is an interesting one, and like you say, may be resolved in the latest version but in case any one else is interested... I shall describe what I found:

It seems that there is an issue only with null values when the datatype is set to 'datetime'.

When I queried all the columns, I noticed that all columns were already set to 'AllowDBNull = True', so I thought it strange why I was receiving the errors for the cells that contain a null value for 'DisconnectTime'!

I then started to look at the 'DataType' for each column. When I set the 'datatype' for the DisconnectTime column from 'datetime' to 'string' I no longer receive the errors.

I am not sure why?

This is the info I queried for the columns:

column = UserName, allowdbnull = True, datatype = string
column = SessionState, allowdbnull = True, datatype = Microsoft.RemoteDesktopServices.Management.SESSION_STATE
column = CreateTime, allowdbnull = True, datatype = datetime
column = DisconnectTime, allowdbnull = True, datatype = datetime
column = IdleTime, allowdbnull = True, datatype = uint32
column = CollectionName, allowdbnull = True, datatype = string
column = HostServer, allowdbnull = True, datatype = string

I changed the datatype with the following:

Code: Select all

$Table.Columns['DisconnectTime'].DataType = 'string'
This topic is 5 years and 10 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