Returning Data from MySQL - 1 row vs multiple rows

PowerShell Help
#1

Hi,

TL:DR - SOLVED
I managed to fix this just as i was about to hit post and thought i may as well post it in case it offers some help to anyone else Google’ing in the future.

As well as enclosing the query in @() as mentioned below which should have fixed my issue, I also had an issue with the for loop, once i removed “.Rows” from for ($i = 0; $i -lt $result.Rows.Count; $i++) it worked.

I have a MySQL script I call to make queries to a MySQL database, this works but i have noticed that i get a different result that affects my ability to populate a datagridview depending on if the returned MySQL data has 1 or more than 1 row.

If i get more than 1 row, everything works but if i only get 1 row then it fails.

For Example:

$result = Invoke-MySQL_ps1 -Query "SELECT orderNumber AS ""OrderNumber"", requestDate AS ""RequestDate"", name AS ""RequestedBy"", supplier AS ""Supplier"" FROM requests WHERE STATUS = '10';"

Returns several rows without issue and populates a datagridview using this:

while ($datagridviewAwaitingAuth.Rows.Count -lt $result.Count)
	{
		$datagridviewAwaitingAuth.Rows.Add() | Out-Null
	}
	
	for ($i = 0; $i -lt $result.Rows.Count; $i++)
	{
		$datagridviewAwaitingAuth.rows[$i].Cells["orderNumber"].Value = $result[$i].Item("OrderNumber")
		$datagridviewAwaitingAuth.rows[$i].Cells["requestDate"].Value = $result[$i].Item("RequestDate")
		$datagridviewAwaitingAuth.rows[$i].Cells["requestedBy"].Value = $result[$i].Item("RequestedBy")
		$datagridviewAwaitingAuth.rows[$i].Cells["Supplier"].Value = $result[$i].Item("supplier")
	}

However, if the initial query returns only 1 record/row, then the datagridview isn’t populated.

As a test i output the results to the host and this was the results:

$result = Invoke-MySQL_ps1 -Query "SELECT orderNumber AS ""OrderNumber"", requestDate AS ""RequestDate"", name AS ""RequestedBy"", supplier AS ""Supplier"" FROM requests WHERE STATUS = '10';"
Write-Host $result[0].Item("OrderNumber")

With Multiple Results:

Xnh-0519-143409 ## This is the expected value

With One Result:

ERROR: Method invocation failed because [System.String] does not contain a method named ‘Item’.

I have tried to investigate this and believe it’s because when only 1 row is returned Powershell doesn’t create an array but read that enclosing the query inside @() should force it to be an array.

I tried that but that also didn’t work.

MySQL_ps1 Script for Reference:

Param (
	[Parameter(
				  Mandatory = $true,
				  ParameterSetName = '',
				  ValueFromPipeline = $true)]
	[string]$Query
)
$MySQLAdminUserName = '$username'
$MySQLAdminPassword = '$password'
$MySQLDatabase = '$database'
$MySQLHost = '$host'
$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database=" + $MySQLDatabase
Try
{
	Add-Type -Path 'MySql.Data.dll'
	$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
	$Connection.ConnectionString = $ConnectionString
	$Connection.Open()
	$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
	$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
	$DataSet = New-Object System.Data.DataSet
	$RecordCount = $dataAdapter.Fill($dataSet, "data")
	$DataSet.Tables[0]
}
Catch
{
	Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
}
Finally
{
	$Connection.Close()
}