SQL retrieving OUTPUT into a asp.net form

Question

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

Jon it gives me Procedure or function usps_InsertIntoMedia has too many arguments specified. — jpavlov
– jpavlov, Commented Sep 24, 2012 at 18:58
should I loop them to be injected if the parameter is input and write to lbl if output? — jpavlov
– jpavlov, Commented Sep 24, 2012 at 18:59
You have to specify it as an output parameter, you are passing too many arguments. — JonH
– JonH, Commented Sep 24, 2012 at 19:50
Mark this as your answer, I just went through my query and got it. Thank. — jpavlov
– jpavlov, Commented Sep 24, 2012 at 19:52

MazBros · Accepted Answer · 2012-09-24 18:36:35Z

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"];

answered Sep 24, 2012 at 18:36

MazBros

967 bronze badges

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

Comments

jpavlov · Accepted Answer · 2012-09-26 12:40:47Z

0

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

answered Sep 26, 2012 at 12:40

jpavlov

2,26310 gold badges43 silver badges60 bronze badges

Collectives™ on Stack Overflow

SQL retrieving OUTPUT into a asp.net form

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related