Int to numeric conversion error in SQL Server

Question

I have a question with converting data. I have a select query made in VBA, how can I convert from varchar value column to nvarchar.

I get this error:

Arithmetic overflow error converting numeric to data type numeric

This is my code:

Dim strSQL As String
 Dim rst As New ADODB.Recordset

 strSQL = " INSERT INTO dbo.Soferi_test (test1)" & _
       " SELECT MySQL.dbo.sofieri.certificat_cipti " & _
       " FROM  MySQL.dbo.sofieri " & _
       " WHERE IDNO = " & Forms!Forma_redactare_soferi!IDNO.Value

 Call AttServ(strSQL, rst)

gbn · Accepted Answer · 2013-05-17 07:06:35Z

1

Your int value is too big for the numeric precision and scale definition

Here, I guess the error comes from the WHERE clause. Not the INSERT

DECLARE @foo int = 99999;
DECLARE @n1 numeric(9,1), @n2 numeric (3,2);

-- 9,1 is 9 total digits, 1 decimal place
-- 3,2 is 2 total digits, 2 decimal places

-- 99999 requires at least 5 total digits

PRINT 'Wide enough'
SET @n1 = @foo;


PRINT 'Error'
SET @n2 = @foo;

After comment, the same still applies

DECLARE @foo numeric(5,0) = 99999;
DECLARE @n1 numeric(9,1), @n2 numeric (3,2);

PRINT 'Wide enough'
SET @n1 = @foo;


PRINT 'Error'
SET @n2 = @foo;

edited May 17, 2013 at 7:06

answered May 17, 2013 at 7:00

gbn

434k84 gold badges602 silver badges690 bronze badges

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

4 Comments

Sergio Over a year ago

sorry i had mistake its numeric to data type numeric in my error not INT

gbn Over a year ago

@Sergio: the same applies.

Sergio Over a year ago

how can i update this statments in vba? i am new one in Vba and in sql

gbn Over a year ago

You will have to ensure the datatypes in the WHERE clause match between VBA and SQL

Collectives™ on Stack Overflow

Int to numeric conversion error in SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related