ASP/MySql, can only refer to a recordset once

I have run into a weird problem, developing an ASP site with MySql.

When I extract data from the database, say using this code:

Query = "Select * from tablename where Rel_ID = " & Service_RS("ID") & " And OrderNum = 1"
Set Result_RS = Conn.Execute(Query)

I can only refer to data from the recordset once. This means, the following:

Response.Write Result_RS("ColName")
SaveData = Server.URLEncode(Result_RS("ColName"))

Will give an error in the last line, since the recordset holds no data. I find this very weird, since I have been developing on this application for a while without issues. Is there a setting in MySql/IIS configuration that I have forgotten to switch off/on?

I have never run into this problem before, however it is worth noting that this particular page is one that does a lot of SQL queries and has a lot of recordset variables.

The database is at this point not very sizable, so I'm sure thats not the issue.

Any inputs on this one?

 

There are 2 kinds of recordset. One is a forward only recordset. It seems like you are using that type of recordset storing the data returned by your SQL statement. That's why you can only refer it once.

 

Always use an
BOF
and EOF

before starting the Loop

as this will take care of no data situations

see the code below

if rsStats.eof = false then

do until rsStats.eof = true

rsStats.movenext
loop

else

 

if rsStats.eof = false then

do until rsStats.eof = true
<!--do something-->
rsStats.movenext
loop
End if

 

What exactly is the purpose of the If ... End If block?

(That's a rhetorical question...)

 

if your recordset has no records

then the following statement will give errors
do until rsStats.eof = true

So you are doing an contingency check

before looping

 

Think carefully about what you are saying.

The reality is that if the recordset has no records, the loop will never be entered, thereby preventing errors.

The " = True" portion is also redundant. The following code would accomplish the exact same thing as the original code:

Do Until rsStats.EOF
    <!--do something-->
    rsStats.MoveNext
Loop

Code (markup):

If EOF is True (as in, not False), then the loop will never be entered. The If ... End If block is unnecessary.