displaying multiple values with .FIELDS adodb

Question

i am getting some data:

rs.Filter = "datapath='" + dpath + "' and analystname='" + aname + "' and reportname='" + rname + "' and batchstate='" + bstate + "'"
If Not rs.EOF Then
    MsgBox rs.Fields("rowid")
End If

if the rs.filter returns multiple records, how do i get rs.fields("rowid") to give me multiple rowid values?

Answer 1

do while not (rs.eof and not rs.bof)

   MsgBox rs.Fields("rowid") 
   rs.movenext()

end while

Syntax is probably wrong but thats the idea.

Answer 2

If you simply call rs.Fields("rowId"), you are actually asking for the column value of the current row which is likely to be the first row. Suppose the recordset returns 10 rows, what do you want to do with the rowId value for each of the rows? Show them each in a message box? That means 10 message boxes. If that is really what you wanted to do, you would do something like:

rs.Filter = "datapath='" + dpath + "' and analystname='" + aname + "' and reportname='" + rname + "' and batchstate='" + bstate + "'"
Do Until rs.EOF
    MsgBox rs.Fields("rowid").Value
    rs.MoveNext
Loop

This assumes that rs is a forward-only recordset. If you are using a keyset cursor, then you will likely have to call MoveFirst().

rs.Filter = "datapath='" + dpath + "' and analystname='" + aname + "' and reportname='" + rname + "' and batchstate='" + bstate + "'"
rs.MoveFirst()
Do Until rs.EOF
    MsgBox rs.Fields("rowid").Value
    rs.MoveNext
Loop

Answer 3

This is ADO, so use GetString:

MsgBox rs.GetString

Check first that some records were returned.

More info: http://www.w3schools.com/ado/met_rs_getstring.asp