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
” fromfor ($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()
}