Solved

Can't get more than 255 chars from Access DB

Posted on 2000-05-12
17
286 Views
Last Modified: 2012-06-21
Is there a way to get more than 255 chars from an Access DB in asp?
When I had the field as memo it returned nothing. I changed it to text(255) and that works but I need a LOT more than that.
Do I have to go to SQL Server, and if so, what field type?

Thanks
0
Comment
Question by:SpikeDog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +6
17 Comments
 

Expert Comment

by:pramesh
ID: 2805784
Yes Use GetChunk method to retrieve data from a memo field.
0
 
LVL 2

Expert Comment

by:MoMarvi
ID: 2805846
Post your code, I get severasl Memo fields to work in an Access DB without having to use the GetChunk.
0
 

Author Comment

by:SpikeDog
ID: 2805858
I open my recordset through ADO and a DSN.
Then I say :
profiletext = rs("profiletext")

ProfileText seems to be empty even though there are hundreds of chars in the field.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:mberumen
ID: 2806151
SpikeDog,  I regularly read and write memo fields from my Access tables to ASP and haven't  encountered the problem you are describing....

why don't you try a simple asp page that reads only the memo field in the ADO recordset and display them....  use the response object to check that the data is there...

0
 
LVL 1

Expert Comment

by:cable4096
ID: 2806227
It depends on the cursor type you are using. What does your open and recordset commands look like? Can you post them here?
0
 

Expert Comment

by:simtetchun
ID: 2806761
What version of the software (i.e. IIS, ODBC and etc) you are using on the server?
I did encounter this problem before but I found out that it's caused by the older software version.
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 2808103
I work with databases all the time, and especially with memo fields. MDB-database files shouldn't be hard at all with memo fields (unlike SQL-server).
Here goes

1) are you sure you are SELECTing the memo field from the database.

2) try another statement which only select the memo field, and write it out

SQL = "select memofield from table"
set rs = Connection.Execute(SQL)
response.write rs("memofield")

3) try to be case sensitive, maybe that's what is going wrong (shoul;dn't be it though).

4) when using a memo field, and looking at the page, and when you see no error message or whatsoever. Just use the "view source" button....look through the code and see if there is somewhere an error message.

Good luck,
CJ
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 2808106
btw

GetChunck isn't necessary at all.
0
 

Author Comment

by:SpikeDog
ID: 2808367
Thanks for all the comments.
I now know why it wasn't working.
However, it has raised a bigger issue and I'm offering my entire collection of 85 points to anyone that can tell me why - because I'd like to know.
Here's the answer:
To ensure that the value I get back from the rs is not a null I do a fairly obvious thing. I say
if isnull(rs("whatever")) then
    strwhatever = ""
else
    strwhatever = rs("whatever")
end if

This should work right?
It always has before.
I tested this under VB so I could watch the vars and I found that as soon as I exeuted the 'if isnull' line the value of rs("whatever") went from my load of chars to null. I watched it change via the intellisense popup.
Why would testing the value make it equal to null?
I got around this by saying:
strwhatever = rs("whatever") & ""
which is what I usually do when I remember.
Any ideas?
First person to explain the null thingy gets my points and my undying gratitude.

Client uses ADO 2.1, server is win2k unpatched.
0
 

Author Comment

by:SpikeDog
ID: 2808368
Adjusted points from 30 to 85
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 2808508
Just one thing about SQL.

With SQL you always have to select the memo-field(s) after the other (normal) fields.....also, when trying to show them you cannot show them directly (well, you can, but you can't re-use them), you always have to put them into a variable first, and then use 'em.
This is also what i was implying when I said "unlike SQL".

Seems the same was happening with you.
0
 

Expert Comment

by:sasidhargn
ID: 2809263
Try to check what is the version of MDAC. It should be MDAC ver 2.0. If it is earlier version then u can not retrieve more than 255 char. Try to install the MDAC ver 2.0 and check it out. It should work.
0
 

Author Comment

by:SpikeDog
ID: 2809307
sasidhargn, this is definitely not the issue. As I mentioned in my last comment the whole thing works OK when I don't check for a null first. Answer that and you can have the points.
0
 

Expert Comment

by:sasidhargn
ID: 2809406
SpikeDog, Sorry i may misunderstand about ur question. Once i faced the same problem using ASP, that i can not get more than 255 char. that time problem is with MDAC. U can try another also. while querying itself check for null and trim it.u can write the query like below.

select trim(isnull(field,"")) from table

Some times in memo fields if u dont trim also it created problems. Just try it. If i misunderstood the question then its ok..leave it....
0
 

Author Comment

by:SpikeDog
ID: 2809503
No worries sasi
It is definitely a weird thing.
I just can't test for a null value without the value becoming null.
0
 
LVL 11

Accepted Solution

by:
mouatts earned 85 total points
ID: 2809600
I'm believe that the problem is that memo fields don't support the concept of null when passed via ODBC. The reasoning for this to some extent is that under Oracle the equivilent field type doesn't allow testing for null at all. Consequently it is differcult to see how an ODBC driver could test for it either. If one database does it then the chances are other do as well.

So the solution that I have always used is to move the item from the record set before testing and then testing against vbNullString rather than isnull eg

x=rs("mymemo_field")
if x<>vbNullString then ....

Incidently the comment about selecting memo fields as the last item is only true with SqlServer where the memo field must be the last field defined in the table).

HTH
Steve
0
 

Author Comment

by:SpikeDog
ID: 2809826
Good enough for me ...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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