Adding recordset-data to an array

[Razzle]

I want to put the data returned from an SQL query to an array.
As far as I know I can get the data if it's only a single value like this:

Text1.text = rc.Fields("xyz")

But if there are more values returned it doesn't work because only the first value is shown in the text field.
Now, if I got a table called users and I want to know the names of all users who have blue hair I would try this:

Dim db As Database
Dim rs As Recordset, SQL$
Set db = OpenDatabase("C:\My Documents\mytestdb.mdb")
SQL = "SELECT username FROM users where haircolor like ""blue"""
Set rs = db.OpenRecordset(SQL)

But how can I put the names returned (and the other stuff) to an array like this:

Type TUser
username as String
haircolor as String
password as String
End Type
Dim userlist() as TUser

what you want can't be done that way, but you could use GetString to get a complete recordset into a string with delimiters to get a similar result.

best regards

Sascha

[Razzle]

Of which object is GetString a method?
I couldn't find it at db (Database) nor at rs (recordset)

GetString is a method of ADODB-recordsets, so you could swithch from DAO to ADO.

mfg

Sascha

Well Razzle, your sql statement needs to be modified...

SQL = "SELECT username,password,haircolor FROM users where haircolor like ""blue"""

In ur sql query you are selecting only the username into the recordset. Try the above query and you can get the data in the fields using

recordset.fields(index).value

Even in the above query the haircolor will be blue as u r retrieving the records where haircolor is blue. So please look into it and u can always improve on it...

keep in touch...

With regards...vijay.

i forgot to mention, when using DAO, you could still use rs.GetRows to get the complete Recordset into a 2-dimensional array. That's maybe not too efficient, but it can be usefull sometimes.

good luck

Sascha

[jmcswain]

Try this:

Type TUser
username as String
haircolor as String
password as String
End Type
Dim userlist() as TUser
Dim lng As Long
Dim db As Database
Dim rs As Recordset, SQL$
Set db = OpenDatabase("C:\My Documents\mytestdb.mdb")
SQL = "SELECT username, password, haircolor FROM users where haircolor like ""blue"""
Set rs = db.OpenRecordset(SQL)

rs.MoveLast
rs.MoveFirst
Redim TUser(rs.RecordCount)
Do Until rs.EOF
TUser(lng).username = rs.username
TUser(lng).password = rs.password
TUser(lng).haircolor = rs.haircolor
lng = lng + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

For lng = LBound(TUser) To UBound(TUser)
Debug.Print lng & ": " & TUser(lng).username & "," & TUser(lng).password & "," & TUser.haircolor
Next

[jmcswain]

Gee, I wish my indenting had been included. It'll still work, though.

[Razzle]

Thanks a lot, I'll try it that way :)

[lengwai]

Hie all,

By Using getString(), i am changing the representation of recordset in String. That's fine. But after converting it to a String is there a way for me to revert that particular string back to recordset again? It is just out of my curiousity. I used to do this in Java quite often. So i am wondering if it can be done in VB as well.

If there is an answer to this please post it. I am working with an example. Thank you so much,

Happy New YEar,
dave

[techgnome]

Originally posted by lengwai
Hie all,

By Using getString(), i am changing the representation of recordset in String. That's fine. But after converting it to a String is there a way for me to revert that particular string back to recordset again? It is just out of my curiousity. I used to do this in Java quite often. So i am wondering if it can be done in VB as well.

If there is an answer to this please post it. I am working with an example. Thank you so much,

Happy New YEar,
dave
In addition to GetString, there is also a GetRecords that will return the recordset as a two dimentional array.....