CODEWITHSUNDEEP
excelpowershelloledb
jonnybot
jonnybot

Reputation: 2453

OleDbConnection becomes System.Object[] when passed to Function

I have a PowerShell script for reading from an Excel file using OLEDB. I made a Function that sets up the connection based on some parameters, which works just fine. It creates the System.Data.OleDb.OleDbConnection connection, which I then pass to other functions to get additional information about the sheet (worksheet name, column names).

The functions all work great until I pass the connection object to a function that actually queries the primary worksheet in the spreadsheet. Right before that function is called, I can verify that the connection object is still a System.Data.OleDb.OleDbConnection object using the GetType() method. Inside the function, though, it suddenly becomes a System.Object[], before my function has done any work at all.

Thus, when I try to assign the connection to a new OleDbCommand object, I receive the error:

Exception setting "Connection": "Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.D ata.OleDb.OleDbConnection"." At D:\Scripts\AdvancementSpreadsheetImport.ps1:130 char:18 + $command. <<<< Connection = $connection + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyAssignmentException

Take the code outside of the function block and put it in the main body of the script, and everything works.

Any idea what gives?

Create Connection:

Function connectToSheet($sheetPath) {
    try {
        #Construct the data source
        $provider = "Provider=Microsoft.ACE.OLEDB.12.0" #Microsoft.Jet.OLEDB.4.0"
        $dataSource = "Data Source=`"$sheetPath`""
        $extend = 'Extended Properties="Excel 12.0 Xml;HDR=YES;"'
        $connection = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend;")
        $connection.Open()
        log("Connection to $sheetPath open")
        return $connection
    }
    catch {handleError("Error opening OLE connection to the spreadsheet $sheetPath.")}
}

The offending function:

Function constructIndexFile($connection, $sheetName, $outputPath, $outputFileName) {
    log("Connection info: $($connection.GetType())") #You can see that the connection object's type has changed at this point
    try {
        $query = "select [col1], [col2] from [$sheetName] where [col1] <> `"`""
        $command = New-Object System.Data.OleDb.OleDbCommand($query)

The next line triggers the error.

        $command.Connection = $connection 
        $DataReader = $command.ExecuteReader()
        $indexFileStream = [System.IO.StreamWriter] "$outputFileName"
        While ($DataReader.read()) {
            ##Do stuff to construct my index file
        }
        $indexFileStream.close()
        log("Created index file $outputFileName")
    }
    catch {handleError("Error constructing the index file")}
}

Upvotes: 0

Views: 549

Answers (1)

Bill_Stewart
Bill_Stewart

Reputation: 24565

PowerShell doesn't use parentheses to call a function; instead, pass the parameters after the function name separated by spaces. Thus instead of

function_name(param[, ...])

write

function_name param [...]

Upvotes: 3

Related Questions