No value given for one or more required parameters

PowerShell Help
1

My project continues.

I’m writing a powershell 5.1 script to query an access db/mdb file to get info, but get the above error when I try to use a variable in my select statement. It works fine when I hard-code what I’m retrieving from the db.

I get this error message when I try to open/access an mdb file:

Open done
No value given for one or more required parameters.

If I can’t add the variable into the select, this is worthless, since I have over a hundred to look up. This is what I’m doing:

   $pathViewBase = 'C:\Data\EndToEnd_view\' #View dir. 
   $XML_MDB_Dirs = @('\AppText.mdb') #more files later
   foreach($mdbFile in $XML_MDB_Dirs)
   {
      $pathToMdb = Join-Path -Path $pathViewBase -ChildPath $mdbFile
      if(Test-Path $pathToMdb)
      {
        $cn = new-object -comobject ADODB.Connection
        $rs = new-object -comobject ADODB.Recordset
        $selectQuery = “SELECT TOP 1 [TableName].[Message Description],
        [TableName].[ColumnName]
        FROM [TableName]
        WHERE [TableName].[Message Description] = $mdbLookupString”
        Write-Host "selectQuery: $selectQuery" -ForegroundColor Cyan  #looks good
        Write-Host "MDB Open next" -ForegroundColor DarkCyan
        $cn.Open("Provider = Microsoft.ACE.OLEDB.16.0;Data Source = $pathToMdb") #works
        Write-Host "Open done" -ForegroundColor DarkCyan
        #error next line
        $rs.Open($selectQuery,
            $cn, $adOpenStatic, $adLockOptimistic)
        Write-Host "select done"
        $rs.MoveFirst()
        Write-Host "Message value obtained for ERROR: " 
        $rs.Fields.Item("English - Us").Value
      }#test-Path
}#foreach

$temp = $rs.Fields.Item(“English - Us”).Value
Write-Host “item: $temp” -ForegroundColor Cyan
$cn.Close()

I looked at what google told me (which site won’t let me share for some reason), and they are telling me that my column name is wrong. But looking at what prints when I print the selectQuery, it’s fine. Any ideas? Maybe there’s a trick to get it to use the variable in Open?

This is my output:
Open done
No value given for one or more required parameters.
…
select done

FYI, this works fine, but I can’t permanently hard code what’s to the right of =:
$rs.Open(“SELECT *
FROM [TableName]
WHERE [TableName].[Message Description] = ‘ERROR’”,
$cn, $adOpenStatic, $adLockOptimistic)

I tried using the above in $selectQuery with the variable for ‘ERROR’, but it still has the same error.

Other suggestions would be fine too, if this selectQuery can’t be a variable, like returning the query results and obtaining each item from that somehow.

2

It’s working!!! I used what they are doing for a sql variable in the select here: sql

$selectQuery = “SELECT *
FROM [TableName]
WHERE [TableName].[Message Description] = ‘$($mdbLookupString)’”