Search code examples
databasevb.netms-access

Array index filling from 4th record of an Access database

I'm a very beginner programmer and don't know much programming so sorry if the code is messy.

I'm trying to read from an Access database into an array of records but it reads the 4th record (3rd if I use a fixed loop and not an If statement) into the first index of the array.

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

Here is an image of the database:

enter image description here

The data types of the fields match those of what is in the record and they get displayed, only starting from the 3rd/4th record and then going right up to the 29th and then the 1st to 2nd/3rd.

Any assistance would be appreciated.

Solution

  • You will need to sort the results in the way you prefer, so add an ORDER BY clause to your query:

    "SELECT * FROM [TBA DATA] ORDER BY [Order]"