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.
User avatar
stevens
Posts: 416
Meble kuchenne na zamówienie - na wymiar - Wrocław
Joined: Wed Jun 15, 2016 2:26 am

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

Post by stevens »

Thanks, I know howto install the module, but I don't want to install the module.
The GUI will run on different clients, I can't just install it each time/check it each time. I 'd prefer to keep my config as is but get back the datatable output back.
Is that no option?

jvierra
Posts: 14358
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra »

Code: Select all

function Get-SQLTable{
    param(
        $SQL,
        $Database,
        $Server
    )
    $conn = [system.Data.SqlClient.SqlConnection]::new("server=$Server;database=$Database;Integrated Security=SSPI;")
    $conn.open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $sql
    $rdr = $cmd.ExecuteReader()
    $dt = [System.Data.DataTable]::new()
    $dt.Load($rdr)
    $conn.Close()
    return (,$dt)
}

#example
$datagridview.DatSource = Get-SQLTable -SQL 'select * from syscolumns' -Database master -Server pc1

User avatar
stevens
Posts: 416
Joined: Wed Jun 15, 2016 2:26 am

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

Post by stevens »

Thanks! It works, but two more questions:
-why is the (, in the "return (,$dt)" ?
-should the grid now autosort in the GUI? it does not :-(

jvierra
Posts: 14358
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra »

You an sort the grid by clicking on the column headers.

The return is required by the type of object due to the autoenum of the pipeline.

User avatar
stevens
Posts: 416
Joined: Wed Jun 15, 2016 2:26 am

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

Post by stevens »

Thanks, that works indeed in general, but not in my case.
Guess I have something wrong somewhere, but everything looks fine.

jvierra
Posts: 14358
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra »

It works in every case when you are tied to a data table object. Perhaps you are clicking on the wrong thing.

This is a standard behavior of a DataGridView attached to a DataTable object.

Also remember that numeric strings do not sort in numeric order.

User avatar
stevens
Posts: 416
Joined: Wed Jun 15, 2016 2:26 am

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

Post by stevens »

It is working fine now with the input you provided, thanks!
$dt = [System.Data.DataTable]::new()
$dt.Load($reader)
$conn.Close()
return ( ,$dt)

The columns are clickable, they focus and change color when I hover over them, so I guess it is shown as datatable but they really do not sort, whatever column I select. Any property I should change in the datagridview maybe?

jvierra
Posts: 14358
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra »

You have to click on the column header to sort the column.

User avatar
stevens
Posts: 416
Joined: Wed Jun 15, 2016 2:26 am

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

Post by stevens »

Yes, I know.

jvierra
Posts: 14358
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra »

Yes you know what? If the DataTable is correctly bound to the table then it is sortable.

Locked