How to call a parameterized stored procedure from C# and ADO.NET

I have a stored Procedure like so...
ALTER procedure [dbo].[countProduct]
(@ProductID nvarchar(20) ,@qtyinteger output )
as
set @qty= (select count(*) from Products where ProductID = @ProductID)
RETURN @qty

and I want return the value of @qty via a C# method like so:
 protected int instock(string productID)
    {
        string ProductID = productID;
        string strConn = "Data Source=dev1;uid=sa;pwd=pass@word1;Initial Catalog=Products";
        SqlConnection myconn = new SqlConnection(strConn);

        SqlCommand getcount = new SqlCommand("countProduct", myconn);
        getcount.CommandType = CommandType.StoredProcedure;

        SqlParameter parmRetVal = getcount.Parameters.Add("@qty", SqlDbType.Int);
        parmRetVal.Direction = ParameterDirection.ReturnValue;
       
        SqlParameter parmProduct = getcount.Parameters.Add("@ProductID", SqlDbType.NVarChar, 20);
        parmProduct.Direction = ParameterDirection.Input;
       
        parmProduct.Value = ProductID;
        parmRetVal.Value = 0;
        myconn.Open();
        int count = Int32.Parse( getcount.ExecuteScalar().ToString());
        return count;      
    }

I get the following error: "Procedure or function 'countProduct' expects parameter '@qty', which was not supplied."  

Do you see anything wrong with my code??  
       
LVL 1
BoggyBayouBoyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
as the parameter is OUTPUT, the RETURN is not needed
ALTER procedure [dbo].[countProduct] 
(@ProductID nvarchar(20) ,@qty integer output )
as
  select @qty =  count(*) from Products where ProductID = @ProductID

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and your c# code goes like this:
 protected int instock(string productID)
    {
        string ProductID = productID;
        string strConn = "Data Source=dev1;uid=sa;pwd=pass@word1;Initial Catalog=Products";
        SqlConnection myconn = new SqlConnection(strConn); 
        SqlCommand getcount = new SqlCommand("countProduct", myconn);
        getcount.CommandType = CommandType.StoredProcedure;
        
        SqlParameter parmProduct = getcount.Parameters.Add("@ProductID", SqlDbType.NVarChar, 20);
        parmProduct.Direction = ParameterDirection.Input; 
        SqlParameter parmRetVal = getcount.Parameters.Add("@qty", SqlDbType.Int);
        parmRetVal.Direction = ParameterDirection.Output;         
        parmProduct.Value = ProductID;
        myconn.Open();
        getcount.ExecuteNonQuery(); 
        int count = parmRetVal.Value;
        return count;       
    }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BoggyBayouBoyAuthor Commented:
once again... Thanks angellll !!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.