We are building a website in standard ASP for a client and have hit a
snag.  At their request, we are using SQL Server 2005 Express (free
edition) and have found strange things with large text fields. 
Typically we have a number of fields where the content is HTML code
and some go over the 8000 char limit of a standard varchar field.  If
we use a 'text' type field we can display it fine but if we try to put
too much (which is not really all that much)into it when saving from a
form then it throws an error warning of possible truncation and will
not continue, so this is no good.  If we use the new varchar(MAX)
field, we can do everything EXCEPT display it.  We are displaying with
a standard <%=rs("newsletter_text")%> (for example) just like any
other field.  Have tried assigning to a variable first.  Nothing
works.  We have this problem on all three of the servers (all Win
2003) which we have tried.  We can find nothing to help on the web. 
Our deadline is upon us and we can't get round this tiny little
problem.  Anyone out there know the answer for us?  Is there a setting
we have to change?  Is there a trick we have to learn?  Help!!

Hi

Try to reduce the No of charactes length to 100 into that field and
check it out is it working or not.

bala

Thanks bala - sorry for delay - I expected an email to tell me of
activity!  I'm new to this.

I have tried this and it still doesn't display.  Very odd

PCWebman

does it actually store correctly in the database? I mean if you just
look at it with a sql query, does it come back correctly?

I am really bad at keeping up with this because it doesn't email me

frankcorrao-ga - if you are still out there.  
Yes, it comes back correctly if I run a query in the Database
Management Tool, whether the field is 'text' or 'varchar(MAX)' - just
not on the web when I try to Response.Write it.

Any more ideas?  Anyone?

I have tried the same setup: pulling data from a varchar(max) in a SQL
Server database into an ASP page. The result is no data. If you test
for whether the field was empty, test comes back negative (not empty).
If I change the varchar(max) to a varchar(8000) it works fine.

After some testing I found there are no SQL Server 2005 data types
that can be read by an ASP page that hold more than 8000 characters. I
speculate that the 2-byte field length indicator is not read by ASP.
Maybe the field length of the old Access memo data type was encoded
differently.

You can't fix the problem by casting to string with CStr. You can't
fix it by taking the first n characters of the field with a Mid
function. The data simply is not read into your
rs.Fields.Item("longdata").

I think it's a backward-compatibility dead end.

That leaves work-arounds. An Iframe containing a .NET page that pulls
up your content properly might work if you know ahead of time the size
of the Iframe.

Maybe you could redirect to a .NET page that grabs your data and sends
it back as form data. Simply including a .NET page in the ASP file
won't work because each type is processed differently. You cannot send
Session variables between ASP and .NET pages either.

In the end, it's probably easier to convert your ASP pages to .NET and
avoid the whole problem.

Good luck and please email me if you find a workaround.

Try making the varchar(max) field the last field in the select
statement.  It's a stupid fix, but I've seen it fix this problem with
text fields.

Instead of select id, textfield, somethingelse
change it to
select id, somethingelse, textfield