CODEWITHSUNDEEP
javasqldatabasenetbeansinteger
Petetheodddog
Petetheodddog

Reputation: 111

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

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

Answers (2)

Ted Hopp
Ted Hopp

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

RealSkeptic
RealSkeptic

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

Related Questions