Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Getting "Error converting data type varchar to numeric."

Basically I’m trying to insert 5 rows of data into my Book Table and it gives me an error saying that it can’t convert the data type varchar to numeric.

This is the query I was entering

INSERT INTO Book (BookID,Name,Author,Price,PublishedDate,PublisherID)
VALUES
('B01','Maths','J.Wenton','50.60','10 Jan 2016','P01'),
('B02','Science','S.Hanson','100.00','12 Feb 2016','P01'),
('B03','English','K.Vince','89.30','9 March 2016','P02'),
('B04','Biology','K.Vince','150.80','24 April 2016','P03'),
('B05','Computing','J.Denzin','NULL','NULL','NULL');`

When creating my table I used this

CREATE TABLE Book (
BookID nvarchar(50),
Name nvarchar(50),
Author nvarchar(50),
Price decimal(10,2),
PublishedDate date,
PublisherID nvarchar(50),
);

I want to be able to enter the 5 rows of data into my Book Table.

Tags:

2

Answers


  1. 0

    The string literal 'NULL' can’t be converted to a number – you should use a NULL literal (note the lack of quotes). While you’re at it, although it is possible to convert string literals that represent numbers to numbers, there’s no reason to do so in this context, and you should probably lose the quotes around the numbers:

    INSERT INTO Book (BookID,Name,Author,Price,PublishedDate,PublisherID)
VALUES
('B01', 'Maths', 'J.Wenton', 50.60, '10 Jan 2016', 'P01'),
('B02', 'Science', 'S.Hanson', 100.00, '12 Feb 2016', 'P01'),
('B03', 'English', 'K.Vince', 89.30, '9 March 2016', 'P02'),
('B04', 'Biology', 'K.Vince', 150.80, '24 April 2016', 'P03'),
('B05', 'Computing', 'J.Denzin', NULL, NULL, NULL);
    Login or Signup to reply.
  2. 0

    ‘NULL’ and simply NULL without single quotation marks are different entities. ‘NULL’ is a string, and NULL without quotes is a blank cell in your table. When you try to insert ‘NULL’ into your Book table, the database considers ‘NULL’ as a string. So when it comes to inserting in a Price cell the string, it shows an error. Try to delete all single quotation marks in your request and that should work.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search