Store SQL Query into MultiDimensional Array

Question

A the title suggests I am attempting to store a query into a 2-D array. I must be doing something wrong as it seems it is only storing the last row in the array in the (0,0) through (0,5) column (i guess thats just the first column lol)

Before it is suggested that I use a list, my next step is to randomize the array to output something different each time its called. That part i have figured out but I keep stumbling on this read to array nonsense.

Here is what I currently have:

    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    dataFile = "C:\Users\Ashleysaurus\Documents\test.accdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString
    Dim Str As String
    Dim dr As OleDbDataReader
    Dim myArray(2, 5) As String

    myConnection.Open()
    Str = "SELECT Question, Answer1, Answer2, Answer3, Answer4, CorrectAnswer FROM Critters;"
    Dim cmd As OleDbCommand = New OleDbCommand(Str, myConnection)
    dr = cmd.ExecuteReader

    While dr.Read()
        Dim i As Integer = 0
        myArray(i, 0) = CType(dr("Question"), String)
        myArray(i, 1) = CType(dr("Answer1"), String)
        myArray(i, 2) = CType(dr("Answer2"), String)
        myArray(i, 3) = CType(dr("Answer3"), String)
        myArray(i, 4) = CType(dr("Answer4"), String)
        myArray(i, 5) = CType(dr("CorrectAnswer"), String)
        i = i + 1
    End While

    myConnection.Close()

If you declare and initialize to 0 the variable i at every loop what else do you expect to happen here? Move the declaration and the initialization outside the loop. By the way, do you know that a DataTable is just a glorified array with a lot of extra gadget to work with data? — Steve
– Steve, Commented Sep 24, 2016 at 21:14
You'd be much better off normalizing that table - you could easily repeat answers and store it more compactly — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Sep 24, 2016 at 21:16
(almost) Anytime you have columns named xxx1 xxx2 xxx3, you have missed a relationship in the DB design. As is, it is rigid: 4 candidate answers and one of them is repeated as the correct one. An answer table would be more flexible with one candidate tagged as correct for this question — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Sep 24, 2016 at 21:21

Steve · Accepted Answer · 2016-09-24 21:23:51Z

2

You have a simple error. The declaration and the initialization of the variable i should go outside the loop

Dim i As Integer = 0
While dr.Read()
  ....

But I prefer a lot to use a DataTable with code like this

Dim dt = new DataTable()
dt.Load(cmd.ExecuteReader)

Now you can use the DataTable more or less like a two dimensional array

Dim question as string = dt.Rows(0)(0)

for each row as DataRow in dt.Rows
   Console.WriteLine("Question = " & row(0).ToString())
Next

answered Sep 24, 2016 at 21:23

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doug Coats Over a year ago

this might prove be to be more simplistic. Thanks

Collectives™ on Stack Overflow

Store SQL Query into MultiDimensional Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related