Page 1 of 1
Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 5:14 am
by SondelaConsult
Hello I am trying to pull some data from an SQL Database into a DataGridView. But I am not getting an ouput and get an error. what is wrong with my code.
Code: Select all
$buttonAddData_Click={
#TODO: Place custom script here
$name = $textbox1.Text
$LastName = $textbox2.Text
$DisplayName = $textbox3.Text
$insertquery = "
INSERT INTO [dbo].[Play]
([FirstName]
,[LastName]
,[DisplayName])
VALUES
('$name'
,'$LastName'
,'$displayname')
GO
"
Invoke-SQLcmd -ServerInstance 'W10-PC-HOME' -query $insertquery -U sa -P Year2000 -Database Playpen
$results = Invoke-Sqlcmd -ServerInstance "W10-PC-HOME" -Database "Playpen" -Query "SELECT * FROM Play"
$Results = Invoke-Sqlcmd -ServerInstance localhost -Database playpen -Query "SELECT * As Names FROM Play" | ForEach-Object {
$($_.Names)
}
# Display the results of the SQL Query
$datagridview1.Rows.Add($Results)
}
Product, version and build: 5.6.163
32 or 64 bit version of product: 64
Operating system: Windows 10
32 or 64 bit OS: 64Bit
Re: Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 6:14 am
by Alexander Riedel
[Moved by moderator]
Would be helpful to know what the error is.
Re: Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 6:44 am
by jvierra
The following line is wrong:
$datagridview1.Rows.Add($Results)
It should be:
$datagridview1.Rows.AddRange($Results)
Re: Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 7:25 am
by SondelaConsult
Thank you.. I Have tried that but still get the error
ERROR:
ERROR: Exception calling "AddRange" with "1" argument(s): "Value cannot be null.
ERROR: Parameter name: dataGridViewRows"
Name Test.psf (43, 2): ERROR: At Line: 43 char: 2
ERROR: + $datagridview1.Rows.AddRange($Results)
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : ArgumentNullException
ERROR:
Re: Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 7:31 am
by jvierra
You are not returning any data in you code so the variable is null. THat is what the error message is telling you.
Re: Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 7:35 am
by jvierra
The following query is wrong. You cannot set a wildcard and use "As Names". You must specify a specific field by name.
Re: Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 8:03 am
by SondelaConsult
jvierra wrote: ↑Wed Jun 05, 2019 7:35 am
The following query is wrong. You cannot set a wildcard and use "As Names". You must specify a specific field by name.
Ok thanks So I tried that but still get an error.
This is my line.
$Results = Invoke-Sqlcmd -ServerInstance localhost -Database playpen -Query "SELECT FirstName As Names FROM Play" | ForEach-Object {
$($_.Names)
}
Gives an error still.
So basically how do I get it to display FirstName, Last Name and Display Name in a grid.
>> Running (Name Test.psf) Script...
>> Platform: V5 64Bit (STA) (Elevated) (Forced)
ERROR: Cannot convert argument "dataGridViewRows", with value: "System.Object[]", for "AddRange" to type "System.Windows.Forms.DataGridViewRow": "Cannot convert the
ERROR: "System.Object[]" value of type "System.Object[]" to type "System.Windows.Forms.DataGridViewRow"."
Name Test.psf (43, 2): ERROR: At Line: 43 char: 2
ERROR: + $datagridview1.Rows.AddRange($Results)
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodException
ERROR: + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument
ERROR:
*** PowerShell Script finished. ***
>> Execution time: 00:00:09
>> Script Ended
Re: Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 8:43 am
by jvierra
Since Invoke-SqlCmd returns a datatable just assign that to the grid.
Code: Select all
$p = @{
ServerInstance = 'localhost'
Database = 'playpen'
Query = 'SELECT * FROM Play'
OutputAs = 'Datatable'
}
$dt = Invoke-Sqlcmd @p
$datagridview1.DataSource = $dt
Re: Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 11:50 am
by SondelaConsult
Thanks .. That helps. in terms of its no longer errroing. but the table is completely blank.
Re: Unable to get datagrid updated.
Posted: Wed Jun 05, 2019 11:59 am
by jvierra
Which means your SQL is not returning anything.