VB.net OleDbDataReader 不返回所需的值

Question

>OleDb 在 VB.net 中仅返回第一列

我不知道我哪里出错了，连续盯着它看了 2 个小时，尝试了 150 种不同的变化。不知道错误在哪里，使其不返回除第一个字段之外的任何其他字段。

是的，我知道它看起来真的很混乱，我打算将 5 个读数作为一个函数，但我被这个问题压倒了

Private Sub StartBtn_Click(sender As Object, e As EventArgs) Handles StartBtn.Click
    Dim Numbers As New List(Of Integer)
    For X As Integer = 1 To 15
        Dim Num As Integer = RandomNum()
        Numbers.Add(Num)
    Next
    Dim temp_QuestionSet As New List(Of Question)
    provider = "Provider=Microsoft.ACE.OLEDB.12.0;"
    dataFile = "Data Source=C:course workdttmq.accdb"
    connString = provider & dataFile
    Try
        Connection.ConnectionString = connString
        Connection.Open()
    Catch ex As Exception
        System.Console.Beep()
        MsgBox("Error")
    End Try
    For Each Number In Numbers
        Dim _question As New Question
        _question.ID = Number
        Using Connection
            Dim command As New OleDbCommand("SELECT Question FROM final WHERE QuestionID = " & Number.ToString, Connection)
            Try
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    _question.Q = reader(0).ToString()
                End While
                reader.Close()
            Catch ex As Exception
            End Try
        End Using
        Using Connection
            Dim command As New OleDbCommand("SELECT (Answer1) FROM final WHERE QuestionID = " & Number.ToString, Connection)
            Try
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    _question.A = reader(0).ToString()
                End While
                reader.Close()
            Catch ex As Exception
            End Try
        End Using
        Using Connection
            Dim command As New OleDbCommand("SELECT Answer2 FROM final WHERE QuestionID = " & Number.ToString, Connection)
            Try
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    _question.B = reader(0).ToString()
                End While
                reader.Close()
            Catch ex As Exception
            End Try
        End Using
        Using Connection
            Dim command As New OleDbCommand("SELECT Answer3 FROM final WHERE QuestionID = " & Number.ToString, Connection)
            Try
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    _question.C = reader(0).ToString()
                End While
                reader.Close()
            Catch ex As Exception
            End Try
        End Using
        Using Connection
            Dim command As New OleDbCommand("SELECT Answer4 FROM final WHERE QuestionID = " & Number.ToString, Connection)
            Try
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    _question.D = reader(0).ToString()
                End While
                reader.Close()
            Catch ex As Exception
            End Try
        End Using
        Using Connection
            Dim command As New OleDbCommand("SELECT CorrectAnswer FROM final WHERE QuestionID = " & Number.ToString, Connection)
            Try
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    _question.Z = reader(0).ToString()
                End While
                reader.Close()
            Catch ex As Exception
            End Try
        End Using
        temp_QuestionSet.Add(_question)
    Next
End Sub

Answer 1

您在循环外部有一个连接，循环内有多个命令。虽然这倾向于正确的方向，但每个命令都包装在以连接对象为目标的Using块中。这意味着您在第一次通过循环时在第一个命令结束时释放了连接，使连接不可用。

当我在这里时，您还应该查看参数化查询。

把所有这些放在一起，再加上一些其他的更改，你最终会得到这个：

Dim temp_QuestionSet As New List(Of Question)
Private Sub StartBtn_Click(sender As Object, e As EventArgs) Handles StartBtn.Click
    temp_QuestionSet.Clear()
    Dim QuestionSql As String = "SELECT Question, Answer1, Answer2, Answer3, Answer4, CorrectAnswer FROM final WHERE QuestionID = ?"
    provider = "Provider=Microsoft.ACE.OLEDB.12.0;"
    dataFile = "Data Source=C:course workdttmq.accdb"
    connString = provider & dataFile
    Using Connection As New OleDbConnection(connString), _
          command As New OleDbCommand(QuestionSql, Connection)
        command.Parameters.Add("ID", OleDbType.Integer)
        Connection.Open()
        For Each Number In Enumerable.Range(0,15).Select(Function(i) RandomNum())
            command.Parameters("ID").Value = Number
            Try
                Dim reader As OleDbDataReader = command.ExecuteReader()
                While reader.Read()
                    temp_QuestionSet.Add(New Question With {
                    .ID = Number,
                    .Q = reader("Question").ToString(),
                    .A = reader("Answer1").ToString(),
                    .B = reader("Answer2").ToString(),
                    .C = reader("Answer3").ToString(),
                    .D = reader("Answer4").ToString(),
                    .Z = reader("CorrectAnswer").ToString())
                End While
                reader.Close()
            Catch ex As Exception
               MsgBox("Error: " & ex.Message)
               Console.Beep()
            End Try
        Next
    End Using
End Sub

我还会考虑将其简化为一次访问您的数据库以一次性加载所有问题，然后打乱结果。我提供的代码应该可以轻松朝这个方向前进，您只需更改 SQL 命令即可完成此操作。