'Text' data type returned differently between ASP & Query Analyzer

[suksawat]

I have the SQL 2000 server with a database table having one 'text' data type column. This column stores the general text message (normally body of the email message) which the thrid party application inserts in

I wanted to query this column and display on the html page using ASP

The ASP code returned the data in the column in the continued text which has no linebreaks at all, and the result format is not readble. I copied the text displayed on the returned page onto the notepad then opened with the hex editor to see the text in raw format, and found none of char13, char 10 nor any char which may identify the linebreak. It seemed to me there are no linebreaks or any identifiers to the linebreaks for the data returned from the ASP comparing to the original text

I then tried query the same column from the Query analyzer. I copied the result returned from the query analyzer into the notepad. I then opened it with the hex editor to see what it contains in raw format,and I found there were char code 20 (space char) instead. I noticed that 2 spaces char code indentify the line break in which they substitue one line break when comparing to the original text

my questions

- Why the result returned in ASP has no linebreak indentifiers like the result returned from the query analyzer ( 2 spaces char)
- What would be the way to query the data from the 'text' data type column and display correctly as the original text format

I used the common basic asp code as belo

<%@ Language="VBScript"%><
strSQL = "Select Body from main
set conn = Createobject("ADODB.Connection"
conn.open "Provider=SQLOLEDB;Data Source=Pluto;Initial Catalog=Message;Integrated Security=SSPI
set rs = conn.execute (strSQL
rs.MoveFirs
Do While Not rs.Eo
%><tr><td>Body= </td><td><%=rs("Body")%></td></tr><
rs.movenex
Loo
rs.clos
conn.clos

Many thanks in advance

[Aaron [SQL Server MVP]]

> The ASP code returned the data in the column in the continued text which

> has no linebreaks at all, and the result format is not readble.

Yes, because plain text and HTML are not identical!

Imagine if you created an HTML page and every single line break was
interpreted that way? We'd either have very ugly web pages or HTML code
that was impossible to maintain.

You need to replace plain text carriage returns with HTML tags that
represent and display carriage returns.

[url]http://www.aspfaq.com/2188[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]
(Reverse e-mail to reply.)