money in stored procedure

Hi there,

I have a table products with product_price money(8) field. I have a stored procedure,

CREATE procedure p_dat_update_product(
@.product_id int,
@.product_price decimal
)
as
set nocount on

declare
@.msg varchar(255) -- error message holder

-- remove any leading/trailing spaces from parameters
select @.product_id = ltrim(rtrim(@.product_id))
select @.product_price = ltrim(rtrim(@.product_price))

-- turn [nullable] empty string parameters into NULLs
if (@.product_id = N'') select @.product_id = null
if (@.product_price = N'') select @.product_price = null

-- start the validation
if @.product_id is null
begin
select @.msg = 'The value for variable p_dat_update_product.@.product_id cannot be null!'
goto ErrHandler
end

-- execute the query
if exists (select 'x' from product where product_id = @.product_id)
begin
update dbo.product
set
product_price = @.product_price
where product_id = @.product_id
end

if (@.@.ERROR <> 0) goto Errhandler

return
ErrHandler:
raiserror 30001 @.msg
return
GO

And a class function

public void Edit_Product(string product_id,string product_price)
{

decimal dec_product_price = decimal.Parse(product_price);

SqlDataServ oMisDb = new SqlDataServ("LocalSuppliers");
oMisDb.AddParameter("@.product_id", product_id);
oMisDb.AddParameter("@.product_price", dec_product_price.ToString());
oMisDb.ExecuteCmd("p_dat_update_product");
}

But i keep getting this error,

Error converting data type nvarchar to numeric

oMisDb.ExecuteCmd("p_dat_update_product");

I do not know what to do and solve this. Is there something that i have done wrong.

thanks in advance.

PS: still learning aspx and ms sql

Hi

the stored proc accepts int and decimal

priteshchandra:

CREATE procedure p_dat_update_product(
@.product_id int,
@.product_price decimal
)

while you are passing string

priteshchandra:

public void Edit_Product(string product_id,string product_price)

so you need to cast them to appropriate datatypes

priteshchandra:

oMisDb.AddParameter("@.product_id", product_id);
oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

should be

oMisDb.AddParameter("@.product_id", convert.toint32(product_id));
oMisDb.AddParameter("@.product_price", convert.toDecimal(dec_product_price));

hope that helps.

thanks,

satish.

|||

I tired ur solution but got this error.

Compiler Error Message:CS0103: The name 'convert' does not exist in the current context

oMisDb.AddParameter("@.product_price", convert.toDecimal(dec_product_price));

|||

aargh my friend

Convert.ToDecimal() , do you've .net documentation/MSDN or not?

its straight forward naming convention of C#.

thanks,

satish.

|||

I think he meant Decimal.Parse() and Int32.Parse()?

|||

Motley:

I think he meant Decimal.Parse() and Int32.Parse()?

yes infact TryParse would be efficient.

|||

Just replace this line:

oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

with this:

oMisDb.AddParameter("@.product_price", dec_product_price);

because you already converted it to decimal here:

decimal dec_product_price = decimal.Parse(product_price);

|||

kipo:

Just replace this line:

oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

with this:

oMisDb.AddParameter("@.product_price", dec_product_price);

because you already converted it to decimal here:

decimal dec_product_price = decimal.Parse(product_price);

I tired this before and i kept getting this error,

CS1502:The best overloaded method match for'MISUtilities.DataServices.SqlDataServ.AddParameter(string, string)'has some invalid arguments

Source Error:

Line 359: oMisDb.AddParameter("@.product_price", dec_product_price);

|||

satish_nagdev:

aargh my friend

Convert.ToDecimal() , do you've .net documentation/MSDN or not?

its straight forward naming convention of C#.

thanks,

satish.

Sory, i do not have MSDN. am using Express Edition for now since me learning .NET

|||

I believe you are looking for this syntax:

oMdb.Parameters.Add("@.param",SqlDbType.Int).Value={something}

or

oMdb.Parameters.AddWithValue("@.param", {something})

of course, replace {something} with a constant, variable, etc.

|||

After working to finding a solution, i kept failing. eventually i came up with this.

i changed the field from money to nvarchar and everything worked fine on wards.

question:

replacing money with nvarchar, will it affect my data in any way already stored?

|||

Hi Pritesh,

you said money datatype and in actual post i could see you are accepting decimal datatype. i think that is the actual problem.

regarding replacing money with nvarchar is not a good idea. there might be cases when you need to work on numeric (money) so you'll do conversion back and forth to money and varchar.

so in your table the column is of type money right? if yes modify your sp to accept money instead of decimal and in c# code use overloaded parameter constructor and pass datatype like sqlint etc

thanks,

satish.

|||

well, i tired placing money in the stored procedure but i kept getting error that money is not valid in sp.