1

I am trying to insert some data into this table, but I get this error:

Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.

SQL INSERT STATEMENT

I tried to remove the quotes, but it is still showing me the same error:

USE CobornSalesDB; 
GO 

INSERT INTO SalesActivity 
VALUES ('AC00001', '05-12-2016', 'AG16170', 'C000001', 'P0001', 'S00002'‌​, '1', '200000.00', '',‌ ​'1.2220', '20', '100000.00', '25-12-2016', '30-12-2016', '31-12-2016', 'A00‌​0001', 'PR00001'); 
GO
Improve this question
4
  • 2
    Single quotes are for string literals. Remove them for numeric data. Commented Oct 18, 2017 at 21:23
  • the QTY should be without quotes and so are the other numerical data.... remove commas for thousand separator Commented Oct 18, 2017 at 21:24
  • 2
    Although single quotes aren't needed for numeric values, they won't cause an error if they are used. But the commas will. Commented Oct 18, 2017 at 21:32
  • Hi i did that but is still showing me the same error USE CobornSalesDB; GO INSERT INTO SalesActivity VALUES ('AC00001','05-12-2016','AG16170','C000001','P0001','S00002','1','200000.00','','1.2220', '20','100000.00','25-12-2016','30-12-2016','31-12-2016','A000001','PR00001'); GO Commented Oct 18, 2017 at 21:57

3 Answers 3

Reset to default
3

In all of your numeric columns, take the commas out of the values you are inserting.

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

1 Comment

Its still not working i try this way as well i dont know what i am doing wrong USE CobornSalesDB; GO INSERT INTO SalesActivity VALUES ('AC00001','05-12-2016','AG16170','C000001','P0001','S00002',1,200000.00,'',1.2220, 20,100000.00,'25-12-2016','30-12-2016','31-12-2016','A000001','PR00001'); GO
2

This is your query taken from the comment. You really should include it in the question as text, not as image.

INSERT INTO SalesActivity VALUES 
('AC00001',
'05-12-2016',
'AG16170',
'C000001',
'P0001',
'S00002'‌​,
'1',
'200000.00',
'',‌              -- valueEUR
​'1.2220', 
'20',
'100000.00',
'25-12-2016',
'30-12-2016',
'31-12-2016',
'A00‌​0001',
'PR00001');

In the definition of the table we can see that valueEUR column is numeric. You are passing a string there. Not just a string, but a string that can not be converted into a number. An empty string '' can't be converted into a number.

I'm guessing, you want to insert NULL in that field. So, you should

  • write NULL in the INSERT statement.
  • Also, you should remove all quotes around numbers, so that the server would not have to convert strings into numbers.
  • Also, you should write your dates in YYYY-MM-DD format. Otherwise, one day you may be surprised to see that server guessed it incorrectly and swapped month and day.
  • Also, you should list all column names in the INSERT statement. Otherwise your code will break when you add a new column to the table.

The query should look similar to this:

INSERT INTO dbo.SalesActivity 
    (Activity_ID,
    [Date],
    Quatation_Number,
    Customer_ID,
    Product_ID,
    Status_ID,
    Quantity,
    valueGBR,
    valueEUR,
    Rate,
    Commission,
    weightedValue,
    estDecisionDate,
    currentEstCompletionDate,
    originalEstCompletionDate,
    Agent_ID,
    Probability_ID)
VALUES 
    ('AC00001',
    '2016-12-05',
    'AG16170',
    'C000001',
    'P0001',
    'S00002'‌​,
    1,
    200000.00,
    NULL,‌
    ​1.2220, 
    20,
    100000.00,
    '2016-12-25',
    '2016-12-30',
    '2016-12-31',
    'A00‌​0001',
    'PR00001');
Improve this answer

2 Comments

Hi i try to do it like this but is giving this error: Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '‌'.
@GeorgiGeorgiev, please update the question with the new query that you are trying to run. In my answer the query doesn't have '' at all, so I have no idea where you took it from.
0

Try entering date in mm/dd/yyyy format like this

INSERT INTO SalesActivity VALUES ('AC00001','12-05-2016','AG16170','C000001',
'P0001','S00002'‌​,1,200000.00,'',‌ ​1.2220, 20,100000.00,
'12-25-2016','12-30-2016','12-31-2016','A00‌​0001','PR00001');
Improve this answer

3 Comments

mm/dd/yyyy is an ambigous format, because in Europe many countries use the dd/mm/yyyy format. I would go for the yyyy/mm/dd format.
hi now is giving meths error Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '‌'.
INSERT INTO SalesActivity VALUES ('AC00001','12-05-2016','AG16170','C000001', 'P0001','S00002'‌​,1,200000,'',‌ ​1.2220, 20,100000, '12-25-2016','12-30-2016','12-31-2016','A00‌​0001','PR00001');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.