Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 C# ASP.NET , +
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 OriginalGriff 620
1 Maciej Los 610
2 CPallini 255
3 RyanDev 161
4 Peter Leow 125
0 OriginalGriff 5,784
1 Sergey Alexandrovich Kryukov 4,838
2 Peter Leow 3,009
3 Maciej Los 2,894
4 DamithSL 2,465


Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 12 Nov 2012
Copyright © CodeProject, 1999-2014
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