ACCESS memo fields in ASP

H

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
 
B

Bob Barrows [MVP]

Hugh said:
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
 
H

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
 
H

hugh Welford

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

regards Hughg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top