Click here to Skip to main content
12,699,492 members (32,754 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL-server-2005 C# ASP.NET , +
I have some sql statements in my stored procedure which concatenates strings with carriage return wherever needed.I execute this stored procedure from C#,the procedure executes perfectly and the output is returned to a label.But the carriage return which I have added in my sql statements doesnt affects to C# labels.Why is this so?What is the alternate for this?
declare @data varchar(500)
SET @data='HELLO,'+char(13)+char(10)+'Member'
print @data
--select @data

The above code is inside a stored procedure.I executed the stored procedure from SQL SERVER MANAGEMENT STUDIO and I get my desired output.The statement 'HELLO,'+char(13)+char(10)+'Member' will first print 'HELLO', and then the cursor comes to next line because of char(13)+char(10) and the it print 'Member'.
Below is the output:

But this is only possible if I use print command,but if I use print command,how can I output the value to C#?.
And I use select command then I can output the value to C# but the carriage return doesn't work.

Please help.
Posted 11-Nov-12 23:11pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Handle "InfoMessage" event of SqlConnection object. It will fires when something you write print command in sql statement. For Example
SqlConnection con = new SqlConnection(conString);
            con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessage);
            SqlCommand cmd = new SqlCommand("your_sp"), con);           


Handle event as
void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
          //sb is your global StringBuilder variable
           sb.Append("\n" + e.Message);
skokeh 12-Nov-12 4:41am

i really like your idea, but i wanted to ask you is this only work if you use "print"
in your SQL query, or what?
Sanjay K. Gupta 12-Nov-12 4:59am
InfoMessage event only fires when a client message is sent by SQL Server during/after sql query execution.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

if possible useSET @data='HELLO,'+ '\n' +'Member' in stored procedure. It should work in .net
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

Dear Mathew,

You put Spaces in your Sql "Code +char(13)+char(10)+" it's can't be new Line in .net

in .net you have to mention the New line like this "\n" example "Hello" + "\n"
you Can put a word in your Stored Procedure, like this

SET @data='HELLO,'+'\n'+'\n'+'Member'

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170118.1 | Last Updated 12 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100