ConvertDataset for GridView 'Invalid storage type: DBNull.'

[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]

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

[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]

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.

[stevens]

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

[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.

[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]

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

[stevens]

Yes, I know.

[jvierra]

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