When I run a SELECT statement using Invoke-Sqlcmd, I'd like to choose 1 out of X results to move forward with a loop. In the example below, I have multiple results for 'Bob' and I'd like to choose the appropriate one to continue working with the query to update his favFood (I know I don't have the code there but just giving an example of what I have so far)
$nameList = $(Write-Host "Enter a list of Names seperated by a comma (ex.`"Bob, Rick, Jordan\`"): " -ForegroundColor Green -NoNewline; Read-Host)
$nameList = $nameList.Split(', ')
ForEach ($element in $nameList)
{
Invoke-Sqlcmd -Server Server01 -database People -Query ` "SELECT firstName, lastName, address, favFood`
FROM People ` WHERE firstName = '$element'" }
Is there a way I can list a first name and have the program ask me which of X results I'd like to update?
I'm honestly a bit stumped on how to choose the specific row/person out of X results to update.
Small side-note/bonus points: When running the program it complains about a "Missing argument in parameter list." as I'm writing down the names even though it will accept and return results as expected (I am separating the names by a space and comma ex. "Bob, Ross, Jay"
In theory you don't need a loop at all, you could use the SQL IN operator and pass all the names of $nameList to your query. Then once you have stored the $result of your query, you can use Out-GridView -PassThru to have a popup DataGridView for you to choose a row, which would be stored in $choice.
Write-Host 'Enter a list of Names seperated by a comma (ex."Bob, Rick, Jordan"): ' -ForegroundColor Green -NoNewline
$nameList = (Read-Host).Split(',').Trim()
# $namelist would become Bob','Rick','Jordan
$nameList = $nameList -join "','"
$result = Invoke-Sqlcmd -Server Server01 -Database People -Query @"
SELECT firstName, lastName, address, favFood
FROM People WHERE firstName IN ('$namelist');
"@
$choice = $result | Out-GridView -PassThru