java.sql.SQLSyntaxErrorException: Columns of type 'INTEGER' cannot hold values of type 'CHAR'

Question

I want to be able to update my table using the codes that I tried but it always shows the error just like that

I tried the prepared statement but I don't know if I did it correctly.

So the code looks like this,

try{
    String sql = "update master.Employees set EmployeeName = '" + jTextField1.getText() + "'" + ",Address = '" + jTextField3.getText() + "'" + ",PhoneNumber = '" +
            jTextField4.getText() + "'" + ",EmailAddress = '" + jTextField5.getText() + "'" + "WHERE EmployeeID = " + jTextField1.getText();
    Statement update = myconObj.createStatement();
    update.executeUpdate(sql);
}
catch (SQLException e){
    e.printStackTrace();
}

i tried to enter the employee Id which is the primary key of my database to be able to update but it always shows the error:

java.sql.SQLSyntaxErrorException: Columns of type 'INTEGER' cannot hold values of type 'CHAR'.

I think I messed up the grab structure because it shows this one

¬í sr %org.netbeans.lib.ddl.impl.CreateTable¢”j ™   xr +org.netbeans.lib.ddl.impl.ColumnListCommand2›Œî]2)³ L columnst Ljava/util/Vector;xr )org.netbeans.lib.ddl.impl.AbstractCommandø8¦Œ·q•ª Z executionWithExceptionZ    newObjectL addpropst Ljava/util/Map;L formatt Ljava/lang/String;L nameq ~ L ownerq ~ L quoteStrq ~ L spect ,Lorg/netbeans/lib/ddl/DatabaseSpecification;xp pt :create table [{object.owner}.]{object.name}
(
    {columns}
)t  EMPLOYEESpppsr java.util.VectorÙ—}[€;¯ I capacityIncrementI elementCount[ elementDatat [Ljava/lang/Object;xp       ur [Ljava.lang.Object;ÎXŸs)l  xp   
sr %org.netbeans.lib.ddl.impl.TableColumn;¦±5‘b  I decsizeZ nullableI sizeI typeL checkeq ~ L constraintColumnsq ~ L defvalq ~ xr -org.netbeans.lib.ddl.impl.AbstractTableColumn¸Ô§y[k  Z   newColumnZ  newObjectL addpropsq ~ L cnameq ~ L formatq ~ L nameq ~ L otypeq ~ L refcolq ~ L reftabq ~ xppt 
EMPLOYEEIDt ³{column.name} {column.type}[({column.size}[,{column.decsize}])][ {column.type.suffix}][ default {default.value}][ {column.notnull}not null][ check ({check.condition})] primary keyt 
EMPLOYEEID_PKt PRIMARY_KEYpp        
   pppsq ~ pt EMPLOYEENAMEt §{column.name} {column.type}[({column.size}[,{column.decsize}])][ {column.type.suffix}][ default {default.value}][ {column.notnull}not null][ check ({check.condition})]q ~ t COLUMNpp       2   pppsq ~ pt ADDRESSq ~ q ~ q ~ pp       2   pppsq ~ pt PHONENUMBERq ~ q ~ q ~ pp       
   pppsq ~ pt EMAILADDRESSq ~ q ~ q ~ pp       2   pppsq ~ pt IMAGEq ~ q ~ !q ~ pp       2   pppppppx

the table is created in the database within the netbeans and y primary key is the employeeID which is integere. — Shaggy Lid
– Shaggy Lid, Commented May 9, 2019 at 14:02
Please learn how to use prepared statements. Concatenating values into a query string is unsafe as it opens your code to SQL injection, and in addition it can cause all kinds of other errors. — Mark Rotteveel
– Mark Rotteveel, Commented May 9, 2019 at 15:11

Swati · Accepted Answer · 2019-05-09 14:41:25Z

2

Try to used PreparedStatement like below :

 try
      {
          String sql = "UPDATE `master.Employees` SET `EmployeeName`=?, `Address `=?, `PhoneNumber`=?, `EmailAddress`=? WHERE `EmployeeID `=?";
         PreparedStatement ps = con.prepareStatement(sql);
         //putting value for all placeholder (?)
         ps.setString(1,jTextField2.getText());
         ps.setString(2,jTextField3.getText());
         ps.setInt(3,Integer.parseInt(jTextField4.getText()));
         ps.setString(4,jTextField5.getText());
         ps.setInt(5,Integer.parseInt(jTextField1.getText()));

         int i=0;
         i = ps.executeUpdate();
         if(i>0)
             {
         //do   something   
             }
     }
     catch(Exception e)
      {

      e.printStackTrace();

      }

answered May 9, 2019 at 14:41

Swati

28.5k4 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Swati Over a year ago

Glad i helped :)

Angabo · Accepted Answer · 2019-05-09 14:04:52Z

0

You need to convert the value of jTextField1.getText (), to pass an integer. You can do it in the following way before doing the update

int id = Integer.parseInt (jTextField1.getText ());

edited May 9, 2019 at 14:04

answered May 9, 2019 at 13:57

Angabo

1299 bronze badges

1 Comment

Angabo Over a year ago

edit the question and put the DDL scheme as comments @Mureinik to help you better

jeffrey · Accepted Answer · 2019-05-09 14:13:42Z

0

maybe other field is Integer, you can check PhoneNumber this is desgined by Integer?

answered May 9, 2019 at 14:13

jeffrey

312 bronze badges

1 Comment

Shaggy Lid Over a year ago

Yes, the employeeId and phone number is integer

Angabo · Accepted Answer · 2019-05-09 14:37:13Z

0

The function call jTextField1.getText() basically returns a String object and the database is expecting an integer. Try Integer.parseInt(jTextField1.getText()) instead.

edited May 9, 2019 at 14:37

Angabo

1299 bronze badges

answered May 9, 2019 at 13:55

rickskeggs

241 bronze badge

7 Comments

Shaggy Lid Over a year ago

I did what you said but, it shows the same error again

rickskeggs Over a year ago

As @Mureinik suggested we will need to look at the DDL of the employees table.

Shaggy Lid Over a year ago

uhm, i'm sorry but where can i find the DDL of my database in netbeans???

rickskeggs Over a year ago

You will need to go to the services tab. Select on the database you are writing to and expand. Select the Tables folder and highlight the Employees table and right click. One option should be 'Grab Structure'.

Shaggy Lid Over a year ago

I think i messed up that part

|

minus · Accepted Answer · 2019-05-09 14:38:28Z

0

The problem may be spacing.

instead of

+ "WHERE EmployeeID = " + jTextField1.getText();

you should write

+ " WHERE EmployeeID = " + jTextField1.getText();

answered May 9, 2019 at 14:38

minus

2,78618 silver badges19 bronze badges

Comments

user2807083 · Accepted Answer · 2021-01-08 18:11:18Z

0

Remove '' from integer value phone number so that your code looks like this

String sql = "update master.Employees set EmployeeName = '" + jTextField1.getText() + "'" + ",Address = '" + jTextField3.getText() + "'" + ",PhoneNumber = " +
        jTextField4.getText() + " + ",EmailAddress = '" + jTextField5.getText() + "'" + "WHERE EmployeeID = " + jTextField1.getText();
Statement update = myconObj.createStatement();

Hint:

Char='123434'
Integer=12345

edited Jan 8, 2021 at 18:11

user2807083

3,0024 gold badges32 silver badges38 bronze badges

answered Jan 8, 2021 at 16:44

Muhammad Fayaz

13 bronze badges

Collectives™ on Stack Overflow

java.sql.SQLSyntaxErrorException: Columns of type 'INTEGER' cannot hold values of type 'CHAR'

6 Answers 6

1 Comment

1 Comment

1 Comment

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

1 Comment

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related