How do I SELECT and store multiple rows of a database with the same ID using OLEDB Connection in VisualBasic

Question

    Private Sub GetAffectedTraits(ByVal QuestionIndex)
        NumberOfTraitsAffected = 0
        Dim IndexTracker As Integer

        Dim TraitsAffected(23) As String
        'Set all to Null value
        For i As Integer = 1 To 23
            TraitsAffected(i) = " "
        Next

        Dim Command As New OleDbCommand("SELECT [Affected Trait] FROM Sheet1 WHERE QuestionIndex=" & QuestionIndex & "", TraitValuesConn)
        DB_Reader = Command.ExecuteReader
        While DB_Reader.Read()
            TraitsAffected(IndexTracker) = DB_Reader("Affected Trait")
            IndexTracker += 1
        End While

        For i As Integer = 1 To 23
            MsgBox(TraitsAffected(i)) 'Does not include affected traits yet for some reason
        Next
    End Sub

In this code, I'm trying to select and store each 'Affected Trait' from the database stored with the same ID (or QuestionIndex). I want them to be stored separately in the TraitsAffected array. This is what I am currently trying but it does not change the array at all yet. I want to stick with the syntax I have been using with the OLEDB connection.

This is for an academic assignment.

Answer 1

If you're not sure of the number of affected traits, it's better to use List instead of Array.

Check the following code and see if it works for you.

Private Sub GetAffectedTraits(ByVal QuestionIndex)
    Dim NumberOfTraitsAffected = 0
    Dim TraitsAffected As List(Of String) = New List(Of String)

    Using TraitValuesConn As OleDbConnection = New OleDbConnection("your connection string")
        TraitValuesConn.Open()

        Dim Command As New OleDbCommand("SELECT [Affected Trait] FROM Sheet1 WHERE QuestionIndex=" & QuestionIndex & "", TraitValuesConn)
        Dim DB_Reader = Command.ExecuteReader
        While DB_Reader.Read()
            TraitsAffected.Add(DB_Reader("Affected Trait"))
        End While
    End Using

    For Each value As String In TraitsAffected
        MsgBox(value)
    Next
End Sub