How to Get a Recordset into an Array?

General
1

Hi everybody.

I always deal with recordset in order to do querys and display to a Listbox.
But now that I’m dealing with thread’s I learn the lesson very well. “Don’t invoke any objects of the GUI”, like Listboxes, buttons, etc.

I use a method to get into an Array the content of a Listbox:

  ReDim my2DArray(-1,-1) 'Preventing If the Array has data stored before.
    
  ReDim my2DArray(Listbox.ListCount - 1, Listbox.ColumnCount - 1) 'It resize the Array based on listbox's columns
  
  For i as integer=0 to listbox.listcount -1 'Counting the Rows
    
    For j as integer=0 to listbox.ColumnCount -1 'Counting the Columns
      my2dArray(i,j) = listbox.Cell(i,j)
     
            
    Next
  Next

So, But in this case, I need to catch directly the content of the recordset directly into an array.
And once the array is filled, Then populate the listbox with the array.

At this way I have the data displayed on my listbox but also present on the array, for using it on a Thread.

I now that I can count the records in a Rows in a Recordset doing “recordSet.Recordcount”, But I have not idea how to count Columns on a Recordset.

Because RecordSet.FieldCount works for counting each field and the recordset (equivalent to a cell in a table).

Any suggestions?

2

Well, a recordset does not have “columns” per se, it depends on how you have placed your data into the DB. What data is stored in each column in your Listbox example above?

3 
redim arr(rs.recordCount-1,rs.fieldCount-1)
dim row as integer
while not rs.EOF
for col=0 to rs.fieldCount-1
   arr(row,col)=rs.IdxField(col+1).stringvalue
next col
row=row+1
rs.movenext
wend

One can equate a “recordset COLUMN” to its field… however its up to you via your SELECT statement to return them in the desired order

4

Well, I have columns in mySQL Database:

  • DECIMAL(15,2) for store currency values
  • TEXT for text
  • VARCHAR(36) for text with numbers, delimiting to 36 characters
  • DATETIME to Store Date values
  • INT for values without decimals
  • TINYINT(1) for boolean values
5

Technically those are “Fields”, but in some nomenclatures they are refered to as “Columns”

Is there a reason the code I posted doesn’t work for your need?, or did I misunderstand your requirements?

6

[quote=276088:@Dave S]redim arr(rs.recordCount-1,rs.fieldCount-1)
dim row as integer
while not rs.EOF
for col=0 to rs.fieldCount-1
arr(row,col)=rs.IdxField(col+1).stringvalue
next col
row=row+1
rs.movenext
wend[/quote]

Thanks Dave, As I read that is that I want to do, I gonna test it. You’re a Genius!

7

[quote=276088:@Dave S]Technically those are “Fields”, but in some nomenclatures they are refered to as “Columns”

Is there a reason the code I posted doesn’t work for your need?, or did I misunderstand your requirements?[/quote]
Once Again I say Dave, Thanks so much It works me a lot.

So then for populate my listbox I made this:

dim sizeX As integer = Ubound(my2DArray, 1) //For Row
dim sizeY As Integer = UBound(my2dArray, 2) //For Column

recibidas.Columncount = sizeY //Telling the number of columns

For x As Integer = 0 to SizeX
listbox.AddRow("")
For y As Integer = 0 to SizeY
listbox.Cell(x,y) = my2dArray(x,y)
listbox.CellTAG(x,y) = my2dArray(x,y)
Next

Next

8

In SQL these are neither called “Fields” nor “Columns”, but “Attributes” (or “Named attributes”). SQL tables don’t have an order of the attributes of a relation (= a table).

9

Geez Eli… its a matter of nomenclature… Depending on where/how you learned your craft.

To ME, database RECORD is a ROW (or record), a data element within that ROW is a FIELD, all of the same FIELD in the TABLE is referred to as a COLUMN, and each COLUMN has ATTRIBUTES (data type, length, defaults etc)