Fetchhing price from db to textbox

sangeetha k

9y1kLearn iOS Programming

i want fetch a single data named price from db to textbox but it yeilds 0

#My Stored procedure

alter PROCEDURE [dbo].[spGetPrice]

@productId as int,

@categoryId as int,

@price as int output

BEGIN

SELECT @price=price from product04 where product_id=@productId and category_id=@categoryId

END

#My dll

conn.Open();

SqlCommand cmd = new SqlCommand("spGetPrice", conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@productId", DbType.Int32).Value = productId;

cmd.Parameters.AddWithValue("@categoryId", DbType.Int32).Value = categoryId;

cmd.Parameters.Add("@price", SqlDbType.Int);

cmd.Parameters["@price"].Direction = ParameterDirection.Output;

price=Convert.ToInt32( cmd.ExecuteScalar());

#My bll

int price;

try

{

price = dal.GetPrice(categoryId, productId);

return price;

}

catch

{

throw;

}

#code behind

protected void btnGetDetails_Click(object sender, EventArgs e)

{

int productId=Convert.ToInt32(txtProductid.Text);

int categoryId=Convert.ToInt32(txtCategoryId.Text);

txtPrice.Text = bll.GetProductPrice(categoryId, productId).ToString();

}

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

sangeetha k9y ago
everything was fine it was just a minor error i got it it was mapping the product id and category id wrongly that's it thank you all :)
0
Ankit Sharma9y ago
Hi Sangeetha,

you cannot retrieve values from a Select statement and assign value to a variable at the same time
so you need to alter you SP as below
alter PROCEDURE [dbo].[spGetPrice]
@productId as int,
@categoryId as int,
@price as int output
AS
BEGIN
SELECT @price=price from product04 where product_id=@productId and category_id=@categoryId
SELECT @price
END
GO
Try this and let me know if it works
0
Ramesh Palanivel9y ago
Hi Sangeetha,

Please share your dll full code
0
sangeetha k9y ago
In Three tier architecture
0
sangeetha k9y ago
someone else tell me the other way of doing it so that i can get it right
0
sangeetha k9y ago
sp gives the value
0
Amit Gupta9y ago
Hey

Have you tried executing your sp in smss? Check whether your sp gives 0 or some value.

Ref
http://www.c-sharpcorner.com/UploadFile/rohatash/get-out-parameter-from-a-stored-procedure-in-Asp-Net/
0
sangeetha k9y ago
I have changed the code to this format even now it gives this yeild zero

conn.Open();
SqlCommand cmd = new SqlCommand("spGetPrice", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@productId", productId));
cmd.Parameters.Add(new SqlParameter("@categoryId",categoryId));
cmd.Parameters.Add("@price", SqlDbType.Int);
cmd.Parameters["@price"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int price= Convert.ToInt32( cmd.Parameters["@price"].Value);
0
Ramesh Palanivel9y ago
Hi sangeetha,

change this code,

cmd.Parameters.AddWithValue("@productId", productId);
cmd.Parameters.AddWithValue("@categoryId", categoryId);
0
sangeetha k9y ago
even then it fetches zero only
0
Ramesh Palanivel9y ago
Hi Sangeetha,

Please change the below code,

price=Convert.ToInt32( cmd.ExecuteScalar());

price = Convert.ToInt32(cmd.Parameters["@price"].Value);
0