Your source for ASP-related frequently asked questions and answers! : ASP FAQS : Databases, General

Question: My memo fields show up as blank! OR When I test my memo field for a value and then try to show it only if it is not blank [or only if it contains some string or or or], then when I display it I get nothing!

Answer: To use Memo fields in Access (and longer VARCHAR fields in various other DBs), you must "follow the rules." Which are:

(1) The memo field should be the last field in your list of fields.
(2) You can only retrieve the value of a memo field from the recordset ONE TIME.

Or you can follow the other set of rules, below...


Often this means that you must specifically call out the fields you are reading, via

SELECT fld1, fld2, ... memoFld FROM table ...

instead of simply

SELECT * FROM table ...

And then you *must* copy the memo field to a VBS variable and thereafter use the variable, thus:

theText = RS("memoFld")
If Trim(theText) = "" Then
    Response.Write theText
End If

Do you see it? If you had coded Response.Write("memoFld") in both of the places I used the theText variable there, the second time you would always get a null value!!!

Another set of rules

Thanks to user "Stacy" for these rules!

Stacy has verified that they work in all the following combinations. We invite you to try them on your system and report the results:
Access - using a System DSN
Access - using ODBC Microsoft Access Driver (*.mdb) and a DSN-less connection
Access - using Microsoft.Jet.OLEDB.4.0 and a DSN-less connection
SQL Server 2000 - using a System DSN
SQL Server 2000 - using ODBC Driver={SQL Server} and a DSN-less connection
SQL Server 2000 - using OLE DB Provider=SQLOLEDB and a DSN-less connection

And the technique is simple:
Rule 1: Be sure to use ADODB.RecordSet.Open to create the recordset object. (That is, do not use ADODB.Connection.Execute or ADODB.Command.Execute!)
Rule 2: Set the cursor location to the client.
Rule 3: Use adOpenKeyset as the open mode for the recordset.

So, in code:

Set RS = Server.CreateObject("ADODB.RecordSet")
RS.CursorLocation = adUseClient
RS.Open table_name_or_sql_query, your_connection_object, adOpenKeyset

Stacy reports that if you do this, you no longer have to worry about the order of the fields or the other restrictions noted above.

FAQ posted by Bill Wilkinson at 11/7/2000 9:12:07 PM to the Databases, General category. This FAQ has been viewed 58,032 times.

Copyright 2019 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers