Solved

Can't get more than 255 chars from Access DB

Posted on 2000-05-12
17
250 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 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

18 Experts available now in Live!

Get 1:1 Help Now