how to get break to next line inside the select statement.

Question

0.00/5 (No votes)

See more:

XML

i saved already thousands of data into database. we can not update with \r\n\r\n.

but in database it shows correctly. as i typed in textbox with multiline using enter. the html only did not accept. so, i used like below.

N'<table cellpadding="0" cellspacing="0" width="780" bgcolor="#ffffff">'+
N'<tr>'+
N'<td>'+
N'<p style="color:#777777;text-decoration:none;font-size:12pt;font-family:Calibri; ">'+
cast((SELECT ISNULL(replace(cast(code.memo as nvarchar(max)),CHAR(13), '<br/>') ,'')FROM orders LEFT OUTER JOIN code ON code.memo_id = orders .memo_id WHERE rtno=@rtno FOR XML PATH(''),TYPE) AS NVARCHAR(MAX)) +'

NOTE : here memo column is ntext datatype where not able to replace. so, i converted into nvarchar then done replace.


</P>'+
N'</td>'+
N'</tr>'+
N'</table>'+

when i check in database using this below query to check html format.

SELECT TOP 1 *FROM [msdb].[dbo].[sysmail_sentitems] order by sent_date desc

**NO VALUE FOR GOOD KNOWLEDGE IN GOVT FIELD BUT IT IS VALUE FOR IT FIELD.&lt;br/&gt;  // can you see here wrong html format as i have give for xml path..

** ALL VALUES & TEST GOOD, NO PAIN NO GAIN AS WELL AS IT IS EQUAL TO THIS MEANING LIKE NO BRAIN NO.&lt;br/&gt;  //invalid html format.

so i removed for xml path from select query... and executed email stored procedure. then i checked body content using SELECT TOP 1 *FROM [msdb].[dbo].[sysmail_sentitems] order by sent_date desc

Now, i got correct html format like below

**All Tools NOT REPAIRED will be charged a Check Fee of $10.00 to $1000.00 (depending on tool).<br/>

**All Tools NOT REPAIRED will be returned DISASSEMBLED.<br/>

But, when i see into gamil account or outlook after sent mail (execued email stored procedure), I am getting output with <br/> tag like below

i) when execute without for xml path stuff,

**All Tools NOT REPAIRED will be charged a Check Fee of $10.00 to $1000.00 (depending on tool).<br/>**All Tools NOT REPAIRED will

be returned DISASSEMBLED.<br/>

ii) when i execute with for xml path stuff

**All Tools NOT REPAIRED will be charged a Check Fee of $10.00 to $1000.00 (depending on tool).&lt;br/&gt;**All Tools NOT REPAIRED will

be returned DISASSEMBLED.&lt;br/&gt;

kindly help me. how to solve this problem?

Posted 11-Mar-14 1:39am

christhuxavier

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sohail awr · Answer 1 · 2014-03-11T03:09:00

I have checked your data and
is correct to break a line in select statement. But if the problem is again occurring the next idea is below. check it.

VB

Public Class WebForm1
    Inherits System.Web.UI.Page
    Public oCn As New System.Data.SqlClient.SqlConnection("Data Source=(local);Initial Catalog=MyDatabase;Uid=sa")

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        fill_data()
    End Sub

    Sub fill_data()
        If oCn.State = ConnectionState.Closed Then
            oCn.Open()
        End If
        Dim str As String = ""
        Dim cmd As New SqlClient.SqlCommand("select * from Table_2", oCn)
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet("bpl")
        Dim i As Integer = 0

        da.Fill(ds, "bpl")
        str = ds.Tables(0).Rows(0).Item(0).ToString.Replace("//", "<br /><br />")
        Me.Label1.Text = str
        oCn.Close()
    End Sub
End Class