Capturing Return Value of stored procedure

Question

im trying to implement Custom Role provider for my .Net application. Im trying to check if the role added by user already exists in the db or not by using the Asp.NET's role provider stored procedure like

SqlConnection sqlConnection = new SqlConnection(conn);
SqlCommand sqlCommand = new SqlCommand("aspnet_Roles_RoleExists", sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@RoleName", SqlDbType.VarChar, 256).Value = "/";
sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 256).Value = roleName;
sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int,4).Direction = ParameterDirection.ReturnValue;
try
{
  sqlConnection.Open();
  sqlCommand.ExecuteNonQuery();
  int x = (int)sqlCommand.Parameters["@ReturnValue"].Value;
  if ((int)sqlCommand.Parameters["@ReturnValue"].Value == 0)
                 ...

the problem is that when i add the role e.g admin and run the sp manually from the database like

DECLARE @return_value int

EXEC    @return_value = [dbo].[aspnet_Roles_RoleExists]
        @ApplicationName = N'/',
        @RoleName = N'admin'

SELECT  'Return Value' = @return_value

it gives me the correct result but from the application if i try to add the role admin again the statement

(int)sqlCommand.Parameters["@ReturnValue"].Value

always give me 0. Can anybody guide me to what is causing this behavior

duplicate of http://stackoverflow.com/questions/5412256/return-value-using-sqlcommand — Sebastian Meine, Jan 10 '13 at 14:40
@SebastianMeine but im not using any class of the sort as `SafeDataReader` — John x, Jan 10 '13 at 14:42

score 1 · Accepted Answer · answered Jan 10 '13 at 14:46

That's more likely because you inverted the values expected by the stored procedure:

sqlCommand.Parameters.Add("@RoleName", SqlDbType.VarChar, 256).Value = "/";
sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 256).Value = roleName;

And it should be:

sqlCommand.Parameters.Add("@RoleName", SqlDbType.VarChar, 256).Value = roleName;
sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 256).Value = "/";

You were passing roleName as parameter for the ApplicationName.

i dont have words to express my gratitude ... god bless you @lcarus — John x, Jan 10 '13 at 14:49

Capturing Return Value of stored procedure

1 Answers1