Unable to get datagrid updated.

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.
Locked
SondelaConsult
Posts: 4
Joined: Fri May 31, 2019 5:57 am

Unable to get datagrid updated.

Post 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

User avatar
Alexander Riedel
Posts: 7378
Joined: Tue May 29, 2007 4:43 pm

Re: Unable to get datagrid updated.

Post by Alexander Riedel »

[Moved by moderator]

Would be helpful to know what the error is.
Alexander Riedel
SAPIEN Technologies, Inc.

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

Re: Unable to get datagrid updated.

Post by jvierra »

The following line is wrong:
$datagridview1.Rows.Add($Results)

It should be:
$datagridview1.Rows.AddRange($Results)

SondelaConsult
Posts: 4
Joined: Fri May 31, 2019 5:57 am

Re: Unable to get datagrid updated.

Post 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:

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

Re: Unable to get datagrid updated.

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

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

Re: Unable to get datagrid updated.

Post by jvierra »

The following query is wrong. You cannot set a wildcard and use "As Names". You must specify a specific field by name.

SondelaConsult
Posts: 4
Joined: Fri May 31, 2019 5:57 am

Re: Unable to get datagrid updated.

Post 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

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

Re: Unable to get datagrid updated.

Post 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

SondelaConsult
Posts: 4
Joined: Fri May 31, 2019 5:57 am

Re: Unable to get datagrid updated.

Post by SondelaConsult »

Thanks .. That helps. in terms of its no longer errroing. but the table is completely blank.

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

Re: Unable to get datagrid updated.

Post by jvierra »

Which means your SQL is not returning anything.

Locked