Doug Coats Doug Coats - 2 months ago 24
Vb.net Question

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

Answer

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
Comments