I keep getting this exception. I want to insert using a query into a table called Toy.
It’s in this order as below:
Toy ID
, Toy Name
, age
, price(as currency data type)
, color
Exception: invalid character value for cast
import java.sql.*; public class market{ public static void main(String[] args) throws Exception { Connection c = DriverManager.getConnection("jdbc:ucanaccess://C:\Users\Thekr\OneDrive\Documents\Market.accdb"); Statement stmt = c. createStatement(); String query4= "INSERT INTO Toy VALUES (6,'JB Truck',3,20.00,'Yellow')";//here is where the exception is happening the data type of each column: integer,string,integer,currency data type that I dont know what is it,string stmt.executeUpdate(query4); // the excepting is : // Exception in thread "main" net.ucanaccess.jdbc.UcanaccessSQLException: // > UCAExc:::5.0.0-SNAPSHOT data exception: invalid character value for // > cast at // > net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:230) // > at market.main(market.java:11) Caused by: java.sql.SQLDataException: // > data exception: invalid character value for cast at // > org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at // > org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at // > org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) at // > org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source) at // > net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:65) // > at // > net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:264) // > at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:48) // > at // > net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:228) // > ... 1 more Caused by: org.hsqldb.HsqlException: data exception: // > invalid character value for cast at // > org.hsqldb.error.Error.error(Unknown Source) at // > org.hsqldb.error.Error.error(Unknown Source) at // > org.hsqldb.Scanner.convertToNumber(Unknown Source) at // > org.hsqldb.types.NumberType.convertToType(Unknown Source) at // > org.hsqldb.StatementDML.getInsertData(Unknown Source) at // > org.hsqldb.StatementInsert.getResult(Unknown Source) at // > org.hsqldb.StatementDMQL.execute(Unknown Source) at // > org.hsqldb.Session.executeCompiledStatement(Unknown Source) at // > org.hsqldb.Session.executeDirectStatement(Unknown Source) at // > org.hsqldb.Session.execute(Unknown Source) ... 7 more } }
Advertisement
Answer
So I found out what was causing the Exception, ucanaccsess does not support a direct insertion of data,so you need to specify the column of the inserted data so the only change to my code will be :
String query4="INSERT INTO Toy(ToyID,ToyName,Age,Price,Color) VALUES (6,'JB Truck',3,20.00,'Yellow')";