Volatility of query results in asp

Question

I have seen a couple of instances now where some legacy code I'm working on exhibits what seems buggy behovior and I wonder if I just don't understand something.

  rsCaspio.Open sql, dbCaspio, ,adLockReadOnly
  response.write(rsCaspio("fieldname") & "<BR>")
  response.write(rsCaspio("fieldname") & "<BR>")

It will write the contents the first time but the second time it is blank. Is this not unexpected and I just don't know something about queries or is there something really odd happening that I need to dig into deeper? Perhaps results aren't guaranteed after the first read? This code has been working but with a few changes it has suddenly gone blank. The original programmer would read once and reference the result several times and in several routines which is scary, but the example above is from my debugging and the lines are right next to each other.

If it was asp.net I'd look for overloaded stuff or freaky pointer math since it's legacy, but that shouldn't be the case in good old ASP, right?

Thanks!

Answer 1

Yes, pretty much as Chris just said. I found this problem occuring with older versions of SQL Server. I think the solution is any TEXT columns have to appear last in your select statement, and you can't read them twice.

I think there is nothing else you can do about it. Maybe update some ADO internals or SQL Server itself - possibly it is an obscure setting in SQL Server, but I never found it.