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

[arbazpathan]

I have developed following web service in vs2008 but cannot resolve th following error
System.Data.SqlClient.SqlException: Arithmetic overflow error converting varchar to data type numeric.

here is my web service code

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 prod_normal_price and prod_current_types both are of type numric(18,0) in my sql db

[BitBlt]

This is just a guess, but it appears when you set your dynamic sql commands up, you are surrounding your calculated number with single-quotes (which makes it into a string literal). You should probably omit the single-quotes.

Try something more like this:
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";

Of course, you'll have to do that with each iteration of that. I haven't tested this, but I think it will help. Good luck!

[arbazpathan]

This is just a guess, but it appears when you set your dynamic sql commands up, you are surrounding your calculated number with single-quotes (which makes it into a string literal). You should probably omit the single-quotes.

Try something more like this:
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";

Of course, you'll have to do that with each iteration of that. I haven't tested this, but I think it will help. Good luck!

tried it but now getting following error
System.Data.SqlClient.SqlException: Must declare the scalar variable "@d".

plz help me sorting out this new one

[arbazpathan]

tried it but now getting following error
System.Data.SqlClient.SqlException: Must declare the scalar variable "@d".

plz help me sorting out this new one

got it finally this time it is not showing any error but values in the database are not updating plz check whether my update query is correct

[BitBlt]

Since I don't know what your table structures look like, and I don't know what your data looks like, and I don't know what your actual SQL statement looks like at execution time, that's pretty tough for me to do.

I can give you some general advice:
1. Make sure the column names in your queries match up with your table definitions.
2. Make sure that the parameter values you pass will actually get a "hit" in your data.
3. Use SSMS to write a query that demonstrably works, then copy/paste that statement and alter it to point to your parameter values in your program code.
4. Use code tags when you post, so you can refer to specific lines to be looked at, and I can tell you what line number to look at in return.

Other than that, I can't tell you. Check on those things, and if it still doesn't work then at least you've ruled those things out.

[arbazpathan]

Since I don't know what your table structures look like, and I don't know what your data looks like, and I don't know what your actual SQL statement looks like at execution time, that's pretty tough for me to do.

I can give you some general advice:
1. Make sure the column names in your queries match up with your table definitions.
2. Make sure that the parameter values you pass will actually get a "hit" in your data.
3. Use SSMS to write a query that demonstrably works, then copy/paste that statement and alter it to point to your parameter values in your program code.
4. Use code tags when you post, so you can refer to specific lines to be looked at, and I can tell you what line number to look at in return.

Other than that, I can't tell you. Check on those things, and if it still doesn't work then at least you've ruled those things out.

thanks now everything is working fine really appreciate your effort