0

I have a SQL stored procedure that OUTPUTs the number to a row after I insert of few records. The query works great and returns what I need, although. I would like to take the output from the query and read it into a label. Can someone shine a little light? Thanks.

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@spName", txt_Name.Text));
            cmd.Parameters.Add(new SqlParameter("@spSource", txt_Source.Text));
            cmd.Parameters.Add(new SqlParameter("@spDateCreated", txt_DateCreated.Text));

            paramReturnNumber = cmd.Parameters.AddWithValue("@out", SqlDbType.NVarChar);
            paramReturnNumber.Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();

The Query:

ALTER PROCEDURE [dbo].[usps_InsertIntoMedia]
@spName varchar(100),
@spSource varchar(100),
@spDateCreated datetime
AS
SET NOCOUNT ON;  
DECLARE @out nvarchar
BEGIN
INSERT INTO Media (Name, Source, DateCreated)
OUTPUT inserted.Number 
VALUES(@spName,@spSource,@spDateCreated )   

Select @out = Media.Number
FROM Media  
END
Sean Vieira
  • 155,703
  • 32
  • 311
  • 293
jpavlov
  • 2,215
  • 9
  • 43
  • 58
  • Jeff try this: http://stackoverflow.com/questions/3433694/how-to-run-the-stored-procedure-that-has-output-parameter-from-c – JonH Sep 24 '12 at 18:36
  • Jon it gives me Procedure or function usps_InsertIntoMedia has too many arguments specified. – jpavlov Sep 24 '12 at 18:58
  • should I loop them to be injected if the parameter is input and write to lbl if output? – jpavlov Sep 24 '12 at 18:59
  • You have to specify it as an output parameter, you are passing too many arguments. – JonH Sep 24 '12 at 19:50
  • Mark this as your answer, I just went through my query and got it. Thank. – jpavlov Sep 24 '12 at 19:52

2 Answers2

0

I am using the following technique:

    Hashtable ht = new Hashtable();
    foreach (SqlParameter sqlp in cmd.Parameters)
    {
         if ((sqlp.Direction == ParameterDirection.InputOutput) || 
             (sqlp.Direction == ParameterDirection.Output))
         {
             ht.Add(sqlp.ParameterName, sqlp.Value);
         }
    }
    <lblName>.Text = (string)ht["@out"];
MazBros
  • 96
  • 7
0

The solution to this problem was to specify the OUTPUT parameter using the SET statment.

jpavlov
  • 2,215
  • 9
  • 43
  • 58