CODEWITHSUNDEEP
javasqlms-access
Adesh
Adesh

Reputation: 947

Number of query values and destination fields are not the same in ms access

I am trying to execute this sql insert statement below but encountered an error which is listed below. Would appreciate some advise, thanks.

 public void addSale(String saleDetails, String saleTotal) 
                        throws ClassNotFoundException, SQLException {

     Statement myStatement = getConnection();
     String sql = "INSERT INTO Sale (SaleDetails, SaleTotal) VALUES ('"+saleDetails+"','"+saleTotal+"')";

     myStatement.executeUpdate(sql);
     closeConnection();
  } 
}

Error:

Java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same. at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6956) at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7113) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3109) at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:337) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:287)

Here's the design view on ms-access:

enter image description here

Upvotes: 1

Views: 2677

Answers (3)

goggle pink
goggle pink

Reputation: 132

It's because of comma as a decimal separator on your system. value1 is 2.5 and value2 is 3.2 but you got 2,5,3,2 (you hope 2.5,3.2)

Upvotes: 0

JamesB
JamesB

Reputation: 7894

I think your insert should be:

String sql = "INSERT INTO Sale (SaleDetails, SaleTotal) VALUES (" + saleDetails + ", " + saleTotal + ")";

Also, you may need to convert saleTotal from a string to a number before doing the insert.

Upvotes: 0

Udo Held
Udo Held

Reputation: 12548

Do a System.out.println(sql); or use your logging framework and try that statement directly within access. It's possible that you have problems with the different data types over odbc. Otherwise the statement looks fine for me.

Maybe your variables contains ' and/or commas. That may cause problems as well if they aren't escaped. You should consider using paramenter binding.

Upvotes: 1

Related Questions