Converting a text entry(char) to an integer in java to be used in a java database

Question

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'."

Check this for how to convert `char` to `int`: http://stackoverflow.com/questions/4968323/java-parse-int-value-from-a-char — Jonas Czech, May 14 '15 at 19:30

score 2 · Accepted Answer · answered May 14 '15 at 19:31

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.

Oh my god thank you so much! It was something so minor but I didn't really notice the difference. — Petetheodddog, May 14 '15 at 19:40

score 1 · Answer 2 · answered May 14 '15 at 19:32

1

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.

answered May 14 '15 at 19:32

Ted Hopp

232,168
48
399
521

This really helped a lot! My friend suggested I print out everything so I could figure out what was going on but I wasn't too sure how to go about doing that but this really helped! Thank you! – Petetheodddog May 14 '15 at 19:42

Converting a text entry(char) to an integer in java to be used in a java database

2 Answers2