ConvertDataset for GridView 'Invalid storage type: DBNull.'

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 3 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
stevens
Posts: 493
Last visit: Mon Sep 19, 2022 12:23 am
Has voted: 2 times

ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by stevens »

Hi,

Please see viewtopic.php?f=21&t=14437
This item is locked, so I ll have to post a new item on the same (note: cannot download the attachment, it says 'does not exist anymore'.
Property of the object I try to get in the gridview is TypeName: System.Management.Automation.PSCustomObject

If I use $datasetConverted = ConvertTo-DataTable -InputObject $dataset
Then Load-DataGridView -DataGridView $datagridview1 -Item $($datasetConverted | sort Date -Descending)
I get Exception calling "NewRow" with "0" argument(s): "Invalid storage type: DBNull.".Exception.Message
Loading the GridView with $DataSet works fine.

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

Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by jvierra »

You cannot convert a dataset to a DataTable using that function. A dataset is already a DataTable. Why would you want to convert it?
User avatar
stevens
Posts: 493
Last visit: Mon Sep 19, 2022 12:23 am
Has voted: 2 times

Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by stevens »

Thanks for quick reply.
Because I can't click on column, then have it sorted. See https://lazywinadmin.com/2015/01/powers ... dview.html
Assumed that the convert would make this happen (like it did in other gridviews). So I guess it's another approach then?
User avatar
stevens
Posts: 493
Last visit: Mon Sep 19, 2022 12:23 am
Has voted: 2 times

Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by stevens »

Tried to add action, like in article (see below) but that doesn' work ....


$datagridview1_ColumnHeaderMouseClick=[System.Windows.Forms.DataGridViewCellMouseEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellMouseEventArgs]
if ($datagridview1.DataSource -is [System.Data.DataTable])
{
$column = $datagridview1.Columns[$_.ColumnIndex]
$direction = [System.ComponentModel.ListSortDirection]::Ascending

if ($column.HeaderCell.SortGlyphDirection -eq 'Descending')
{
$direction = [System.ComponentModel.ListSortDirection]::Descending
}

$datagridview1.Sort($datagridview1.Columns[$_.ColumnIndex], $direction)
}
}
User avatar
stevens
Posts: 493
Last visit: Mon Sep 19, 2022 12:23 am
Has voted: 2 times

Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by stevens »

If I comment out #if ($datagridview1.DataSource -is [System.Data.DataTable]) -in the click action on the columsn- and click on the column
then the error is
Exception calling "Sort" with "2" argument(s): "DataGridView control must be bound to an IBindingList object to be sorted.".Exception.Message
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by jvierra »

When you use a DataTable with a grid the column sort automatically. There is no need to add any code. It is just how the grid works. Adding code will just screw this up.

Most of it depends on what you are sorting and how you loaded it. Without that information there is really no way for us to tell why you are having issues.

Example:
Create a new form with a DataGridView.

Place either piece of code in a button and click it.

Code: Select all

# get a DataTable:
$dt = Invoke-Sqlcmd -Query 'select * from syscolumns' -server omega\sqlexpress -OutputAs DataTables
$datagidview1.DataSource = $dt

# Get-Files
$files = Get-ChildItem -File
$dt = ConvertTo-DataTable $files
$datagidview1.DataSource = $dt
In both cases the columns will be sortable by clicking. No other code is necessary.
User avatar
stevens
Posts: 493
Last visit: Mon Sep 19, 2022 12:23 am
Has voted: 2 times

Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by stevens »

Thanks! I'm getting my data from sql using a Powershell function, the one below.
So then I have the $dataset, so I guess everything should be ok already.

I load the datagrid via Load-DataGridView -DataGridView $datagridview1 -Item $($dataset | sort Date -Descending)

However, when I click a row, it doesn't sort. Am I missing something?

----
$command.Connection = $conn
Write-Debug $query
$command.CommandText = $query
$reader = $command.ExecuteReader()
$dataset = @()
While ($reader.Read())
{
$row = new-object -TypeName PSCustomObject
for ($i = 0; $i -lt $reader.fieldcount; $i++)
{
$row | Add-Member -MemberType NoteProperty -Name $reader.GetName($i) -Value $reader.GetValue($i)
}
$dataset += $row
}
$reader.Close()
write-output $dataset
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by jvierra »

You need to return a DataTable an not a dataset. Please review my code example wh9ioch shows how to do that.

I never use the function to load a sql table as it is unnecessary and can cause issues.
User avatar
stevens
Posts: 493
Last visit: Mon Sep 19, 2022 12:23 am
Has voted: 2 times

Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by stevens »

You gave this as a solution:
"$dt = Invoke-Sqlcmd -Query 'select * from syscolumns' -server omega\sqlexpress -OutputAs DataTables"
But I don't have the sqlcmd cmdlets installed and cannot on the computer which has the GUI running.
Isn't there another solution changing my command?

$command.Connection = $conn
Write-Debug $query
$command.CommandText = $query
$reader = $command.ExecuteReader()
$dataset = @()
While ($reader.Read())
{
$row = new-object -TypeName PSCustomObject
for ($i = 0; $i -lt $reader.fieldcount; $i++)
{
$row | Add-Member -MemberType NoteProperty -Name $reader.GetName($i) -Value $reader.GetValue($i)
}
$dataset += $row
}
$reader.Close()
write-output $dataset
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'

Post by jvierra »

Just run the following command to get the SQLServer support module.

Install-Module SQLServer
This topic is 3 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