1,105,226 Community Members

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

Member Avatar
arbazpathan
Newbie Poster
17 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
0
 

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!

Member Avatar
arbazpathan
Newbie Poster
17 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
arbazpathan
Newbie Poster
17 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
0
 

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.

Member Avatar
arbazpathan
Newbie Poster
17 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Question Answered as of 2 Years Ago by BitBlt
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article