Access数据库数组索引填充异常：第4条记录写入数组首索引求助

我是编程新手，代码若有混乱之处还请见谅。

我尝试将Access数据库中的数据读取到记录数组中，但发现第4条记录（若使用固定循环而非If语句则为第3条）被写入数组的首个索引。数据字段类型与记录中的类型匹配，数据能正常显示，但顺序异常：先填充第3/4条至第29条记录，之后才填充第1至2/3条记录。

代码示例 #

Sub GetChoices()
        Dim counter As Integer

        Try
            Dim SQLReader As OleDbDataReader
            Dim connection_type As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
            Dim file_location As String = "Data Source=D:\ADV H CS\TBA Project\TBADATA.accdb"
            Dim conn As OleDbConnection
            conn = New OleDbConnection(connection_type & file_location)
            conn.Open()

            Dim query As String = "SELECT * FROM [TBA DATA]"
            Dim command As New OleDbCommand(query, conn)
            SQLReader = command.ExecuteReader()

            counter = 0
            'Dim condition As Integer = 0 (condition I use for a conditional breakpoint)

            If SQLReader.HasRows Then
                While SQLReader.Read

                    Scenario(counter).Situation = SQLReader("Scenario")
                    Scenario(counter).Option1 = SQLReader("Option1")
                    Scenario(counter).Option2 = SQLReader("Option2")
                    Scenario(counter).PlayerChoice1 = SQLReader("PlayerChoice1")
                    Scenario(counter).PlayerChoice2 = SQLReader("PlayerChoice2")

                    'condition = condition + 1 (condition I use for a conditional breakpoint)

                    If counter < 28 Then
                        counter = counter + 1
                    End If
                End While
            Else
                RichTextBox1.Text = "No Results Returned"
            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

数据库情况 #

数据库表[TBA DATA]包含Scenario、Option1、Option2、PlayerChoice1、PlayerChoice2等字段，记录在界面中按连续序号顺序排列。

核心问题分析 #

你遇到的顺序异常，本质是SQL查询未指定明确的排序规则。Access数据库执行SELECT * FROM [TBA DATA]时，返回的记录顺序是基于存储物理顺序或主键索引顺序，和你在数据库界面看到的显示顺序不一定一致。

步骤1：给查询添加排序规则 #

假设你的表有一个用于标识记录顺序的自增ID字段（比如名为ID），修改SQL语句为：

SELECT * FROM [TBA DATA] ORDER BY ID ASC

如果没有ID字段，也可以用能确定顺序的业务字段排序，比如：

SELECT * FROM [TBA DATA] ORDER BY Scenario ASC

步骤2：优化计数器逻辑 #

你代码中If counter < 28 Then counter = counter + 1的写法，会导致当counter达到28后，后续读取的所有记录都会覆盖Scenario(28)的值。如果你的数组Scenario长度为29（索引0到28），应该改为同时限制读取条件：

While SQLReader.Read AndAlso counter <= 28
    ' 赋值逻辑
    counter = counter + 1
End While

这样当counter超过数组上限时会自动停止读取，避免覆盖最后一个元素。

修改后的完整代码 #

Sub GetChoices()
        Dim counter As Integer

        Try
            Dim SQLReader As OleDbDataReader
            Dim connection_type As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
            Dim file_location As String = "Data Source=D:\ADV H CS\TBA Project\TBADATA.accdb"
            Dim conn As OleDbConnection
            conn = New OleDbConnection(connection_type & file_location)
            conn.Open()

            ' 添加ORDER BY指定排序字段，根据你的表实际字段调整
            Dim query As String = "SELECT * FROM [TBA DATA] ORDER BY ID ASC"
            Dim command As New OleDbCommand(query, conn)
            SQLReader = command.ExecuteReader()

            counter = 0

            If SQLReader.HasRows Then
                ' 同时限制计数器不超过数组上限
                While SQLReader.Read AndAlso counter <= 28

                    Scenario(counter).Situation = SQLReader("Scenario")
                    Scenario(counter).Option1 = SQLReader("Option1")
                    Scenario(counter).Option2 = SQLReader("Option2")
                    Scenario(counter).PlayerChoice1 = SQLReader("PlayerChoice1")
                    Scenario(counter).PlayerChoice2 = SQLReader("PlayerChoice2")

                    counter = counter + 1
                End While
            Else
                RichTextBox1.Text = "No Results Returned"
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            ' 确保数据库连接关闭，避免资源泄漏
            If conn IsNot Nothing AndAlso conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try

内容的提问来源于stack exchange，提问作者Le7tch