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

Question

arbazpathan 0 Newbie Poster

12 Years Ago

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

Edited 12 Years Ago by ~s.o.s~ because: Added code tags, please learn to use them.

2 Contributors
7 Replies
149 Views
1 Day Discussion Span
Latest Post 12 Years Ago Latest Post by arbazpathan

All 7 Replies

Momerath 1,327 Nearly a Senior Poster

12 Years Ago

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?

Edited 12 Years Ago by Momerath because: n/a

Momerath 1,327 Nearly a Senior Poster

12 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

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

arbazpathan 0 Newbie Poster · Answer 1 · 2012-03-02T15:39:33+00:00

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

arbazpathan 0 Newbie Poster · Answer 2 · 2012-03-02T22:09:13+00:00

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 0 Newbie Poster · Answer 3 · 2012-03-02T23:24:38+00:00

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 1,327 Nearly a Senior Poster Featured Poster · Answer 4 · 2012-03-02T23:28:49+00:00

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

arbazpathan 0 Newbie Poster · Answer 5 · 2012-03-02T23:50:38+00:00

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

kk thanx

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

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers