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
{ }) on the editor toolbar to nicely format and syntax highlight it!INSERT INTO dbo.Books(ISBN, ....). Second: if you insert a numerical value - there's absolutely no point in putting that value into single quotes!'$8.30'as the price - just use8.30(no single quotes, no currency sign) and you should be fine