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 » Wed Jun 05, 2019 5:14 am

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: 7023
Joined: Tue May 29, 2007 4:43 pm

Re: Unable to get datagrid updated.

Post by Alexander Riedel » Wed Jun 05, 2019 6:14 am

[Moved by moderator]

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

User avatar
jvierra
Posts: 13736
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Unable to get datagrid updated.

Post by jvierra » Wed Jun 05, 2019 6:44 am

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 » Wed Jun 05, 2019 7:25 am

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:

User avatar
jvierra
Posts: 13736
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Unable to get datagrid updated.

Post by jvierra » Wed Jun 05, 2019 7:31 am

You are not returning any data in you code so the variable is null. THat is what the error message is telling you.

User avatar
jvierra
Posts: 13736
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Unable to get datagrid updated.

Post by jvierra » 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.

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

Re: Unable to get datagrid updated.

Post by SondelaConsult » Wed Jun 05, 2019 8:03 am

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

User avatar
jvierra
Posts: 13736
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Unable to get datagrid updated.

Post by jvierra » Wed Jun 05, 2019 8:43 am

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 » Wed Jun 05, 2019 11:50 am

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

User avatar
jvierra
Posts: 13736
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Unable to get datagrid updated.

Post by jvierra » Wed Jun 05, 2019 11:59 am

Which means your SQL is not returning anything.

Locked