Accessing Stored procedure from Application

Question

I have stored procedure

CREATE PROCEDURE [dbo].[MyProcedure]
@Order_ID               int,
@ReturnMessage  varchar(250) OUTPUT
AS

If exists(select Fname from table where condition)
Begin
Insert into Table1(Fname)
               values(
Select @ReturnMessage ="It exists and Fname inserted"
return @ReturnMessage
End
Select @ReturnMessage ="It does not exists"
return @ReturnMessage
GO

I want to call this Procedure from C#Application & return the valueof ReturnMessage in my application

SqlParameter paramOrderId = new SqlParameter("@Order_ID", SqlDbType.Int);

paramOrderId.Value = Order_ID;

SqlParameter paramRetMsg = new SqlParameter("@ReturnMessage", SqlDbType.Int );

paramRetMsg.Direction = ParameterDirection.Output;

ReturnMessage = Convert.ToInt32(SqlHelper.ExecuteScalar(ds._conStringCCR, CommandType.StoredProcedure, "MyProcedure", paramOrderId, paramRetMsg));

and I am not able to return the value to the application .

Please let me know whats wrong.

pujain · Answer

Arvind, Thanks for the reply, But when I use t , instead of the value of return message it has '"System.Data.SqlClient.SqlDataReader" in the retrun message. let me know

paresh jagatiya · Answer

2 ways, 1st : no need to have return @ReturnMessage just use Select @ReturnMessage ="It exists and Fname inserted" and try following.. ReturnMsg = SqlHelper.ExecuteScaler(......).ToString(); (As Executescaler gives you Object type) 2nd : Returnmessage = paramRetMsg.value; (before you access the value of output param the connection need to be closed).

arvind haritus · Answer

hi please try this, may be help you ReturnMessage =(SqlHelper.ExecuteReader(ds._conStringCCR, CommandType.StoredProcedure, "MyProcedure", paramOrderId, paramRetMsg)).ToString();

pujain · Answer

Now i am trying this .. But it gives me error : Cannot implicitly convert type 'System.Data.SqlClient.SqlDataReader' to 'string' public static string GetMsg( int Order_ID) { DataAccess ds = new DataAccess(); String ReturnMessage = string .Empty; try { SqlParameter paramOrderId = new SqlParameter("@Order_ID", SqlDbType.Int); paramOrderId.Value = Order_ID; SqlParameter paramRetMsg = new SqlParameter("@ReturnMessage", SqlDbType.VarChar,250); paramRetMsg.Direction = ParameterDirection.Output; ReturnMessage =(SqlHelper.ExecuteReader(ds._conStringCCR, CommandType.StoredProcedure, "MyProcedure", paramOrderId, paramRetMsg)); } } Please let me know .