vb.net System.Data.dll 中发生类型为“System.IndexOutOfRangeException”的第一次机会异常

A first chance exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll

提问by Devanuze

I've just moved this code to a new sub and tried to run it and get the exception as said in the tite.

我刚刚将此代码移动到一个新的子程序并尝试运行它并获得如 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")

From the debug panel:

从调试面板：

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

Is there a way to resolve this error or make a work around in order to keep the functionality?

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

Thanks.

谢谢。

回答by Steve

In this line

在这一行

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

you are trying to read a field named PanelIP, but your query doesn't retrieve it.
This causes the IndexOutOfRangeException, because, internally, the reader tries to convert that name (PanelIP) to a position in the list of fields retrieved and when the name doesn't exist then the position is assumed to be -1. Of course -1 is an invalid index in the list of fields retrieved

您正在尝试读取名为 的字段PanelIP，但您的查询未检索到它。
这会导致IndexOutOfRangeException, 因为在内部，读取器尝试将该名称 (PanelIP) 转换为检索到的字段列表中的位置，并且当该名称不存在时，则假定该位置为 -1。当然 -1 是检索到的字段列表中的无效索引

Said that, let's take a look at your query and how to fix it.

说了这么多，让我们来看看您的查询以及如何解决它。

I hope for you that the TableName variable is under your strict control because a user could pass anything in that variable and this could lead to a dangerous situations called Sql Injection

我希望您对 TableName 变量进行严格控制，因为用户可以在该变量中传递任何内容，这可能导致称为 Sql 注入的危险情况

The things to fix are the missing field and change every string concatenation of currentClientID to an appropriate parameter

要解决的问题是缺少的字段并将 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

Sadly there is no way to parameterize also the TableName because parameters can be used only for values not for fields or table names

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

回答by Tushar

There is no variableNameparameter in your query ad you are trying to add the value of one .This causes the IndexOutOfRangeException.

variableName您尝试添加值 1 的查询广告中没有参数。这会导致IndexOutOfRangeException.

You mus include the parameter or just remove

你必须包括参数或只是删除

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