Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
HI!!
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?
eg:-
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:
HELLO,
Member
 
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 22:11pm
Rate this: bad
good
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);           
 
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
----------------------------------
Handle event as
void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
       {
          //sb is your global StringBuilder variable
           sb.Append("\n" + e.Message);
       }
  Permalink  
Comments
skokeh at 12-Nov-12 4:41am
   
Sanjay 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 at 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
good
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
  Permalink  
v2
Rate this: bad
good
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'
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 716
1 OriginalGriff 442
2 Tadit Dash 400
3 sanket saxena 337
4 Peter Leow 228
0 Sergey Alexandrovich Kryukov 11,890
1 OriginalGriff 7,335
2 Peter Leow 5,003
3 Abhinav S 3,943
4 Maciej Los 3,575


Advertise | Privacy | Mobile
Web03 | 2.8.140421.2 | Last Updated 12 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid