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.