CODEWITHSUNDEEP
vb.netoledb
Doug Coats
Doug Coats

Reputation: 7107

Store SQL Query into MultiDimensional Array

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()

Upvotes: 0

Views: 1222

Answers (1)

Steve
Steve

Reputation: 216293

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

Upvotes: 2

Related Questions