Arithmetic overflow error converting expression to data type int

Question

Table structure is

userid int
fname varchar(50)
lname varchar(50)
Email varchar(50)
Phone decimal(18,2)

This is procedure

ALTER procedure [dbo].[AddUser] 
     @userid int,
     @fname varchar(50),
     @lname varchar(50),
     @Email varchar(50),
     @Phone decimal(18,2)
as
    insert into UserInfo(Userid, Fname, Lname, Email, Phone) 
    values(@userid, @fname, @lname, @Email, @Phone)

This is my code....

protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["con1"]);
    try
    {
        con.Open();

        SqlCommand cmd = new SqlCommand("AddUser",con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@userid",Int32.Parse(TextBox1.Text.Trim()));
        cmd.Parameters.AddWithValue("@fname",TextBox2.Text) ;
        cmd.Parameters.AddWithValue("@lname",TextBox3.Text) ;
        cmd.Parameters.AddWithValue("@Email",TextBox4.Text);
        cmd.Parameters.AddWithValue("@Phone",TextBox5.Text);

        cmd.ExecuteNonQuery();

        Label1.Text = "Record Has Been Added Successfully!";
    }
    catch (Exception en)
    {
        Label1.Text = "Retry to add";
    }
}

If I enter Phone no value max any eg. 233242342423 it gives error... please how to solve it?

Why is phone decimal(18,2) anyway? Why does it need two decimal places? What about leading zeroes? — Martin Smith
– Martin Smith, Commented Nov 29, 2013 at 13:22
You really shouldn't use decimal or any other numeric type for a phone number! Phone numbers are not "numbers", only a sequence of digits. — marsze
– marsze, Commented Nov 29, 2013 at 13:25

Aghilas Yakoub · Accepted Answer · 2013-11-29 13:24:01Z

1

I suggest you to define varchar(15) for storing your phone. ( For performance define index on that field)

answered Nov 29, 2013 at 13:24

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yosi Dahari Over a year ago

This is true, but probably doesn't answer the question.

user3049495 Over a year ago

if am converting varchar(15)both in column and sp also i got error The conversion of the varchar value '9881526473' overflowed an int column.

liquidsnake786 · Accepted Answer · 2013-11-29 13:24:56Z

1

Not only will you lose your leading zeroes but it is bad practice to store phone numbers as integers or doubles. Use a string (text field), because you wont be doing any arithmetic on the phone number.

answered Nov 29, 2013 at 13:24

liquidsnake786

4472 silver badges8 bronze badges

1 Comment

user3049495 Over a year ago

if am converting varchar(15)both in column and sp also i got error The conversion of the varchar value '9881526473' overflowed an int column.

Govinda Rajbhar · Accepted Answer · 2013-11-29 13:27:36Z

0

you should not take phone number data type as decimal. take phone number as a string and give length like phone varchar(18). it will work.

answered Nov 29, 2013 at 13:27

Govinda Rajbhar

3,0476 gold badges42 silver badges64 bronze badges

1 Comment

user3049495 Over a year ago

if am converting varchar(50)both in column and sp also i got error The conversion of the varchar value '9881526473' overflowed an int column.

Yosi Dahari · Accepted Answer · 2013-11-29 13:33:37Z

0

The way you used AddWithValue, the command assumes all your columns of string type (except the first one).

This will not work well for the column: cmd.Parameters.AddWithValue("@Phone",TextBox5.Text)

which probably not defined as string in the database, so you need to try parsing them explicitly. Note - you need to consider replacing the type for storing your phone (varchar will be a good option).

edited Nov 29, 2013 at 13:33

answered Nov 29, 2013 at 13:26

Yosi Dahari

7,0495 gold badges26 silver badges48 bronze badges

2 Comments

user3049495 Over a year ago

if am converting varchar(15)both in column and sp also i got error The conversion of the varchar value '9881526473' overflowed an int column.

Yosi Dahari Over a year ago

@user3049495 - then you should change the column type in the database.

Glen Thomas · Accepted Answer · 2015-09-21 19:55:17Z

0

The AddWithValue method converts your value to a SQL data type based on the type of the value that you give it. If the SQL data type is a decimal you need to parse to a .NET decimal for AddWithValue to convert it correctly:

cmd.Parameters.AddWithValue("@Phone", Decimal.Parse(TextBox5.Text));

answered Sep 21, 2015 at 19:55

Glen Thomas

10.8k5 gold badges40 silver badges71 bronze badges

Comments

C. Miles · Accepted Answer · 2022-01-20 16:39:06Z

0

You may have reached the max value of an int in @userid.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver15

I would suggest using a bigint or alternatively adjusting your approach to the userid column

answered Jan 20, 2022 at 16:39

C. Miles

11 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Arithmetic overflow error converting expression to data type int

6 Answers 6

2 Comments

1 Comment

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related