SQL Server stored procedure return value, and using it in C#

Question

My application allows the user to add a new player to the database. Before adding a new player, I'd like for a stored procedure to check whether or not a player with the first name and last name exists. I want the stored procedure to return a bit value, 0 or 1. My C# method will then return this value and the program can decide whether or not to proceed with the creation.

Note that I've cut some of the general validation out of the code below, i.e. if the fields are empty, or the balance TextBox is invalid etc..

I'm also aware that I may be using the wrong datatype when handling the returned value. i.e. int instead of bool.

When I run this, I get an error saying that my SP requires an input parameter @ReturnedValue.

Cheers

 Player newPlayer = new Player();
 newPlayer.PlayerID = Guid.NewGuid();
 newPlayer.FirstName = TextBoxFirstName.Text;
 newPlayer.LastName = TextBoxLastName.Text;
 newPlayer.Balance = Convert.ToDouble(TextBoxInitialCredit.Text);

var exists = newPlayer.CheckExists();

if (exists == 1)
{
     newPlayer.AddPlayer();
}

and here's the method:

 public int CheckExists()
 {
     SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Badminton"].ConnectionString);

     myConnection.Open();

     SqlCommand SqlCmd = new SqlCommand("CheckPlayerExists", myConnection);
     SqlCmd.CommandType = CommandType.StoredProcedure;

     SqlCmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = FirstName;
     SqlCmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = LastName;
     SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 2).Direction = ParameterDirection.Output;

     SqlDataAdapter myDataAdapter = new SqlDataAdapter(SqlCmd);
     DataSet myDataSet = new DataSet();
     myDataAdapter.Fill(myDataSet);

     int exists = Convert.ToInt32(SqlCmd.Parameters["@ReturnValue"].Value);

     myConnection.Close();

     return exists;
 }

and now my stored procedure:

CREATE PROCEDURE dbo.CheckPlayerExists
   @firstName nvarchar(50),
   @lastName nvarchar(50),
   @ReturnValue bit output
AS 
BEGIN
IF EXISTS (SELECT * FROM Players WHERE FirstName = @firstName AND LastName = @lastName)
        SET @ReturnValue = 1
ELSE
        SET @ReturnValue = 0

RETURN @ReturnValue
END

you should use FirstName = @firstName OR LastName = @lastName — Sudhakar Tillapudi
– Sudhakar Tillapudi, Commented Feb 27, 2014 at 15:45
You don't need a table, set or adapter as there are no rows in the output, ExecuteNonQuery would be simplest, see stackoverflow.com/questions/5630002/… — Alex K.
– Alex K., Commented Feb 27, 2014 at 15:48

RBarryYoung · Accepted Answer · 2014-02-27 16:24:59Z

3

First, there's some inconsistency in your posted information. Is the parameter @ReturnValue or @ReturnedValue? That alone could be the problem.

Second, either change your stored procedures declaration of the @ReturnedValue parameter to

@ReturnValue bit = 0 output

or change the C# code that adds the output parameter:

SqlParameter p = new SqlParameter();
p.ParameterName = "@ReturnedValue";
p.SqlDbType = SqlDbType.Int;
p.Value = 0;
p.Direction = ParameterDirection.InputOutput;
SqlCmd.Parameters.Add(p);

edited Feb 27, 2014 at 16:24

answered Feb 27, 2014 at 16:14

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MJay Over a year ago

Thanks RBarry. I had only changed it from atReturnValue to atReturnedValue following an earlier answer. My original code was fine. The problem was your second suggestion. I needed to add '= - output' to the parameter declaration within the SP. Thanks

Sudhakar Tillapudi · Accepted Answer · 2014-02-27 15:51:21Z

0

Problem : You are assigning the ParameterDirection Enum value as Output.

Solution : Change your ParameterDirection Enum value from Output to ReturnValue

ReturnValue: ParameterDirection.ReturnValue

The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.

Replace This:

SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 2).Direction
   = ParameterDirection.Output;

With This:

SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 2).Direction
   = ParameterDirection.ReturnValue;

answered Feb 27, 2014 at 15:51

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

2 Comments

MJay Over a year ago

Thanks, i've changed that as well as the SP to @ReturnedValue, however I receive this error: Procedure or function 'CheckPlayerExists' expects parameter '@ReturnedValue', which was not supplied

Sudhakar Tillapudi Over a year ago

@ManxJason: did you change the direction parameter?

LB2 · Accepted Answer · 2014-02-27 15:52:11Z

0

I think I see your issues. In the line:

SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 2).Direction = ParameterDirection.Output;

change name and direction type to:

SqlCmd.Parameters.Add("@ReturnedValue", SqlDbType.Int, 2).Direction = ParameterDirection.ReturnValue;

answered Feb 27, 2014 at 15:52

LB2

4,86023 silver badges37 bronze badges

2 Comments

MJay Over a year ago

Thanks, i've changed that as well as the SP to @ReturnedValue, however I receive this error: Procedure or function 'CheckPlayerExists' expects parameter '@ReturnedValue', which was not supplied.

LB2 Over a year ago

Did you change the ParameterDirection from .Output to .ReturnValue?

Collectives™ on Stack Overflow

SQL Server stored procedure return value, and using it in C#

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related