How to access an ADO field in a certain row of the recordset ?

[lse123]

How to access an ADO field in a certain row of the recordset ? If I have only one row may use oRS(1) for access field one , oRS(2) or oRS(field2) for access field two,....etc ? If a field of a recordset has value trip.[total expenses] where "trip" is the tablename and other part is fieldname, how to access this field, below is valid ?

totalexpensesTRIP1 = oRS("trip.[total expenses]")

[undertaker78]

<%=RecordSetName.Fields("FieldName").Value%>

[lse123]

What if field name of record set [join 2 tables] has tablename and field name is two words like trip.[total expenses] ?
Also what if record set has 3rows how access value in 2nd row 2nd field ?
Also what if record set has 1row how access value in 2nd field, may use
oRS(2) or this is error ?
I mean without a do while loop....

[Spudhead]

Try not to use recordsets directly. Query the data, then get it into an array with getRows(). Arrays are much less work for the server than a big, complex Recordset object whose extended functionality you're never going to use.

But, to answer your questions:

1. I don't think that ADO will mind a space in the field name if you're referencing it the way undertaker78 suggests. But if it does, there are a couple of ways around it. One is to use the field index rather than the name, if you know it: RecordSetName.Fields(3).Value. The other is to use an alias in your SQL: SELECT [mytable].[myField] AS myAlias.

2. I don't know your application scope but if you're looking to jump around in data like that then I'd consider rethinking it a bit. You probably shouldn't have to go jumping to the nth record. But you can do so with the Move() recordset method. See http://www.devguru.com/technologies/ado/8642.asp

Of course, if you dump the recordset into an array, all of this is academic. You just reference array elements as you would with any other.

[lse123]

I mean without a do while loop... if record set has 1-row how access value in 2nd field, may use simple oRS(2) or this is error [without referencing row without loop] and needs to use recordsetobject.MoveFirst ? if I must use this statement tell me code 3-5 lines in asp/vbscript ?

[Daemonspyre]

If you are only pulling 1 row -- then yes, you can use oRS(2). However, remember that Recordsets are 0-based, so technically the second field would be oRS(1).

You can also use oRS("field_Name") where field_Name is the name of your column in your database (less code than oRS.Fields("field_Name").value)

[lse123]

To refer field: trip.[total expenses]
this is correct
totalexpensesTRIP1 = oRS("trip.[total expenses]")

[Daemonspyre]

If that is how your Access or SQL Server is returning the field name, then yes, that would be correct.

However, that being said -- save your sanity -- use aliases as Spudhead said in his post.