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

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

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?

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

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

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

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

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

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

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

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";
Re: System.Data.SqlClient.SqlException: Arithmetic overflow error converting varchar to d 80 80

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

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

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

kk thanx

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.