Problem: Recordset contains up to 10 questions (Fields are sQ1 through

sQ10). There are multiple records with differing questions. Some have 10 and

some have less. I need to select a given recordset and then present only the

valid questions.  In the following record there are only 5 questions. It

seems that once a field have been used/called, it is no longer available.

The results could be assigned to an array for further use, but that seems a

bit clumsy.



Partial Code:



Set oConn=Server.CreateObject("ADODB.connection")

...

strSQL="SELECT * FROM Questions WHERE ID=" & iLastRecord

Set oRs=oConn.Execute (strSQL)



For iCount = 1 to 10

Response.Write(iCount & " -- " & oRs.Fields("sQ" & iCount & "") & "<BR>")

Next



Response.Write("3 Again -- " & oRs.Fields("sQ3") & "<BR>")



oRs.Close

oConn.Close

Set oConn=Nothing



-----------



Results:



1 -- Quest 1

2 -- Quest 2

3 -- Quest 3

4 -- Quest 4

5 -- Quest 5

6 --

7 --

8 --

9 --

10 --

3 Again --





Question: Why is nothing returned with oRs.Fields("sQ3") the second time? It

doesn't matter if it's the For...Next construct or individual requests,

additional calls return nothing.



Ideas?

Hi Curtis,



A recordset returned by the Connection.Execute method is ALWAYS a read-only 

and forward-only cursor. (also called a firehose cursor)

With your 1 to 10 loop, you are moving forward in the recordset. If you try 

to write number 3 again, you' d have to move back again, which is not 

possible because of the cursor.



You have a couple of options if you insist on referencing number 3 again:



1. Assign it to a variable. This is, I think, the fastest solution to build

2. Assign the complete recordset to an array, with .GetRows. Then use a 

loop to write out the array. Although it may seem "clumsy", when it comes 

to performance this is your fastest solution.

3. First, create a recordset and set the correct cursor options. The open 

it with the SQL statement and connection. Then you can move backwards and 

forward at will.

This option is considered "expensive" since these kinds of recordsets are 

rather slow.



Hope this helps,



Imar





At 09:29 AM 3/28/2001 -0600, you wrote:

>Problem: Recordset contains up to 10 questions (Fields are sQ1 through

>sQ10). There are multiple records with differing questions. Some have 10 and

>some have less. I need to select a given recordset and then present only the

>valid questions.  In the following record there are only 5 questions. It

>seems that once a field have been used/called, it is no longer available.

>The results could be assigned to an array for further use, but that seems a

>bit clumsy.

>

>Partial Code:

>

>Set oConn=Server.CreateObject("ADODB.connection")

>...

>strSQL="SELECT * FROM Questions WHERE ID=" & iLastRecord

>Set oRs=oConn.Execute (strSQL)

>

>For iCount = 1 to 10

>Response.Write(iCount & " -- " & oRs.Fields("sQ" & iCount & "") & "<BR>")

>Next

>

>Response.Write("3 Again -- " & oRs.Fields("sQ3") & "<BR>")

>

>oRs.Close

>oConn.Close

>Set oConn=Nothing

>

>-----------

>

>Results:

>

>1 -- Quest 1

>2 -- Quest 2

>3 -- Quest 3

>4 -- Quest 4

>5 -- Quest 5

>6 --

>7 --

>8 --

>9 --

>10 --

>3 Again --

>

>

>Question: Why is nothing returned with oRs.Fields("sQ3") the second time? It

>doesn't matter if it's the For...Next construct or individual requests,

>additional calls return nothing.

>

>Ideas?