Recordset with Memo Field from MS Access only displays 255 characters

[firsttube]

I have an Access database with memo field in it. On my ASP page I create an ADODB recordset and the write the values of all the fields in all the records. When the memo field writes out, it only displays the first 255 characters from the memo field, even though there are as much as 1000 characters stored in the field.

How can I get the recordset to retrieve all the data from the Memo field?

sqlStat = "SELECT Comments from tbl_Main WHERE ID=" & ID & ";"
rs.open sqlStat, dCon

if not rs.eof then
response.write(rs("Comments"))
end if

rs.close


Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[Sheco]

Try using the GetChunk method.

http://www.w3schools.com/ado/met_field_getchunk.asp

 

[damber]

Also if you're using odbc, try changing to a DSN-Less connection.

http://www.connectionstrings.com/

Do a search on this forum and on Google and you'll get a few posts about issues with 'memo' (blob/image) field data retrieval, especially over ODBC, though not exclusively.

GetRows can also help alleviate in-pipe truncation caused by the data bridge (e.g. ODBC) - but again it's not consistent.


A smile is worth a thousand kind words. So smile, it's easy!

 

[firsttube]

Thanks for the help! I tried the getchunk method and it had no effect.

I have not tried the DSN-less connection, but would like to stay away from that.

I also tried putting the comments field at the end of the sql statement, adn that had no effect either.

For the interim, I opened another recordset with just the comments field in it and that seemed to work.

An other ideas?

Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[damber]

I have not tried the DSN-less connection, but would like to stay away from that.

Any particular reason ? It's faster and more robust, and will likely resolve your issue. Follow the link to the connection strings site and it will show you how for your database of choice (even Access).

The method you also tried usually requires you to put the blob fields at the start of the SELECT statement and read them first.

You can also try GetRows as I suggested above, which creates an array of your data from the recordset. This *might* resolve your problem. It is also a much more efficient way of processing recordset data - especially for large recordsets - unless you need the advanced methods of the recordset object, which most people don't for a web app.

I haven't used ODBC for a long time due to these very reasons. I almost always use a DSN-less connection and GetRows (I have a custom class object that manages the data in the same way as a recordset object, just less bulky, e.g. faster, but that's beside the point)

Try it, you just might like it...

A smile is worth a thousand kind words. So smile, it's easy!

 

[Sheco]

GetChunk only pulls back as many as its "size" parameter specifies. If the size is smaller than the field then it needs to be called more than once. You could use a loop to pull back 100 at a time and keep pulling 100 each time around the loop until the length of the returned bytes is zero.