Data types and export to Excel from ASP page

[Bryan Russell]

Hi,

My asp/sql server application gets an error, but only when you use the
Excel export option:
<% Response.ContentType="application/vnd.ms-excel" %> (very popular w/
users)


I think somebody entered a string of characters that Excel does not
like

So I believe (but not sure) some character(s) in a Ntext sql server
field "desc_text" is confusing the Excel file and causing it to error
(and crashes Excel)

So far, I have attempted three fixes with no success

1) Tried to convert the data type in the SQL query:
CAST(sr.desc_text AS VARCHAR(255))


2) Tried to convert the data type in the VBscript:
<% desc_text1 = Cstr(rs.fields.item("desc_text").value)%>


3) Tried append an apostrophe to the beginning of the field so that
Excel would not read it as a formula. (this might have worked, but
Excel does not treat an apostrophe the same from an export as it does
if you typed it in directly)
<%response.write "'"%> <% response.write desc_text1%>

Soliciting all ideas!

Also, if you have any ideas on how to isolate and identify the
specific record and character(s) that is causing the error, that would
be great. I have it narrowed down to a record set of 600 records, and
the error occurs if I include beyond the 130th character position. The
problem does not occur if I use CAST(sr.desc_text AS VARCHAR(129))


thanks!