Getting "invalid character value for cast" error while inserting data into table. Java

Question

try {               
                    Connection conn=DriverManager.getConnection("jdbc:ucanaccess://E:\\testing.accdb");
                    String sql="insert into Books (Book_ID,Book_Name,Subject_ID,Author) values (?,?,?,?) ";
                    PreparedStatement pst=conn.prepareStatement(sql);
                    pst.setString(1, textField_BN.getText());
                    pst.setString(2, textField_SID.getText());
                    pst.setString(3, textField_BID.getText());
                    pst.setString(4, textField_A.getText());
                    pst.executeUpdate();

                    JOptionPane.showMessageDialog(null, "Data Saved");

                } catch (Exception e) {
                    e.printStackTrace();
                }

I made a connection using Ucanaccess, it is successful and printing tables. However, I tried writing an insert query that would input rows into the table it is giving me the error

UCAExc:::4.0.4 data exception: invalid character value for cast at net.ucanaccess.jdbc.UcanaccessPreparedStatement.setString(UcanaccessPreparedStatement.java:742)

and you don't understand the meaning of `invalid character value for cast at ... setString`? I mean, we don't know which string you try to insert nor do we know which line it is ... check the values and the table schema. And shouldn't `textField_BID.getText()` not be the first value of the insert? I mean, `pst.setString(1, textField_BID.getText());` — UninformedUser, Mar 15 '19 at 18:39
It looks like `textField_SID.getText()` should come at the first place. — Benoit, Mar 15 '19 at 18:39
you have `(Book_ID,Book_Name,Subject_ID,Author)` so it should be `pst.setString(1, textField_BID.getText()); pst.setString(2, textField_BN.getText()); pst.setString(3, textField_SID.getText()); pst.setString(4, textField_A.getText());` - once you fixed this, we can check for further issues but right now the ordering doesn'T match your table def — UninformedUser, Mar 15 '19 at 18:43

score 0 · Accepted Answer · answered Mar 15 '19 at 19:31

Look at line 742 in your java class, see what field it is setting and then validate you are passing in a string. the cast exception happens because of invalid data type being passed into the prepared statement that expects a different data type based on your schema. If you have to add some debugging to print the values of each object value you are setting and also check your schema to make sure your java data types being set in the prepared statement match up.

"invalid character value for cast" error on INSERT via UCanAccess

Getting "invalid character value for cast" error while inserting data into table. Java

1 Answers1