Accessing Stored procedure from Application

pujain

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

pujain20y ago
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 jagatiya20y ago
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 haritus20y ago
hi
please try this, may be help you
ReturnMessage =(SqlHelper.ExecuteReader(ds._conStringCCR, CommandType.StoredProcedure, "MyProcedure", paramOrderId, paramRetMsg)).ToString();
pujain20y ago

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 .