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=pas s@word1;In itial Catalog=Products";
SqlConnection myconn = new SqlConnection(strConn);
SqlCommand getcount = new SqlCommand("countProduct", myconn);
getcount.CommandType = CommandType.StoredProcedur e;
SqlParameter parmRetVal = getcount.Parameters.Add("@ qty", SqlDbType.Int);
parmRetVal.Direction = ParameterDirection.ReturnV alue;
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().T oString()) ;
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??
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=pas
SqlConnection myconn = new SqlConnection(strConn);
SqlCommand getcount = new SqlCommand("countProduct",
getcount.CommandType = CommandType.StoredProcedur
SqlParameter parmRetVal = getcount.Parameters.Add("@
parmRetVal.Direction = ParameterDirection.ReturnV
SqlParameter parmProduct = getcount.Parameters.Add("@
parmProduct.Direction = ParameterDirection.Input;
parmProduct.Value = ProductID;
parmRetVal.Value = 0;
myconn.Open();
int count = Int32.Parse( getcount.ExecuteScalar().T
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??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
once again... Thanks angellll !!!!
Open in new window