Classic ASP: RecordSet Field giving no value

Question

I'm not really an ASP developer, so a little bit lost.

I have the following data access code:

sSQL = "SELECT answer_id, company_name, old_access_company_name, answer, flag_asker_notified FROM Q01_ask_sheiiba_answer_company2 WHERE question_id="& sQuestion_id &" ORDER BY answer_id"
rs.open sSQL, conn  
DO WHILE NOT rs.EOF
    Response.Write(rs.Fields("answer"))
    rs.MoveNext
LOOP

I have tested that the sql query is built properly by outputting it to the response before it is called. It produces the following query:

SELECT answer_id, company_name, old_access_company_name, answer, flag_asker_notified 
FROM Q01_ask_sheiiba_answer_company2 
WHERE question_id=988 
ORDER BY answer_id

When I copy that exact query to sql management studio and run it I get the expected results of 5 rows and each row containing data in every cell, BUT, when I run it through the above code, I get the same 5 rows with the same cell data, EXCEPT for the answer column, which is empty!

What am I missing?

Thanks in advance

Answer 1

There are two things you might want to try:

Put your text field at the end of the query. For example:

SELECT answer_id, company_name, old_access_company_name, flag_asker_notified, answer

If this doesn't give you the results, you might want to try:

WHILE NOT rs.EOF
theanswer=rs("answer")
Response.Write(theanswer)
rs.movenext
wend

Text and Memo fields can play a little havoc with ASP.

EDIT: Another thing that you can try is:

rs.CursorLocation = adUseClient

or

rs.CursorLocation = 3

Answer 2

The problem is that the ODBC driver can't access large text blobs as strings; you need to access them as chunked BLOB data.

I advise instead to dump the ODBC connection and connect using the OLE-DB driver directly. This will let you access that column as if it was just another varchar column.