28 - Sep - 2005
Reading memo fields in Access using asp and ado
Posted by Paul Farrow - Under: Software
as a reminder to myself really, in access 2000 there is now a memo field these can only be read once from the ado recordset in asp. The second time the field will be null, ie if you do the following…
<%= myRS("linkname") %>
<%= myRS("linkname") %>
twice the second one doesnt return anything as it loses the field contents, I believe this is by design
All Entries
Thank you! For years I have struggled with this issue. Usually, the server I am working with handles the memo field type just fine. Once in a while, I wind up working with a server that doesn’t. Usually, I can just use long text fields. But in one case, today, I could not. Because of your tip, I changed my code so that the first time I look at the memo field in the code, I save its contents to a new variable, then I test for null value. Before, I was checking for null value first, then saving contents to a variable. Thanks to you I now know the importance of order in this situation. Thanks again!
(Just passing through, found this on a Google search having hit the same problem.)
Your solution works, but the “real” solution is to put the memo field at the end of the SELECT query. So if you have a memo field somewhere in the middle of your column structure, you need to rearrange the field order in the SELECT and not use ‘SELECT * FROM’
I’m working on Access 2010 Database with IIS7.5, classic ASP site.
The problem with second read of the memo – type field occurs always. It not depend on field order in SQL query.
When I made:
SELECT * FROM some_table
or
SELECT some_memo_field, some_text_field FROM some_table
or
SELECT some_text_field, some_memo_field FROM some_table
or even (!)
SELECT some_memo_field_only FROM some_table
The second read (access) of the field “some_memo_field” always contains “” (null):
Effect of ASP field read like:
response.write “Memo: ” & some_memo_field & “”
response.write “Memo: ” & some_memo_field & “”
in every case is:
Memo: memo_field_value
Memo:
I really don’t know where the problem is, the only solution is probably as Mike Landers wrote: to store the value of memo type fields on first access to some temporary string, and then use it.
I lost lot of hours to find this bug, I just don’t imagine that value of some field can be destroyed/lost just in effect of checking it (access/read).
With best regards!