# Returning Data from MySQL - 1 row vs multiple rows

**URL:** <https://forums.powershell.org/t/returning-data-from-mysql-1-row-vs-multiple-rows/16604>\
**Category:** PowerShell Help\
**Created:** [May 21, 2021, 11:40am UTC](https://forums.powershell.org/t/returning-data-from-mysql-1-row-vs-multiple-rows/16604 "2021-05-21T11:40:12Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jdobbsy1987](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/jdobbsy1987/32/23_2.png) [@jdobbsy1987](https://forums.powershell.org/u/jdobbsy1987)\
**Post date:** [May 21, 2021, 11:40am UTC](https://forums.powershell.org/t/returning-data-from-mysql-1-row-vs-multiple-rows/16604/1 "2021-05-21T11:40:12Z")

</div>

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:

```auto
$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:

```auto
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:

```auto
$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:**

```auto
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()
}

```

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:24pm UTC](https://forums.powershell.org/t/returning-data-from-mysql-1-row-vs-multiple-rows/16604/2 "2024-05-16T20:24:30Z")

</div>