et.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.1 data exception: numeric value out of range more

[Alfonso Pisani]

Hello,
I'm experiencing quite a strange problem with ucanaccess and Java. I have a table called Elenco_Aziende from which I extract all the record in a resultset. Elenco_Aziende is in a relation one to many with other two tables called Elenco_Autisti and Elenco_Veicoli via a field called Partita_IVA_Azienda that is also primary key in Elenco_Aziende table. After extracting all records from Elenco_Aziende I perform a loop for each value of Partita_IVA_Azienda and then open a new result set to try to read values in related fields of Elenco_Autisti and Elenco_Veicoli tables and do for each of them some operation. And here comes strange thing: as long as Partita_IVA_Azienda (that is defined as a text field in microsoft access DB) values are all the same length everything is ok when I try to read in Elenco_Autisti and Elenco_Veicoli tables, but if some of Partita_IVA_Azienda has a different length then I got error net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.1 data exception: numeric value out of range more precisely here is the nested loop scenario:

        Connection con = DriverManager.getConnection("jdbc:ucanaccess://"
            + filepath);

    String qry = "SELECT * FROM Elenco_Aziende";

    ResultSet rs = stmt.executeQuery(qry);
    String cognometest = "";
    String nometest ="";

    while (rs.next()) {

        String partitaiva = "Partita IVA: "
                + rs.getString("Partita_IVA_Azienda") + "\n\r";

        String partitaivazienda = rs.getString("Partita_IVA_Azienda");

        Statement stmtautisti = con.createStatement();

        System.out.println("Sto per eseguire la query per partita iva azienda = " + partitaivazienda + "\n\r");

        String qryautisti = "SELECT * FROM Elenco_Autisti WHERE Partita_IVA_Azienda="
                + partitaivazienda; /*!!!!! AND HERE WHEN I EXECUTE NEXT QUERY IS WHERE I GET THE EXCEPTION net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.1 data exception: numeric value out of range more!!!!!*/
        ResultSet rsautisti = stmtautisti.executeQuery(qryautisti);

        while (rsautisti.next()) {

        do something here

        }

        Statement stmtveicoli = con.createStatement();
        String qryveicoli = "SELECT * FROM Elenco_Veicoli WHERE Partita_IVA_Azienda="
                + rs.getString("Partita_IVA_Azienda");
        ResultSet rsveicoli = stmtveicoli.executeQuery(qryveicoli);

        while (rsveicoli.next()) {

        do something else here

        }

that is as soon as I execute the query String qryautisti = "SELECT * FROM Elenco_Autisti WHERE Partita_IVA_Azienda="+ partitaivazienda; for a different lenght value of Partita_IVA_Azienda I get the problem. I must say I even tried to export the database in a comma separated value and reimporting it in a brand new one... nothing to do and furthermore the problem seems to happen just for large numer of records in tables Elenco_Autisti (138 records) and Elenco_Veicoli (287 records), while seems not to happen for small number of records...Elenco_Aziende is small (no more than 10 records)...HELP needed urgently please!!!!

[Marco Amadei]

The solution, by Gord, on stackoverflow. Please Alfonso, use just one forum for time ;-).