System.Data.SqlClient.SqlException: Arithmetic overflow error converting varchar to d

[arbazpathan]

using System;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;


[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
    private string strConn = "";
    public Service()
    {

        strConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

    }

    [WebMethod]
    public void Update(int ppid,string ppname,int a,int b,int c,long d)
    
    {
        SqlConnection cnn = new SqlConnection(strConn);
        cnn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cnn;

        if (a == 0 && b == 0 && c == 0)
            cmd.CommandText = "update products set prod_normal_price='prod_normal_price+cast(@d as numeric(18,0))/100*prod_normal_price' where product_id=@id and prod_name=@pname";

        if (a == 0 && b == 0 && c == 1)
            cmd.CommandText = "update products set prod_normal_price='prod_normal_price-cast(@d as numeric(18,0))/100*prod_normal_price' where product_id=@id and prod_name=@pname";

        if (a == 0 && b == 0 && c == 2)
            cmd.CommandText = "update products set prod_normal_price=(prod_normal_price*cast(@d as numeric(18,0))/100*prod_normal_price) where product_id=@id and prod_name=@pname";

        if (a == 0 && b == 0 && c == 3)
            cmd.CommandText = "update products set prod_normal_price=(prod_normal_price/(cast(@d as numeric(18,0))/100*prod_normal_price)) where product_id=@id and prod_name=@pname";

        if (a == 1 && b == 0 && c == 0)
            cmd.CommandText = "update products set prod_current_price=(prod_normal_price+cast(@d as numeric(18,0))/100*prod_normal_price) where product_id=@id and prod_name=@pname";

        if (a == 1 && b == 0 && c == 1)
            cmd.CommandText = "update products set prod_current_price=(prod_normal_price-cast(@d as numeric(18,0))/100*prod_normal_price) where product_id=@id and prod_name=@pname";

        if (a == 1 && b == 0 && c == 2)
            cmd.CommandText = "update products set prod_current_price=(prod_normal_price*cast(@d as numeric(18,0))/100*prod_normal_price) where product_id=@id and prod_name=@pname";

        if (a == 1 && b == 0 && c == 3)
            cmd.CommandText = "update products set prod_current_price=(prod_normal_price/cast(@d as numeric(18,0))/100*prod_normal_price) where product_id=@id and prod_name=@pname";

        if (a == 0 && b == 1 && c == 0)
            cmd.CommandText = "update products set prod_normal_price='prod_current_price+cast(@d as numeric(18,0))/100*prod_current_price' where product_id=@id and prod_name=@pname";

        if (a == 0 && b == 1 && c == 1)
            cmd.CommandText = "update products set prod_normal_price='prod_current_price-cast(@d as numeric(18,0))/100*prod_current_price' where product_id=@id and prod_name=@pname";

        if (a == 0 && b == 1 && c == 2)
            cmd.CommandText = "update products set prod_normal_price=(prod_current_price*cast(@d as numeric(18,0))/100*prod_current_price) where product_id=@id and prod_name=@pname";

        if (a == 0 && b == 1 && c == 3)
            cmd.CommandText = "update products set prod_normal_price=(prod_current_price/(cast(@d as numeric(18,0))/100*prod_current_price)) where product_id=@id and prod_name=@pname";

        if (a == 1 && b == 1 && c == 0)
            cmd.CommandText = "update products set prod_current_price=(prod_current_price+cast(@d as numeric(18,0))/100*prod_current_price) where product_id=@id and prod_name=@pname";

        if (a == 1 && b == 1 && c == 1)
            cmd.CommandText = "update products set prod_current_price=(prod_current_price-cast(@d as numeric(18,0))/100*prod_current_price) where product_id=@id and prod_name=@pname";

        if (a == 1 && b == 1 && c == 2)
            cmd.CommandText = "update products set prod_current_price=(prod_current_price*cast(@d as numeric(18,0))/100*prod_current_price) where product_id=@id and prod_name=@pname";

        if (a == 1 && b == 1 && c == 3)
            cmd.CommandText = "update products set prod_current_price=(prod_current_price/cast(@d as numeric(18,0))/100*prod_current_price) where product_id=@id and prod_name=@pname";

        SqlParameter pid = new SqlParameter("@id",ppid);
        SqlParameter pname = new SqlParameter("@pname",ppname);
        SqlParameter pd = new SqlParameter("@d",d);
       
        cmd.Parameters.Add(pid);
        cmd.Parameters.Add(pname);
        cmd.Parameters.Add(pd);

        cmd.ExecuteNonQuery();
        cnn.Close();

    }
}

the pro_current_price and prod_normal_price both are of type numeric(18,0) in my database

[Momerath]

What is the value of d? Why are you casting it into a numeric rather than using the SqlParameter to set the type? What happens when a isn't 0 or 1? All those if statements are confusing, why don't you build the CommandText based on the values of a, b, c?

[arbazpathan]

What is the value of d? Why are you casting it into a numeric rather than using the SqlParameter to set the type? What happens when a isn't 0 or 1? All those if statements are confusing, why don't you build the CommandText based on the values of a, b, c?

the values of a,b,c will be passed by the form on which i am going to refer this web service and they will only be either 0 or 1 , and i have casted d to numeric bcoz i thought it will solve the error but was uselss .
how can i set the type using sqlparameter

[Momerath]

Take a look at the example here, and the SqlDbTypes here

And for these values of a b c you have an error (the same one) in the SQL statement:
0 0 0
0 0 1
0 1 0
0 1 1

[arbazpathan]

Take a look at the example here, and the SqlDbTypes here

And for these values of a b c you have an error (the same one) in the SQL statement:
0 0 0
0 0 1
0 1 0
0 1 1

tried setting up the parameter but not working and i have also removed the casting, the problem here i am getting is in variable d

[arbazpathan]

tried setting up the parameter but not working and i have also removed the casting, the problem here i am getting is in variable d

plz check whether this update query is correct

cmd.CommandText = "update products set prod_normal_price='prod_normal_price-@d/100*prod_normal_price' where product_id=@id and prod_name=@pname";

[Momerath]

No, don't put the ' marks in there, that turns it into a string instead of a calculation.

[arbazpathan]

No, don't put the ' marks in there, that turns it into a string instead of a calculation.

kk thanx