Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 HELP -error converting varchar to numeric

Author  Topic 

suzannecoop
Starting Member

4 Posts

Posted - 2014-09-17 : 23:41:46
Help i have created this database with no varchar values but the database still comes up with this error for inserting medicine values into the medicine table. Can someone please help sort out my data types?

CREATE TABLE Medics (
MedicNo integer PRIMARY KEY,
MedicFName CHARACTER(30),
MedicLName CHARACTER(30)
);

CREATE TABLE Paitent (
PaitentNo integer PRIMARY KEY,
PaitentFName CHARACTER(30),
PaitentlName CHARACTER(30)
);

Create TABLE Medicine (
MedicineNo integer PRIMARY KEY,
MedicineName CHARACTER(10),
MedicineUnit integer ,
MedicinePricePU Decimal (4,2),
);


CREATE TABLE Prescription (
PrescriptionNo integer PRIMARY KEY,
PrescriptionDate Datetime,
MedicNo integer FOREIGN KEY (MedicNo)REFERENCES Medics,
PaitentNo integer FOREIGN KEY (PaitentNo)REFERENCES Paitent,
HerbNo1 Integer FOREIGN KEY (HerbNo1) REFERENCES Medicine(medicineNo),
HerbNo2 Integer FOREIGN KEY (HerbNo2) REFERENCES Medicine(medicineNo),
HerbNo3 Integer FOREIGN KEY (HerbNo3) REFERENCES Medicine(medicineNo),
);



INSERT INTO Medics VALUES ('51' ,'Sharon', 'Cooper')
INSERT INTO Medics VALUES ('52','Martin', 'Spuds')
INSERT INTO Medics VALUES ('53','Suzie','staples')
INSERT INTO Medics VALUES ('54','Evan', 'butts')
INSERT INTO Medics VALUES ('55', 'Katie', 'Crentist')

INSERT INTO Paitent VALUES ('89' ,'Fred', 'Hollows')
INSERT INTO Paitent VALUES ('88','Peter', 'Gaunt')
INSERT INTO Paitent VALUES ('87','Gary','Glitter')
INSERT INTO Paitent VALUES ('86','Freddie', 'May')
INSERT INTO Paitent VALUES ('85', 'Brian', 'Palmer')


INSERT INTO Medicine VALUES ('1','Sage','30','$0.05' )
INSERT INTO Medicine VALUES ('2','Parsely','50','$0.09')
INSERT INTO Medicine VALUES ('3','Rosemary','50','$0.02')
INSERT INTO Medicine VALUES ('4','Thyme','30','$0.09')
INSERT INTO Medicine VALUES ('5','Basil','60','$0.14')


INSERT INTO Prescription VALUES ('101' ,'12 may08', '51','88','1','2',NULL)
INSERT INTO Prescription VALUES ('102','2 jun08','51', '85', '4','1','5')
INSERT INTO Prescription VALUES ('103','6 Jun08','52','86', '2','3','1')
INSERT INTO Prescription VALUES ('104','7 Jun08', '54','87','3','4',NULL)
INSERT INTO Prescription VALUES ('105','7 Jun08','55','88','2',NULL,NULL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-18 : 00:35:25
Remove the single quotes around the integer and decimal data. For instance: INSERT INTO Medics VALUES (51 ,'Sharon', 'Cooper')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -