ArgumentOutOfRangeException，我无法弄清楚为什么

I'm building a PowerShell script that retrieves some info stored in a MS Access 2013 database file. The query is being built dynamically from some other data collected on the machine. I went for a full modular approach and put all of the database functions and management in a separate module that I am bringing in with a "Using Module" statement (the functionality is to be rather large, so I thought this would make it easier to manage the code). Everything appears to be working well except for the openRecordSet function.

我正在构建一个PowerShell脚本，用于检索存储在MS Access 2013数据库文件中的一些信息。查询是从机器上收集的其他一些数据动态构建的。我采用了完整的模块化方法，并将所有数据库功能和管理放在一个单独的模块中，我使用“使用模块”语句引入（功能相当大，所以我认为这样会更容易管理代码）。除了openRecordSet函数之外，一切似乎都运行良好。

Here is the constructor for my Database class as well as my connectToDatabase method and openRecordSet method:

这是我的Database类的构造函数以及我的connectToDatabase方法和openRecordSet方法：

Database($dbPath) {
    $this.connectToDatabase($dbPath)
}

connectToDatabase($Db) {
    $this.databasePath = $Db
    $this.connection = New-Object -ComObject ADODB.Connection
    $this.connection.Open("Provider = Microsoft.ACE.OLEDB.12.0;Data Source='$Db'")
    if ($this.connection -eq $NULL -OR $this.connection.State -eq $this.adStateClosed) {
        throw "$this.DatabasePath found but a valid connection could not be made."
    }
}

[object] openRecordSet($query, $cursorType, $lockType) {
    $RecordSet = New-Object -ComObject ADODB.Recordset
    Write-Host "query = $query"
    return $RecordSet.Open($query, $this.connection, $cursorType, $lockType)
}

This is the code giving me problems. It is in another class that has successfully created a database object and connection by the time execution reaches this line:

这是给我问题的代码。它是在另一个类中，在执行到达此行时成功创建了数据库对象和连接：

$RecSet = $this.database.openRecordSet($query, 3, 1)

Using Visual Studio Code's debug mode, a Write-Host statement and running it in MS Access, I have confirmed that the SQL statement inside $query is proper. The second parameter is the cursor type, which is Dynamic in this instance, and the third parameter is the lock type, which is ReadOnly in this instance. I was using constants I had defined in the Database class but changed them to primitive numeric values to confirm that those weren't causing the problem. The error did not change. Here is the error message:

使用Visual Studio Code的调试模式，Write-Host语句并在MS Access中运行它，我已经确认$ query中的SQL语句是正确的。第二个参数是游标类型，在此实例中为Dynamic，第三个参数是lock类型，在此实例中为ReadOnly。我使用的是我在Database类中定义的常量，但是将它们更改为原始数值以确认那些不会导致问题。错误没有改变。这是错误消息：

Index was out of range. Must be non-negative and less than the size of the
collection.
Parameter name: index
At C:\Users\FakeUser\Documents\MySuperScript\modules\userprofiles.psm1:20 char:21
+ ...                  $RecSet = $this.database.openRecordSet($query, 3, 1)
+                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], ArgumentOutOfRangeException
    + FullyQualifiedErrorId : System.ArgumentOutOfRangeException

I can't figure out what is causing this error. $query is a string and $this.connection is a comObject Connection. I've used this same syntax in a different script with no issues, it was only when I started playing with modules that this happened. Surely it's a simple syntax problem or some other easy thing I'm overlooking.

我无法弄清楚导致此错误的原因。 $ query是一个字符串，$ this.connection是一个comObject Connection。我在不同的脚本中使用了相同的语法，没有任何问题，只有当我开始使用模块时才发生这种情况。当然，这是一个简单的语法问题或其他一些我忽略的简单事情。

1 个解决方案

#1

The return type of the Open method is void. The error pops up because you are casting void to object in order to return it. Here is a listing of ADODB.Recordset

Open方法的返回类型为void。弹出错误是因为您要将void转换为对象以便返回它。这是ADODB.Recordset的列表

_xClone():ADODB.Recordset (Method)
_xResync(AffectRecords:ADODB.AffectEnum):System.Void (Method)
_xSave(FileName:System.String, PersistFormat:ADODB.PersistFormatEnum):System.Void (Method)
AbsolutePage:ADODB.PositionEnum (Get Property)
AbsolutePage:System.Void (Set Property)
AbsolutePosition:ADODB.PositionEnum (Get Property)
AbsolutePosition:System.Void (Set Property)
ActiveCommand:System.Object (Get Property)
ActiveConnection:System.Object (Get Property)
ActiveConnection:System.Void (Set Property)
AddNew(FieldList:System.Object, Values:System.Object):System.Void (Method)
BOF:System.Boolean (Get Property)
Bookmark:System.Object (Get Property)
Bookmark:System.Void (Set Property)
CacheSize:System.Int32 (Get Property)
CacheSize:System.Void (Set Property)
Cancel():System.Void (Method)
CancelBatch(AffectRecords:ADODB.AffectEnum):System.Void (Method)
CancelUpdate():System.Void (Method)
Clone(LockType:ADODB.LockTypeEnum):ADODB.Recordset (Method)
Close():System.Void (Method)
Collect:System.Object (Get Property)
Collect:System.Void (Set Property)
CompareBookmarks(Bookmark1:System.Object, Bookmark2:System.Object):ADODB.CompareEnum (Method)
CursorLocation:ADODB.CursorLocationEnum (Get Property)
CursorLocation:System.Void (Set Property)
CursorType:ADODB.CursorTypeEnum (Get Property)
CursorType:System.Void (Set Property)
DataMember:System.String (Get Property)
DataMember:System.Void (Set Property)
DataSource:System.Object (Get Property)
DataSource:System.Void (Set Property)
Delete(AffectRecords:ADODB.AffectEnum):System.Void (Method)
EditMode:ADODB.EditModeEnum (Get Property)
EOF:System.Boolean (Get Property)
Fields:ADODB.Fields (Get Property)
Filter:System.Object (Get Property)
Filter:System.Void (Set Property)
Find(Criteria:System.String, SkipRecords:System.Int32, SearchDirection:ADODB.SearchDirectionEnum, Start:System.Object):System.Void (Method)
GetRows(Rows:System.Int32, Start:System.Object, Fields:System.Object):System.Object (Method)
GetString(StringFormat:ADODB.StringFormatEnum, NumRows:System.Int32, ColumnDelimeter:System.String, RowDelimeter:System.String, NullExpr:System.String):System.String (Method)
Index:System.String (Get Property)
Index:System.Void (Set Property)
let_ActiveConnection(pvar:System.Object):System.Void (Method)
let_Source(pvSource:System.String):System.Void (Method)
LockType:ADODB.LockTypeEnum (Get Property)
LockType:System.Void (Set Property)
MarshalOptions:ADODB.MarshalOptionsEnum (Get Property)
MarshalOptions:System.Void (Set Property)
MaxRecords:System.Int32 (Get Property)
MaxRecords:System.Void (Set Property)
Move(NumRecords:System.Int32, Start:System.Object):System.Void (Method)
MoveFirst():System.Void (Method)
MoveLast():System.Void (Method)
MoveNext():System.Void (Method)
MovePrevious():System.Void (Method)
NextRecordset(RecordsAffected:System.Object&):ADODB.Recordset (Method)
Open(Source:System.Object, ActiveConnection:System.Object, CursorType:ADODB.CursorTypeEnum, LockType:ADODB.LockTypeEnum, Options:System.Int32):System.Void (Method)
PageCount:System.Int32 (Get Property)
PageSize:System.Int32 (Get Property)
PageSize:System.Void (Set Property)
Properties:ADODB.Properties (Get Property)
RecordCount:System.Int32 (Get Property)
Requery(Options:System.Int32):System.Void (Method)
Resync(AffectRecords:ADODB.AffectEnum, ResyncValues:ADODB.ResyncEnum):System.Void (Method)
Save(Destination:System.Object, PersistFormat:ADODB.PersistFormatEnum):System.Void (Method)
Seek(KeyValues:System.Object, SeekOption:ADODB.SeekEnum):System.Void (Method)
Sort:System.String (Get Property)
Sort:System.Void (Set Property)
Source:System.Object (Get Property)
Source:System.Void (Set Property)
State:System.Int32 (Get Property)
Status:System.Int32 (Get Property)
StayInSync:System.Boolean (Get Property)
StayInSync:System.Void (Set Property)
Supports(CursorOptions:ADODB.CursorOptionEnum):System.Boolean (Method)
Update(Fields:System.Object, Values:System.Object):System.Void (Method)
UpdateBatch(AffectRecords:ADODB.AffectEnum):System.Void (Method)

Slightly modified example using your code:

使用您的代码略微修改的示例：

Class Database  {
    [string] $databasePath
    $connection
    $adStateClosed

    Database($dbPath){
          $this.connectToDatabase($dbPath)
    }

    connectToDatabase($Db){
        $this.databasePath = $Db
        $this.connection = New-Object -ComObject ADODB.Connection
        $this.connection.Open("Provider = Microsoft.ACE.OLEDB.12.0;Data Source='$Db'" )
        if($this.connection -eq $NULL -OR $this.connection.State -eq $this.adStateClosed){
            Throw "$this.DatabasePath found but a valid connection could not be made."
        }
    }
    [object] openRecordSet([string] $query, $cursorType, $lockType){
        $RecordSet = new-object -ComObject ADODB.Recordset
        $RecordSet.Open($query, $this.connection, $cursorType, $lockType)
        return $RecordSet
    }
}


$db = [Database]::new("C:\MonthlySalesReport\MonthlySalesReports.accdb")
$query = "SELECT 1 AS CONNECTED"
$result = $db.openRecordSet($query, 3, 1)

for ($i = 0; $i -lt $result.RecordCount; $i++) {
    $record = $result[$i]
    Write-Host ($record.Name + " " + $record.Value)
}