ACCESS memo fields in ASP

[Hugh Welford]

Hi ... using WIN XP/ FP 2000/ IIS.

I need to be able to retieve variable length (>255 characters) messages
which have been saved in a memo field in an access data base on the server.
I am using the following code to test this : -

<%
set objconn = server.createobject ("ADODB.connection")
set objrec = server.createobject ("ADODB.recordset")
objconn.open "DSN=daters"
strsql = "SELECT DISTINCT messages.* FROM messages WHERE messid = 305 ;"
objrec.open strsql, objconn, adopenforwardonly, adlockoptimistic,adcmdtext
%>

<%=objrec("text")%>

The message is found and retrieved but truncated to 255 characters as if it
were an ACCESS text field not a memo field.

The message which is originally user created on another page, writes in full
to the ACCESS memo field. Also, if I download the data, and retrieve the
message offline via an ACCESS form, it displays fully. Seems then like an
ADO issue in retrieving memo fields (probably my syntax???) - any ideas
please???

Getting desperate

Thanks Hugh

 

[Bob Barrows [MVP]]

Hi ... using WIN XP/ FP 2000/ IIS.

I need to be able to retieve variable length (>255 characters)
messages which have been saved in a memo field in an access data base
on the server. I am using the following code to test this : -

<%
set objconn = server.createobject ("ADODB.connection")
set objrec = server.createobject ("ADODB.recordset")
objconn.open "DSN=daters"

I would switch to the native Jet OLEDB provider instead of ODBC. Here is a
sample connection string:

objconn.open "Provider=microsoft.Jet.oledb.4.0;" & _
"Data Source=p:\ath\to\database.mdb"

If your database is password protected, or uses workgroup security, see
www.able-consulting.com/ado_conn.htm for the syntax to use in each of these
cases. See http://www.aspfaq.com/show.asp?id=2168 if your Access database is
on a remote server.

strsql = "SELECT DISTINCT messages.* FROM messages WHERE messid = 305
;"

You should explicitly name the fields to be returned by your query. Reserve
selstar (select *) for one-off ad hoc queries. Using selstar impairs
performance due to the need for ADO to make an extra trip to the database to
get the actual names of the fields being retruned by the query. In addition,
it often results in data being retrieved from the database that is not
needed.

HTH,
Bob Barrows

 

[Aaron Bertrand - MVP]

In addition to Bob's comments, see http://www.aspfaq.com/2188

(I doubt you need DISTINCT, unless you think two people with the same userid
will post the same message at the same time.)

 

[Hugh Welford]

Hi Bob - thanks for your help. I explicitly named the required field as
suggested and also removed the "DISTINCT" operator from the sql statement
and all is well now.

Regards Hugh

 

[hugh Welford]

Thanks AAron - it was the superfluous DISTINCT that caused the problem.

regards Hughg