CODEWITHSUNDEEP
sqlsql-serverdatabase
Brohge
Brohge

Reputation: 13

Error converting data type varchar to numeric while inserting data

I have a problem with my first insert statement. I have searched online and nothing seems to help my understanding of this problem or how to fix it. When I execute the insert statement it gives me the message:

Error converting data type varchar to numeric.

Table structure:

CREATE TABLE BOOKS
(
    ISBN             INT            NOT NULL  UNIQUE,
    TITLE            VARCHAR(25)    NOT NULL,
    AuthorFname      VARCHAR(20)    NOT NULL,
    AuthorLname      VARCHAR(20)    NOT NULL,
    GENRE            VARCHAR(10)    NOT NULL,
    PUBLISHER        VARCHAR(30)    NOT NULL,
    PUBLISHERDATE    DATE           NOT NULL,
    DESCRIPTION      VARCHAR(300)   NOT NULL,
    EDITION          varchar(100)   NOT NULL,
    FORMAT           VARCHAR(20)    NOT NULL,
    PAGENUMBER       INTEGER        NOT NULL,
    FILESIZE         TEXT           NOT NULL,
    IMAGE            varchar(20)    NOT NULL,
    THUMBNAIL        varchar(20)    NOT NULL,
    PRICE            DECIMAL(9,2)   NOT NULL,
    AVAILABILITY     VARCHAR(20)    NOT NULL,

    PRIMARY KEY(ISBN),
    FOREIGN KEY(TITLE) REFERENCES WISHLIST(TITLE)
)

CREATE TABLE USERINFORMATION
(
    USEREMAIL        VARCHAR(30)    NOT NULL UNIQUE,
    USERNAME         VARCHAR(30)    NOT NULL,
    PASSWORD         TEXT           NOT NULL,
    QUESTION1        TEXT           NOT NULL,
    QUESTION2        TEXT           NOT NULL,
    ANSWER1          VARCHAR(25)    NOT NULL,
    ANSWER2          VARCHAR(25)    NOT NULL,
    BIRTHDATE        DATE           NOT NULL,
    TITLE            VARCHAR(25)    NOT NULL,

    PRIMARY KEY(USEREMAIL),
    FOREIGN KEY(USERNAME) REFERENCES REVIEWS(USERNAME)
)

CREATE TABLE BILLINGINFO
(
    FIRSTNAME        VARCHAR(20)    NOT NULL UNIQUE,
    LASTNAME         VARCHAR(20)    NOT NULL,
    USERNAME         VARCHAR(20)    NOT NULL,
    ADDRESS1         VARCHAR(35)    NOT NULL,
    ADDRESS2         VARCHAR(35)    NOT NULL,
    CITY             VARCHAR(25)    NOT NULL,
    STATE            VARCHAR(35)    NOT NULL,
    ZIP              INTEGER        NOT NULL,
    PHONE            INTEGER        NOT NULL,
    PAYMENTTYPE      TEXT           NOT NULL,
    CARDNUMBER       INTEGER        NOT NULL,
    SECURITYCODE     INTEGER        NOT NULL,
    EXPIRATIONDATE   DATE           NOT NULL,

    PRIMARY KEY(FIRSTNAME)
)

CREATE TABLE LENDINGHISTORY
(
    TITLE            VARCHAR(25)    NOT NULL UNIQUE,
    CHECKUOTDATE     DATE           NOT NULL,
    DUEDATE          DATE           NOT NULL,
    STATUS           TEXT           NOT NULL,
    USERNAME         VARCHAR(20)    NOT NULL,
    RETURNEDDATE     DATE           NOT NULL,

    PRIMARY KEY(TITLE)
)

CREATE TABLE WISHLIST
(
    TITLE            VARCHAR(25)    NOT NULL UNIQUE,
    AUTHORFNAME      TEXT           NOT NULL,
    AUTHORLNAME      TEXT           NOT NULL,
    THUMBNAIL        VARCHAR(20)    NOT NULL,
    GENRE            VARCHAR(10)    NOT NULL,
    PRICE            DECIMAL(9,2)   NOT NULL,

    PRIMARY KEY(TITLE)
)

CREATE TABLE REVIEWS
(
    USERNAME         VARCHAR(30)    NOT NULL UNIQUE,
    REVIEWTEXT       VARCHAR(40)    NOT NULL,
    RATING           INT            NOT NULL,
    TITLE            VARCHAR(25)    NOT NULL,

    PRIMARY KEY(USERNAME)
)

ALTER TABLE REVIEWS
ADD FOREIGN KEY(TITLE)
REFERENCES WISHLIST(TITLE)

INSERT INTO BOOKS
VALUES ('0547928220', 'The Hobbit', 'J.J.R.', 'Tolkien', 'Fantasy', 'Houghton Mifflin Harcourt', '9-18-2012', 
        '"In a hole in the ground, there lived a hobbit." So begins one of the most beloved and delightful tales in the English language. Set in the imaginary world of Middle-earth, at once a classic myth and a modern fairy tale, The Hobbit is one of literature s most enduring and well-loved novels.',
        '75Th Anniversary Edition', 'PaperBack', '320', 'N/A', 'the_Hobbit', 'the_Hobbit2', '$8.30', 'Available');

SELECT *
FROM BOOKS

Upvotes: 0

Views: 4887

Answers (1)

CSS
CSS

Reputation: 412

I think there are a few different things going on here. First of all, you have a line that reads drop TABLE BOOKS before the insert, which will remove the table from the registry before you can add any rows to it.

Secondly, the numerics you're trying to insert are surrounded by quotes and SQL doesn't do very well with implicit conversions to begin with. Your ISBN should probably be a VARCHAR since it contains a leading 0, which will be dropped in the event it actually converts. So @marc_s is right, drop the quotes from all your numeric values to be inserted; this includes ISBN (currently), page number, and price.

I believe the error actually refers to what you have entered in the price field, which is $8.30. A numeric doesn't know how to read $ in SQL. Also, there is a data type that coners such needs called money. Drop the $ and change the data type of the price column to money and you should be OK.

Hope this helps.

-C§

Upvotes: 1

Related Questions