Why output parameter return 0 at c# level?

Question

I am trying to get output from store procedure but in C# it gives me 0.

 public short SelectOccupantTypesByOccupantTypeID(Int64 OccupantID)
            {

                SqlCommand SqlCom = new SqlCommand("SelectOccupantTypesByOccupantTypeID", DatabaseConnection.OpenConnection());
                SqlCom.CommandType = CommandType.StoredProcedure;
                SqlCom.Parameters.AddWithValue("@pk_Occupants_OccupantID", OccupantID);
                SqlParameter sqlParamOccupantTypeID = new SqlParameter("@OccupantTypeID", SqlDbType.SmallInt);
                sqlParamOccupantTypeID.Direction = ParameterDirection.Output;
                SqlCom.Parameters.Add(sqlParamOccupantTypeID);
                short OccupantTypeID = Convert.ToInt16(sqlParamOccupantTypeID.Value);
                SqlCom.ExecuteNonQuery();
                DatabaseConnection.CloseConnection();

                return OccupantTypeID;

            }

trying to get it

protected void ddlOccupants_SelectedIndexChanged(object sender, EventArgs e)
    {
        Int64 OccupantID= Convert.ToInt64(ddlOccupants.SelectedValue);
        Int16 OccupantTypeID= MngOccupants.SelectOccupantTypesByOccupantTypeID(OccupantID);
        txtBoxMonthlyInstallment.Text = OccupantTypeID.ToString();

    }

SP:

alter PROCEDURE [dbo].[SelectOccupantTypesByOccupantTypeID] 

    @pk_Occupants_OccupantID bigint,
    @OccupantTypeID smallint output

AS
BEGIN

         Set @OccupantTypeID= (Select Occupants.OccupantsOccupantTypeID 
         From Occupants
         Where Occupants.pk_Occupants_OccupantID= @pk_Occupants_OccupantID)

         return @OccupantTypeID

END

but sp return correct value at sql level but returns 0 at C# level. Why ?

You need to call ExecuteNonQuery before you read the value... — petelids
– petelids, Commented Oct 6, 2015 at 20:00
Double check you parameter being passed and you fetching data logic. If DBMS say it returns zero it returns zero. To be sure use the profile tool. — jean
– jean, Commented Oct 6, 2015 at 20:00
@jean that isn't necessarily correct. If the integer has not has its value overwritten by a db call, it'll return the default value, which for an integer types is 0, meaning that petelids comment IS the answer. — David L
– David L, Commented Oct 6, 2015 at 20:02
I've been reading C# too long now... I want to smack you for the capitalized local variables :) — Matthew Whited
– Matthew Whited, Commented Oct 6, 2015 at 20:05
@DavidL As I said, check fething logic (and it appears to be the problem here). What I said was: DBMS don't changes behaviour dependant of who called it unless you create some sort of rule about it. The data you got in test is the same you got in app if you pass the exact same parameters. — jean
– jean, Commented Oct 6, 2015 at 20:07

Scott Chamberlain · Accepted Answer · 2015-10-06 20:03:34Z

2

You are reading the value of the parameter before you execute the query, swap the order.

SqlCom.ExecuteNonQuery();
short OccupantTypeID = Convert.ToInt16(sqlParamOccupantTypeID.Value);

However you are commiting other "bad practices", your connections really should be wrapped inside using statements and not reused throughout your program. ADO.NET already implements Connection Pooling, you don't need to do it yourself, just make short lived connections as needed and when you dispose of them they will be returned to the pool.

answered Oct 6, 2015 at 20:03

Scott Chamberlain

128k37 gold badges299 silver badges447 bronze badges

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

2 Comments

Covert Over a year ago

thanks but this practice never created any problem, since my softwares are running at enterprise level since many years without any problem

Scott Chamberlain Over a year ago

Then you have been fortunate enough to not ever have a program that constantly threw a exception between your Open and Close calls that caused connections to be opened but never closed exhausting all available connections to the Sql Server. At minimum I would recommend you begin using try-finally blocks and putting your DatabaseConnection.CloseConnection() in the finally block.

Collectives™ on Stack Overflow

Why output parameter return 0 at c# level?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related