Exception caught : java.sql.SQLException: Column count doesn't match value count at row 1

Question

 public void updateDeduction(String empId, String dedId, String dedname,String dedamount,String date) throws SQLException{
 //update the contributions
  stmt = conn.createStatement();
 String updateString ="INSERT INTO deductions (empId,dedId,dedName,dedAmount,dedDate) VALUES (";
  updateString +="'"+empId+"', ";
  updateString +="CURDATE(), " ;
  updateString +="'"+dedId+"'";
  updateString +="'"+dedname+"', ";
  updateString +="'"+dedamount+"')";


  stmt.executeUpdate(updateString);

  return;

I am getting error whenever I am clicking on the deduction tab please let me know what to do?

besides missing a comma after dedId,Why don't you insert values in the order they should be? empId->dedId->dedName->dedAmount->dedDate?You are missing dedDate. — ye9ane
– ye9ane, Commented Jan 5, 2013 at 6:09
There must be a way to do this that uses placeholders so that you're guaranteed to have things properly escaped and quoted. — mu is too short
– mu is too short, Commented Jan 5, 2013 at 6:10

hd1 · Accepted Answer · 2013-01-05 06:28:52Z

4

It is good practice to use PreparedStatement instead of Statement. It will help you to prevent sql injection attacks. Try to build PreparedStatement like -

String updateString ="INSERT INTO deductions (empId, dedId, dedName, dedAmount, dedDate) VALUES (?,?,?,?,?)";

    PreparedStatement preparedStatement = conn.prepareStatement(updateString);

    preparedStatement.setInt(1, empId);
    preparedStatement.setInt(2, dedId);
    preparedStatement.setString(3, dedName);
    preparedStatement.setDouble(4, dedAmount);
    preparedStatement.setDate(5, dedDate);

    preparedStatement .executeUpdate();

edited Jan 5, 2013 at 6:28

hd1

34.9k5 gold badges83 silver badges95 bronze badges

answered Jan 5, 2013 at 6:21

Avinash T.

2,3692 gold badges16 silver badges23 bronze badges

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

Comments

hd1 · Accepted Answer · 2013-01-05 06:27:29Z

1

The proper way to do this is to use PreparedStatement. I've rewritten OPs code below to show how this is done:

public void updateDeduction(String empId, String dedId, String dedname,String dedamount,String date) throws SQLException{
    //update the contributions
    PreparedStatement updateString = conn.prepareStatement("INSERT INTO deductions (empId,dedId,dedName,dedAmount,dedDate) VALUES (?, ?, ?, ?, ?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); // you passed in CURDATE() instead of using one of your parameters.
    updateString.setString(1, empId);
    updateString.setString(2, dedId);
    updateString.setString(3, dedName);
    updateString.setString(4, dedAmount);
    updateString.setString(5, date); // you were missing this line
    if (updateString.executeUpdate() == 1) return;
    else throw new RuntimeException("Update failed");
}

Some comments on my code which should make it clearer as to why I used this style. The if line exists to ensure the insert was successful, as executeUpdate is defined to return the number of rows inserted in an insert context. Also, you must declare your statements as updatable if they change rows at all. Hope this helps, and if you need further assistance/explanation, please leave a comment here.

answered Jan 5, 2013 at 6:27

hd1

34.9k5 gold badges83 silver badges95 bronze badges

1 Comment

Mark Rotteveel Over a year ago

The addition of , ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE to the prepareStatement method is unnecessary: this query does not produce a resultset. This is only meant for ResultSets that allow you to update selected rows throught the ResultSet.

DWright · Accepted Answer · 2013-01-05 08:03:41Z

0

You don't have a comma in updateString +="'"+dedId+"'";

Also the order of the values you concatenate into the string do not match the order of fields in the INSERT INTO (...)

The fix would be something like

  updateString +="'"+empId+"', ";
  updateString +="'"+dedId+"', "; //Or updateString += dedId+", ";  If dedId is an integer value in the database.
  updateString +="'"+dedname+"', ";
  updateString +="'"+dedamount+"', ";
  updateString +="CURDATE())" ;

Notice I've re-ordered the string concatenates to match the INSERT INTO (...) field order, and all the fields have commas after them except the last one.

edited Jan 5, 2013 at 8:03

answered Jan 5, 2013 at 6:06

DWright

9,5594 gold badges39 silver badges57 bronze badges

4 Comments

DWright Over a year ago

Yes, and do use PreparedStatement like some of the other answerers said. I was just helping with the syntax error. But they are going a step further by showing what is also conceptually correct, and you can see how doing this would have helped avoid the syntax error in the first place.

XAH Over a year ago

Exception caught : java.sql.SQLException: Incorrect integer value: 'null' for column 'dedId' at row 1

DWright Over a year ago

If dedId is an integer in the database, then the line should be: updateString += dedId+", ";

DWright Over a year ago

Basically, anything that is a string in the database needs to be INSERT INTO (stringfield) VALUES('stringvalue'). But anything that is not a string cannot have the ' in VALUES: INSERT INTO (intField) VALUES(123) is an example

Collectives™ on Stack Overflow

Exception caught : java.sql.SQLException: Column count doesn't match value count at row 1

3 Answers 3

Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related