Data types and export to Excel from ASP page

Discussion in 'ASP General' started by Bryan Russell, Jul 23, 2004.

  1. 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!
     
    Bryan Russell, Jul 23, 2004
    #1
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.