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
ConvertDataset for GridView 'Invalid storage type: DBNull.'
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.
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.
Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'
You cannot convert a dataset to a DataTable using that function. A dataset is already a DataTable. Why would you want to convert it?
Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'
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?
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?
Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'
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)
}
}
$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)
}
}
Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'
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
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
Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'
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.
In both cases the columns will be sortable by clicking. No other code is necessary.
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
Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'
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
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
Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'
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.
I never use the function to load a sql table as it is unnecessary and can cause issues.
Re: ConvertDataset for GridView 'Invalid storage type: DBNull.'
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
"$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