How to keep the text from a textarea in the same format inside SQL Database?

Posted on 2005-04-14
Last Modified: 2012-06-21
Ok,.. this question kinda spans over a couple areas, but since its mainly dealing with SQL, I put it here.

I have an ASP page with a form on it.  On the form is a large textarea for comments.  

On Submit, the textarea, along with the rest of the form fields goes to a ASP handler, which loads it all into a SQL table.

After that, I pull this information down onto another page.  When I pull it down, all the text from the textarea is run together without any formatting.

Is there anyway to send the text from the textara to the SQL Database and back down to the other page, while still retaining its spacing, tabs, and other formatting features?

Question by:DrinkGreen
    LVL 7

    Expert Comment

    You need to translate characters like carriage return (Chr(13)), double spaces, etc into their HTML equivalents.

    Double spaces dispaly only as single space (html compacts it), so Replace(text, "  ", "  ")
    CR  -- Replace(textarea, Chr(13), "<br")

    this should get you started.

    LVL 7

    Expert Comment

    Here's a routine I use:

    DBField = HTMLEscape(texarea)

      Private Function HTMLEscape(ByVal xField As String) As String
        'Replace these characters with their HTML encoding: &, ', "
        xField = Replace(xField, "&", "&amp;")
        xField = Replace(xField, "'", "&#39;")
        xField = Replace(xField, """", "&quot;")

        'Get these Extended characters
        xField = Replace(xField, Chr(145), "&#39;") 'left single quotation mark to regular single quote
        xField = Replace(xField, Chr(146), "&#39;") 'right single quotation mark to regulare single quote
        xField = Replace(xField, Chr(147), "&quot;") 'left double quotation mark to regular double quote
        xField = Replace(xField, Chr(148), "&quot;") 'right double quotation mark to regular double quote
        xField = Replace(xField, Chr(150), "&ndash;") 'dash the length of 'n' character
        xField = Replace(xField, Chr(151), "&mdash;") 'dash the length of 'm' character
        xField = Replace(xField, Chr(188), "&frac14;") ' 1/4
        xField = Replace(xField, Chr(189), "&frac12;") '1/2
        xField = Replace(xField, Chr(190), "&frac34;") '3/4

        'Return Escaped field
        Return xField
      End Function
    LVL 49

    Accepted Solution

    Normal display of text on an HTML page exhibits the symptoms you describe.  That is the way web pages are supposed to work.

    One possible option is to wrap the data in
          <PRE> ...your data here... </PRE>
    tags.  That will make the text be displayed in a fixed-width font and it will preserve line-ends and so-forth... in breifl, it will probably look more like the original text.

    -- Dan

    Author Comment

    So do I put the <PRE> </PRE> tags around the textarea, or inside it.... or do I put it on the page that pulls the text out of the database?
    LVL 49

    Expert Comment

    Use it on the page that displays the output.

    Odds are, the data in the database is formatted as desired, but it will depend upon what it includes.  If the Textarea contains <table> tages and other major formatting, then the issue will be more complex.  To verify/diagnose that, use Query Analyzer or other tool to examine the data in situ in the database.

    Author Comment

    No, I don't have any <table> tags in it.

    That worked great!  Thanks for the tips DR.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    754 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

    20 Experts available now in Live!

    Get 1:1 Help Now