Reputation: 111
I'm making a database system for a school project(Matric PAT) where I have to make a database in java (for this I'm using netbeans) and then I have to create a java program that connects to the data and is able to perform specific tasks like change data in the database, display data etc.
I'm creating a way to add a new entry, I've made a Jframe form (GUI) that I am using to do entries and everything is alright so far except, when I try to add the entry it says that I cannot put char variable types into a column that has a variable type of an integer. I feel like I've tried everything but I don't know how to fix this.
Here is my SQL statement that I used to generate the table for my database that I'm working on:
CREATE TABLE tblPatients
(
Patient_ID integer NOT NULL,
First_Name varchar(25) NOT NULL,
Last_Name varchar(25) NOT NULL,
Date_Of_Birth date,
Sex varchar (6),
Address varchar(25),
City varchar(25),
PostalCode varchar (4),
Charge integer,
Paid BOOLEAN,
Doctor_ID integer NOT NULL,
PRIMARY KEY (Patient_ID),
FOREIGN KEY (Doctor_ID) REFERENCES tblDoctors(Doctor_ID)
)
And this is the code that I'm using to add an entry to the database:
private void btnAddPatientActionPerformed(java.awt.event.ActionEvent evt) {
try
{
String url = "jdbc:derby://localhost:1527/PAT_DB";
//Only use these if the database has a username or password
//String username = "";
//String password = "";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();
String Query = "INSERT INTO TBLPATIENTS (PATIENT_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, SEX, ADDRESS, CITY, "
+ "POSTALCODE, CHARGE, PAID, DOCTOR_ID) VALUES ('"+PatientID_field.getText()+"', "
+ "'"+FirstName_field.getText()+"', '"+LastName_field.getText()+"', "
+ "'"+day_field.getSelectedItem()+month_field.getSelectedItem()+year_field.getSelectedItem()+"',"
+ " '"+Sex_field.getSelectedItem()+"', '"+Address_field.getText()+"', '"+City_field.getText()+"',"
+ " '"+PostalCode_field.getText()+"', '"+Charge_field.getText()+"',"
+ " '"+Paid_field.getSelectedItem()+"', '"+DoctorID_field.getText()+"')";
stmt.execute(Query);
JOptionPane.showMessageDialog(null, "Patient added to the database.");
PatientID_field.setText(null);
FirstName_field.setText(null);
LastName_field.setText(null);
day_field.setSelectedItem("00");
month_field.setSelectedItem("00");
year_field.setSelectedItem("0000");
Sex_field.setSelectedItem("0");
Address_field.setText(null);
City_field.setText(null);
PostalCode_field.setText(null);
Charge_field.setText("0");
Paid_field.setSelectedItem(0);
DoctorID_field.setText(null);
}
catch(SQLException ex)
{
JOptionPane.showMessageDialog(null, ex.toString());
}
}
I would add a picture of my GUI and the error message that I get but I don't have enough rep yet, however this is the error message: "java.sql.SQLSyntaxErrorException: Columns of type 'INTEGER' cannot hold values of type 'CHAR'."
Upvotes: 1
Views: 1820
Reputation: 234867
The first thing I noticed is that you are passing PatientID_field.getText()
to MySQL in single quotes, while it is declared as INTEGER
. You may have other fields with the same problem; I didn't bother looking for all of the problems of this type. I suggest you print out the value of Query
before using it. You can then see where you are supplying a string where the INSERT
statement expects an integer.
Upvotes: 1
Reputation: 34648
Some of the values that you put in your statement are surrounded by single quotes. For example, this line:
+ " '"+PostalCode_field.getText()+"', '"+Charge_field.getText()+"',"
Not all databases accept values surrounded by single quotes as valid values for integers/numbers, so this should be:
+ " '"+PostalCode_field.getText()+"', "+Charge_field.getText()+","
But I have to strongly recommend that you use a prepared statement instead. Building a statement like this is prone to SQL injection and makes the code hard to read and debug. A prepared statement also takes care of the single quotes for you based on the the type of the value.
Upvotes: 2