System.Data 中发生了 'System.IndexOutOfRangeException' 类型的第一次异常.dll

Question

我刚刚将这段代码移到一个新的sub中，并尝试运行它并获得tite中所说的异常。

Public Sub LoadPanel(TableName As String)
    Debug.Print("LoadPanel:") 'name of module
    PushCallStack("LoadPanel") 'name of module
    Try
        Using dbConnection As New OleDbConnection(My.Settings.SMSA_databaseConnectionString)
            dbConnection.Open()
            Debug.Print("   dbConnection Success")
            Dim sqlcommand As String
            sqlcommand = "SELECT " & TableName & ".[PanelName] FROM  " & TableName & " WHERE (((" & TableName & ".[Owner])=" & CurrentClientID & ")) OR (((" & TableName & ".[Admin1])=" & CurrentClientID & ")) OR (((" & TableName & ".[Admin2])=" & CurrentClientID & ")) OR (((" & TableName & ".[Admin3])=" & CurrentClientID & ")) OR (((" & TableName & ".[Admin4])=" & CurrentClientID & "));"
            Debug.Print("   sqlcommand = " & sqlcommand)
            Dim getSomeData As New OleDbCommand(sqlcommand, dbConnection)
            With getSomeData
                .Parameters.AddWithValue("@variableName", "variableValue")
                Debug.Print("   Executing: getSomeData")
            End With
            Debug.Print("   Success: getSomeData")
            Debug.Print("   Try: DataReader")
            Using dataReader As OleDbDataReader = getSomeData.ExecuteReader
                Do While dataReader.Read
                    Dashboard_Client.ComboBox_PanelChoice.Items.Add(dataReader("PanelIP"))
                Loop
            End Using
        End Using
        PopCallStack() 'do not remove
    Catch ex As Exception
        GlobalErrHandler()
    End Try
    Debug.Print("LoadPanel End")

从调试面板：

LoadPanel:
dbConnection Success
Executing: getSomeData
Success: getSomeData
Try: DataReader
A first chance exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll

有没有办法解决这个错误或进行变通以保持功能？

谢谢。

Answer 1

在行中

  Dashboard_Client.ComboBox_PanelChoice.Items.Add(dataReader("PanelIP"))

您正在尝试读取名为PanelIP的字段，但查询无法检索到该字段。
这会导致IndexOutOfRangeException，因为在内部，读取器试图将该名称（PanelIP）转换为检索到的字段列表中的某个位置，当该名称不存在时，该位置被假定为-1。当然，在检索到的字段列表中，-1是无效索引

话虽如此，让我们看看您的查询以及如何修复它。

我希望TableName变量处于您的严格控制之下，因为用户可以传递该变量中的任何内容，这可能会导致一种称为Sql Injection 的危险情况

需要修复的是丢失的字段，并将currentClientID的每个字符串连接更改为适当的参数

Dim sqlcommand = "SELECT " & TableName & ".[PanelName] " & _ 
                             TableName & ".[PanelID] " & _
                 " FROM  " & TableName & _ 
                 " WHERE (" & TableName & ".[Owner])=@ownerID) " & _
                 " OR    (" & TableName & ".[Admin1])=@clientID1) " & _
                 " OR    (" & TableName & ".[Admin2])=@clientID2) " & _   
                 " OR    (" & TableName & ".[Admin3])=@clientID3) " & _ 
                 " OR    (" & TableName & ".[Admin4])=@clientID4)"
Try
    Using dbConnection As New OleDbConnection(.....)
    Using getSomeData As New OleDbCommand(sqlcommand, dbConnection)
        dbConnection.Open()
        With getSomeData
            ' In oledb you need to add a parameter for every placeholder
            ' because the parameters are not recognized by name but by position
            .Parameters.Add("@ownerID", OleDbType.Integer).Value = currentClientID
            .Parameters.Add("@clientID1", OleDbType.Integer).Value = currentClientID
            .Parameters.Add("@clientID2", OleDbType.Integer).Value = currentClientID
            .Parameters.Add("@clientID3", OleDbType.Integer).Value = currentClientID
            .Parameters.Add("@clientID4", OleDbType.Integer).Value = currentClientID
        End With
        Using dataReader As OleDbDataReader = getSomeData.ExecuteReader
            Do While dataReader.Read
                Dashboard_Client.ComboBox_PanelChoice.Items.Add(dataReader("PanelIP"))
            Loop
        End Using
    End Using
    End Using
    PopCallStack()
Catch ex As Exception
    GlobalErrHandler()
End Try

遗憾的是，无法参数化TableName，因为参数只能用于值，而不能用于字段或表名

Answer 2

您的查询中没有variableName参数，您正试图添加一的值。这会导致IndexOutOfRangeException。

您可以包含参数或删除

.Parameters.AddWithValue("@variableName", "variableValue")