Link to home
Start Free TrialLog in
Avatar of BoggyBayouBoy
BoggyBayouBoy

asked on

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??  
       
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BoggyBayouBoy
BoggyBayouBoy

ASKER

once again... Thanks angellll !!!!