Solved

Memo fields displays only 255 characters

Posted on 2004-04-03
16
827 Views
Last Modified: 2008-02-01
I have field in an Access 2002 database that is set to "memo" because it contains large amounts of text.

However, when I display the contents of the field on the web (using ASP vbscript) only 255 characters show up.

Here's an example: http://www.ihea.info/pages/details.asp?pubID=8

The paragraph "contents" stops at the word "thickening" even though there's more text inside the database field.

What could be wrong?
0
Comment
Question by:polaatx
  • 5
  • 5
  • 3
16 Comments
 
LVL 1

Expert Comment

by:jmcraig
ID: 10751081
If the Acces database is passing the memo field to a string variable on the ASP page then the limit is determined by the ASP page. Since a string variable can only hold 255 charcters by default then your memo field is still being truncated/cut.

Try these lines of code

Dim strMemoFieldData
strMemoFieldData = Space(1200)

You can then pass the Memo field data into the variable "strMemoFieldData"

0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10751257
Hi polaatx,

I think you are passing the data from your Table to ASP through a query,
in the query you have done grouping,
if grouping is used, it will ONLY get 255 characters.
So the way around this is to read your data from the Table after filtering it.

Hope this helps

Jaffer
0
 

Author Comment

by:polaatx
ID: 10752548
Jaffer, I am not sure my problem is what you describe or what jmcraig describes , or both.

Regarding your solution. Yes there was grouping done.

Here's my SQL right now:

SELECT *
FROM qryDetails
WHERE PubID = MMColParam

If you understand you correctly, it would be something like:

SELECT qryDetails.*, tblBrochures.description
From qryDetails, tblBrochures
WHERE PubID = MMColParam

but when I put this into Dreamweaver's Recordset wizard, Dreamweaver just takes out the mention of tblBrochures.description automatically because I guess it assumes it is already part of query.

Below is the SQL in the query. Can you please help me figure out what kind SQL to give Dreamweaver to incorporate your solution?

SELECT tblBrochures.PubID, tblBrochures.HardCopyAvailable, tblBrochures.PubCode, tblBrochures.PublicationPending, tblBrochures.Activation, tblLanguages.LanguageName, tblTitle.TitleName, tblSpecialPopulation.SpecialPopulation, tblDisease.DiseaseName, tblCreationStatement.creationStatement, tblTitle.description, tblBrochures.pdfLocation, tblBrochures.thumbnail, tblBrochures.DonationPriceSchedule, tblBrochures.DownloadAvailable, tblBrochures.Description, tblBrochures.PubDate, tblBrochures.RevisionDate, tblTitle.OriginalBrochureID, tblDownloadDonation.PriceUnder10, tblDownloadDonation.Price100, tblDownloadDonation.Price250, tblDownloadDonation.Price500, tblDownloadDonation.Price1000, tblDownloadDonation.Price5000
FROM tblDownloadDonation RIGHT JOIN ((tblCreationStatement RIGHT JOIN (tblLanguages RIGHT JOIN (tblTitle RIGHT JOIN (tblSpecialPopulation RIGHT JOIN tblBrochures ON tblSpecialPopulation.SpecialID = tblBrochures.SpecialPopID) ON tblTitle.titleID = tblBrochures.Title) ON tblLanguages.LanguageID = tblBrochures.LanguageID) ON tblCreationStatement.statementID = tblTitle.creationStatement) LEFT JOIN (tblDisease RIGHT JOIN tblTitleDisease ON tblDisease.DiseaseID = tblTitleDisease.DiseaseID) ON tblTitle.titleID = tblTitleDisease.TitleID) ON tblDownloadDonation.DonationID = tblBrochures.DonationPriceSchedule
GROUP BY tblBrochures.PubID, tblBrochures.HardCopyAvailable, tblBrochures.PubCode, tblBrochures.PublicationPending, tblBrochures.Activation, tblLanguages.LanguageName, tblTitle.TitleName, tblSpecialPopulation.SpecialPopulation, tblDisease.DiseaseName, tblCreationStatement.creationStatement, tblTitle.description, tblBrochures.pdfLocation, tblBrochures.thumbnail, tblBrochures.DonationPriceSchedule, tblBrochures.DownloadAvailable, tblBrochures.Description, tblBrochures.PubDate, tblBrochures.RevisionDate, tblTitle.OriginalBrochureID, tblDownloadDonation.PriceUnder10, tblDownloadDonation.Price100, tblDownloadDonation.Price250, tblDownloadDonation.Price500, tblDownloadDonation.Price1000, tblDownloadDonation.Price5000;
0
 

Author Comment

by:polaatx
ID: 10752576
jmcraig,

How would I pass the Memo field data into the variable "strMemoFieldData"?

I tried the following, but got a "name redefined" asp error:

<%Dim strMemoFieldData
strMemoFieldData = Space(1200)

strMemoFieldData =(rsDetails.Fields.Item("tblBrochures.Description").Value)
%>
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10753071
polaatx
At least I know you have a problem from the query, as grouping will truncate the memo field to 255 characters.
let me guide you to another similar question.
There you will find at least 2 answers, 1 from me for using the Dlookup, and the other from nico5038,

My solution:
1- Don't read this memo field from the query,
2- in the control source of the memo field in the Report, use the Dlookup function (place the "where" conditions here) to get the memo info and use CanGrow too,

http://www.experts-exchange.com/Databases/MS_Access/Q_20898838.html

I hope this helps you further.

jaffer
0
 
LVL 1

Expert Comment

by:jmcraig
ID: 10753115
Paste the code on the page up to the troublesome paragraph + 10 additional lines and I will take a look
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:polaatx
ID: 10753184
jmcraig,

Here's the code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<!--#include file="../Connections/IHEA2.asp" -->
<%
Dim rsDetails__MMColParam
rsDetails__MMColParam = "1"
If (Request.QueryString("PubID") <> "") Then
  rsDetails__MMColParam = Request.QueryString("PubID")
End If
%>
<%
Dim rsDetails
Dim rsDetails_numRows

Set rsDetails = Server.CreateObject("ADODB.Recordset")
rsDetails.ActiveConnection = MM_IHEA2_STRING
rsDetails.Source = "SELECT * FROM qryDetails WHERE PubID = " + Replace(rsDetails__MMColParam, "'", "''") + ""
rsDetails.CursorType = 0
rsDetails.CursorLocation = 2
rsDetails.LockType = 1
rsDetails.Open()

rsDetails_numRows = 0
%>
<%
Dim rsResults__mmreg
rsResults__mmreg = "3000"
If (request.querystring("reg") <> "") Then
  rsResults__mmreg = request.querystring("reg")
End If
%>
<%
Dim rsResults__mmdis
rsResults__mmdis = "3000"
If (request.querystring("sub")  <> "") Then
  rsResults__mmdis = request.querystring("sub")
End If
%>
<%
Dim rsResults__mmlang
rsResults__mmlang = "3000"
If (request.querystring("lang")  <> "") Then
  rsResults__mmlang = request.querystring("lang")
End If
%>
<%
Dim rsResults__mmspecpop
rsResults__mmspecpop = "3000"
If (request.querystring("spec")  <> "") Then
  rsResults__mmspecpop = request.querystring("spec")
End If
%>
<%
Dim rsResults
Dim rsResults_numRows

Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_IHEA2_STRING
rsResults.Source = "SELECT tblBrochures.PubID, tblBrochures.Activation, tblBrochures.PublicationPending, Count(tblDisease.DiseaseName) AS CountOfDiseaseName, Max(tblDisease.DiseaseName) AS MaxOfDiseaseName, tblLanguages.LanguageName, tblLanguages.LanguageID, Max(tblTitle.TitleName) AS MaxOfTitleName, Count(TblRegion.RegID) AS CountOfRegID, Count(TblDisease.DiseaseID) AS CountOfDiseaseID, Max(TblRegion.Region) AS MaxOfRegion, tblSpecialPopulation.SpecialID, Max(tblSpecialPopulation.SpecialPopulation) AS MaxOfSpecialPopulation, tblBrochures.DownloadAvailable, tblBrochures.HardCopyAvailable  FROM ((TblRegion RIGHT JOIN (((tblTitle RIGHT JOIN (tblLanguages RIGHT JOIN tblBrochures ON tblLanguages.LanguageID = tblBrochures.LanguageID) ON tblTitle.titleID = tblBrochures.Title) LEFT JOIN tblSpecialPopulation ON tblBrochures.SpecialPopID = tblSpecialPopulation.SpecialID) LEFT JOIN tblLanguageRegion ON tblLanguages.LanguageID = tblLanguageRegion.LanguageID) ON TblRegion.RegID = tblLanguageRegion.RegionID) LEFT JOIN tblDiseaseTitle ON tblTitle.titleID = tblDiseaseTitle.TitleID) LEFT JOIN tblDisease ON tblDiseaseTitle.DiseaseID = tblDisease.DiseaseID  WHERE TblRegion.RegID=" + Replace(rsResults__mmreg, "'", "''") + " or TblDisease.DiseaseID=" + Replace(rsResults__mmdis, "'", "''") + " or tblLanguages.LanguageID=" + Replace(rsResults__mmlang, "'", "''") + " or tblSpecialPopulation.SpecialID=" + Replace(rsResults__mmspecpop, "'", "''") + "  GROUP BY tblBrochures.PubID, tblBrochures.Activation, tblBrochures.PublicationPending, tblLanguages.LanguageName, tblLanguages.LanguageID, tblSpecialPopulation.SpecialID, tblBrochures.DownloadAvailable, tblBrochures.HardCopyAvailable  ORDER BY tblLanguages.LanguageName;"
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()

rsResults_numRows = 0
%>

<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsResults_numRows = rsResults_numRows + Repeat1__numRows
%>
<%
Dim rsAllTitles
Dim rsAllTitles_numRows

Set rsAllTitles = Server.CreateObject("ADODB.Recordset")
rsAllTitles.ActiveConnection = MM_IHEA2_STRING
rsAllTitles.Source = "SELECT LanguageID, LanguageName  FROM tblLanguages  ORDER BY LanguageName"
rsAllTitles.CursorType = 0
rsAllTitles.CursorLocation = 2
rsAllTitles.LockType = 1
rsAllTitles.Open()

rsAllTitles_numRows = 0
%>

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%Dim strMemoFieldData
strMemoFieldData = Space(1200)

strMemoFieldData =(rsDetails.Fields.Item("tblBrochures.Description").Value)
%>
<%
Dim rsDetails__MMColParam
rsDetails__MMColParam = "1"
If (Request.QueryString("PubID") <> "") Then
  rsDetails__MMColParam = Request.QueryString("PubID")
End If
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>IHEA: International Health Education Alliance</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
@import url(styles_IHEA.css);

-->
</style>

<SCRIPT LANGUAGE="javascript" SRC="scripts.js"></SCRIPT>

</head>

<body id="detailspage" onload="setBottom(self.screen.availHeight)"><!-- #BeginLibraryItem "/Library/masthead.lbi" -->
<div id="masthead"> <span><a href="../Library/home.asp"><img src="../images/logo/banner1.gif" alt="IHEA Homepage" width="450" height="70" border="0"></a></span>
<div id="menu">
<ul>
<li><a href="home.asp">Home </a></li>
<li><a href="mission.asp">About Us </a></li>
<li><a href="development.asp">Our Work </a></li>
<li><a href="support.asp">Support IHEA </a></li>
<li><a href="contact.asp">Contact Us </a></li>
<li><a href="emailupdates.asp">Email Updates</a></li>
</ul>
</div></div><!-- #EndLibraryItem --><div id="container">
<div id="sidebar">

<!--#include file = "forminclude.asp" -->
<hr color="#999" width="95%">
</div>
<div id="maincontent"><br>

<% 'if IsNUll (rsDetails.Fields.Item("thumbnail").Value) then%><%'else%>
<%'end if%>
<p><span class="header">Title: </span><%=(rsDetails.Fields.Item("TitleName").Value)%></p>
<p><span class="header">Language: </span><%=(rsDetails.Fields.Item("LanguageName").Value)%></p>
<p><span class="header">Subject: </span><%=(rsDetails.Fields.Item("DiseaseName").Value)%></p>
<p><span class="header">Contents: </span>
<% if IsNull(rsDetails.Fields.Item("tblBrochures.Description").Value) then
response.write(rsDetails.Fields.Item("tblTitle.Description").Value) & "</p>"
else
response.write(rsDetails.Fields.Item("tblBrochures.Description").Value) & "</p>"
end if %>
</p>

0
 

Author Comment

by:polaatx
ID: 10753205
jjafferr, thanks for your help. I checked out the link. The other quesiton has to do with pulling the data inside Access. I am not creating a report. I am pulling data from Access using ASP vbscript and displaying it on the web. So I guess I have to mess with my SQL to get the data directly from the table. my SQL appears below. I don't know if you can help in any way figure out how to alter it so I get the value fro tblBrochures.Description directly from the table.

rsResults.Source = "SELECT tblBrochures.PubID, tblBrochures.Activation, tblBrochures.PublicationPending, Count(tblDisease.DiseaseName) AS CountOfDiseaseName, Max(tblDisease.DiseaseName) AS MaxOfDiseaseName, tblLanguages.LanguageName, tblLanguages.LanguageID, Max(tblTitle.TitleName) AS MaxOfTitleName, Count(TblRegion.RegID) AS CountOfRegID, Count(TblDisease.DiseaseID) AS CountOfDiseaseID, Max(TblRegion.Region) AS MaxOfRegion, tblSpecialPopulation.SpecialID, Max(tblSpecialPopulation.SpecialPopulation) AS MaxOfSpecialPopulation, tblBrochures.DownloadAvailable, tblBrochures.HardCopyAvailable  FROM ((TblRegion RIGHT JOIN (((tblTitle RIGHT JOIN (tblLanguages RIGHT JOIN tblBrochures ON tblLanguages.LanguageID = tblBrochures.LanguageID) ON tblTitle.titleID = tblBrochures.Title) LEFT JOIN tblSpecialPopulation ON tblBrochures.SpecialPopID = tblSpecialPopulation.SpecialID) LEFT JOIN tblLanguageRegion ON tblLanguages.LanguageID = tblLanguageRegion.LanguageID) ON TblRegion.RegID = tblLanguageRegion.RegionID) LEFT JOIN tblDiseaseTitle ON tblTitle.titleID = tblDiseaseTitle.TitleID) LEFT JOIN tblDisease ON tblDiseaseTitle.DiseaseID = tblDisease.DiseaseID  WHERE TblRegion.RegID=" + Replace(rsResults__mmreg, "'", "''") + " or TblDisease.DiseaseID=" + Replace(rsResults__mmdis, "'", "''") + " or tblLanguages.LanguageID=" + Replace(rsResults__mmlang, "'", "''") + " or tblSpecialPopulation.SpecialID=" + Replace(rsResults__mmspecpop, "'", "''") + "  GROUP BY tblBrochures.PubID, tblBrochures.Activation, tblBrochures.PublicationPending, tblLanguages.LanguageName, tblLanguages.LanguageID, tblSpecialPopulation.SpecialID, tblBrochures.DownloadAvailable, tblBrochures.HardCopyAvailable  ORDER BY tblLanguages.LanguageName;"
0
 
LVL 1

Expert Comment

by:jmcraig
ID: 10753572
Replace the code at the bottom of the section you posted that reads thus;

<p><span class="header">Contents: </span>
<% if IsNull(rsDetails.Fields.Item("tblBrochures.Description").Value) then
response.write(rsDetails.Fields.Item("tblTitle.Description").Value) & "</p>"
else
response.write(rsDetails.Fields.Item("tblBrochures.Description").Value) & "</p>"
end if %>
</p>


With code that reads so;

<p><span class="header">Contents: </span>
<% if IsNull(strMemoFieldData) then
response.write(rsDetails.Fields.Item("tblTitle.Description").Value) & "</p>"
else
response.write(strMemoFieldData) & "</p>"
end if %>
</p>


Hope fully this should set things right just as a precaution you may want to replace any occurrences of

rsDetails.Fields.Item("tblTitle.Description").Value

with

strMemoFieldData


Joshua




0
 

Author Comment

by:polaatx
ID: 10754447
Joshua, thanks for your reply.

First, I realized that the record I am interested in showing is the (rsDetails.Fields.Item("tblTitle.Description").Value),
not (rsDetails.Fields.Item("tblBrochures.Description").Value)

So I reversed what you gave me and put this on top of the page:

<%Dim strMemoFieldData
strMemoFieldData = Space(1200)
strMemoFieldData = (rsDetails.Fields.Item("tblTitle.Description").Value)
%>

And put this in the bottom:
<p><span class="header">Contents: </span>
<% if IsNull(rsDetails.Fields.Item("tblBrochures.Description").Value)  then
response.write (strMemoFieldData)  & "</p>"
else
response.write(rsDetails.Fields.Item("tblBrochures.Description").Value)  & "</p>"
end if %>
</p>

But it is still not showing more than 255 characters.

Here's the page as it appears on the web: http://www.ihea.info/pages/details.asp?pubID=41

What else could be wrong? Could the problem also be because it is grouped in a query, like jjafferr says?

I am leaving for Mexico first thing in morning. But i will check email from internet cafes and hopefully somehow try to implement any other solutions you might have. (Maybe I can use notepad to edit pages and then ftp it up using IE, I don't know) Thanks a bunch.
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 250 total points
ID: 10754669
polaatx
It is very simple to check if the problem IS from the query, just open the qery and run it, then count your characters in it.
Away to go about this is to split the memo field into many 255 character fields, this is what I did to one of our Reports which was taking the value from such a query,
In the quey, assuming your field name is Contents, place each of the following lines in a field by itself:
Contents1: mid([Contents],1,255)
Contents2: mid([Contents],256,511)
Contents3: mid([Contents],512,767)
Contents4: mid([Contents],768,1023)
Contents5: mid([Contents],1024,1279)
Contents6: mid([Contents],1280,1535)
Contents7: mid([Contents],1536,1791)
Contents8: mid([Contents],1792,2047)
Contents9: mid([Contents],2048,2303)
Contents10: mid([Contents],2304,2559)

In order for you to be safe, you have to make you contentsNN bigger than a maximum number that you can use,
If the the values do not exist for the rest, you will have NULL/Empty value which will not hurt your calculations/formula,
the last thing you should do in your ASP (not in the query, but where you take the quey results to), put the ContenetsNN together/Concatenate like this (depending on how ASP does it):
Contents=Contents1 + Contents2 + Contents3 + Contents4 + Contents5 + Contents6......
OR
Contents=Contents1 & Contents2 & Contents3 & Contents4 & Contents5 & Contents6...

So what is happening here is,
we took the maximum characters of Contents the query gives for each filed,
then put them together in ASP

I hope this helps,

jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 11118309
Hi Jack
My last comment will work.
Well, since I was new to Access, I faced this problem and solved it this way, and it is in use for more than 2 years already in our office application.
I am working on an update, so this will be tackeled differently.

thanks

jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 11118459
Thanks buddy, I already feel better.

Aren't you suppose to wear your Admin hat and say this ;o)

jaffer
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now