8

I am having issues trying to get the syntax correct for my C# 2008 asp.net code. I need to get a return value (Select @@Identity) from my stored procedure

My C# code is:

        SqlConnection conn = new SqlConnection(strConn);
        string sql = "usp_ClientProfile_Header";
        SqlCommand cmdHeader = new SqlCommand(sql, conn);

        cmdHeader.CommandType = CommandType.StoredProcedure;

        cmdHeader.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@FName"].Value = txtFName.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@LName"].Value = txtLName.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@EmailAddress", SqlDbType.VarChar, 100));
        cmdHeader.Parameters["@Email"].Value = txtEMail.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@Address1", SqlDbType.VarChar, 255));
        cmdHeader.Parameters["@Address1"].Value = txtAddress1.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@City"].Value = txtCity.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@State", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@State"].Value = txtState.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@ZipCode", SqlDbType.VarChar, 12));
        cmdHeader.Parameters["@Postal_Code"].Value = txtZip.Text.Trim();

The next line in my code needs to be the "ParameterDirection.ReturnValue" but I can't seem to get the syntax correct.

Any ideas?

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
user279521
  • 4,779
  • 21
  • 78
  • 109
  • You say "return value" which implies: RETURN (@NewID) syntax, but you say "select @@Identity" which will return a resultset, not a return value. My answer assumes you are using the RETURN syntax. Could you clarify? – AdaTheDev Feb 26 '10 at 16:05
  • the code in the stored proc is Select @@Identity Unfortunately the stored proc cannot be modified. – user279521 Feb 26 '10 at 16:06
  • Pity proc cannot be modified. Knowning that I'd just like to mention that SCOPE_IDENTITY should be used in preference to @@Identity. I know it's not applicable in this case, just spreading seeds for others that may not know http://msdn.microsoft.com/en-us/library/aa259185(SQL.80).aspx – Binary Worrier Feb 26 '10 at 16:15
  • @user279521 - Ok, makes sense now. It was confused by the fact you were talking about ParameterDirection.ReturnValue which isn't actually what you want – AdaTheDev Feb 26 '10 at 16:33

4 Answers4

19

To capture a RETURN VALUE (returned by SQL using the RETURN({number}) syntax) use:

cmdHeader.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;

Also, you should probably be using SCOPE_IDENTITY() instead of @@IDENTITY

Edit:
So your sproc would do something like:

DECLARE @NewId INTEGER
INSERT SomeTable(FieldA) VALUES ('Something')
SELECT @NewId = SCOPE_IDENTITY()
RETURN (@NewId)

And your C# code to retrieve that value would be:

int newId = cmdHeader.Parameters[@ReturnValue].value;

Edit 2:
Ok, the original question confused the issue as the "return value" is a different thing to what you're actually doing which is returning a single column resultset.

So, instead DON'T add a ReturnValue parameter at all. Just use ExecuteScalar() using your original SqlCommand setup as below:

int newId = Convert.ToInt32(cmdHeader.ExecuteScalar());
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • How do I assign the return value to a variable? – user279521 Feb 26 '10 at 16:11
  • NO!. My proc code is "Select @@Identity" This proc cannot be modified. – user279521 Feb 26 '10 at 16:23
  • @user279521 - ah that's clear now! Added "Edit 2" to my answer! – AdaTheDev Feb 26 '10 at 16:32
  • Thanks AdaTheDev. I was under the impression that only "ParameterDirection.ReturnValue" was able to get the value. I totally forgot abot ExecuteScalar. Thanks again! – user279521 Feb 26 '10 at 16:40
  • I'm in the strange position of being unable to change the stored procedure (3rd party site) or have access to 'ExecuteScalar' (it needs to be called via webservice with only some methods accessible). Is there any way I can get the return value from an ExecuteStoredProcedure? – Ian Grainger Feb 07 '13 at 11:14
4
SqlParameter parameterReturnValue = new SqlParameter("ReturnValue", SqlDbType.Int, 4);
parameterReturnValue.Direction = ParameterDirection.ReturnValue;
cmdHeader.Parameters.Add(parameterReturnValue);

//Execute your command
cmdHeader.ExecuteNonQuery();

//Get the return value
int returnvalue = (int)myCommand.Parameters["ReturnValue"].Value;
madatanic
  • 1,760
  • 3
  • 16
  • 28
  • you need to assign your ReturnValue in your stored procedure. it could be @ReturnValue or whichever you choose. you can use this statement (in my case ReturnValue): SET ReturnValue= 'Your result' – madatanic Feb 26 '10 at 16:16
0

SQL Stored Procedure:

Assume procedure returns some id/ identity as shown below

SET @ReturnValue= Scope_identity() return @ReturnValue

in C# we can

SqlParameter[] param = null; param = new SqlParameter[] { new SqlParameter("@idStudent",idStudent), new SqlParameter("","") // some empty parameters for return value }

//here we can update param with return type SqlParameter parOut = new SqlParameter("@ReturnValue", 0); parOut.Direction = ParameterDirection.Output; param[param.Length - 1] = parOut;

DataSet dsResult = con.executeSelectStoredProc("SP_NAME", param);

user1421730
  • 11
  • 1
0

The direction is set on a SqlParameter object, for your example it should be something like:

cmdHeader.Parameters["@Postal_Code"].Direction = ParameterDirection.ReturnValue;
STW
  • 44,917
  • 17
  • 105
  • 161
  • For a return value, do I have to do a cmdHeader.Parameters.Add(new SqlParameter("@Postal_Code", SqlDbType.VarChar, 12)); as well? – user279521 Feb 26 '10 at 15:56
  • Yes, the return parameter needs to be added to the `SqlCommand`'s parameter collection alongside the other parameters. – STW Feb 26 '10 at 15:58