Column count doesn't match value count at row 1 - Java SQL

Question

I am getting the "Column count doesn't match value count at row 1" but I couldn't seem to fix it. I tried changing the quotation marks and data type but still no luck. Can anyone see where I've gone wrong?

public void actionPerformed(ActionEvent arg0) {
            if(!textField.getText().isEmpty() && !textField_1.getText().isEmpty() && !textField_2.getText().isEmpty() && !textField_3.getText().isEmpty() && !textField_4.getText().isEmpty() && !textField_5.getText().isEmpty() && !textField_6.getText().isEmpty() && !textField_7.getText().isEmpty())
            {
                String moduleName = textField.getText();
                String studId = textField_1.getText();
                String sFName = textField_2.getText();
                String sLName = textField_3.getText();
                int weekNo = Integer.parseInt(textField_4.getText());
                int lAttend = Integer.parseInt(textField_5.getText());
                int tAttend = Integer.parseInt(textField_6.getText());
                int wAttend = Integer.parseInt(textField_7.getText());

                lh.addStud(moduleName, studId, sFName, sLName , weekNo, lAttend, tAttend, wAttend);
            }
            else
            {
                lblNewLabel_4.setText("You have Missing Field");
            }
            fillDB();
           }
        });

Below is my query:

String aSQuery = "INSERT INTO Group6_Register VALUES ('" + moduleName + "', '" + studId + "', '" + sFName + "', '" +  sLName +"', '" +  weekNo + "', '" +  lAttend + "', '"  + tAttend + "', '" + wAttend + "')";
            statements = connection.prepareStatement(aSQuery);
            statements.executeUpdate();

I guess `PK` field of `Group6_Register` is omitted here – triclosan Nov 22 '12 at 14:37 — triclosan, Nov 22 '12 at 14:37
where is `prepareStatement` here? – triclosan Nov 22 '12 at 14:38 — triclosan, Nov 22 '12 at 14:38

PermGenError · Accepted Answer · 2012-11-22T14:40:37.990

4

"Column count doesn't match value count at row 1"

usually occurs when your columns defined in the values clause don't match your actual table columns.

say your table have 4 coulmns, col1 col2 col3 col4

now you want to only insert data into col2, col3, col4

your insert statement should be

Insert into table (col2, col3, col4) values(col2val, col3val, col3val)

However, i would strongly recommend you to use PreparedStatement while executing sql queries using JDBC.

   String query= "Insert into table (col2, col3, col4) Values(?,?,?)";
   Statement st = conn.preparedStatement(query);
   st.setString(1, col2value);
   st.setString(2, col3Val); 
   st.setString(3, col4Val);

edited Nov 22 '12 at 14:40

answered Nov 22 '12 at 14:38

PermGenError

45,977
8
87
106

Thank you, it worked. Very clear and helpful. One last thing, how can I refresh the JTable once I have inserted new data? I had to re-run it to see the update. – user1781232 Nov 22 '12 at 14:49
@user1781232 check thi slink http://stackoverflow.com/questions/3179136/jtable-how-to-refresh-table-model-after-insert-delete-or-update-the-data – PermGenError Nov 22 '12 at 14:50
When I put tableModel.fireTableDataChanged(); I get an error saying "Syntax error on token "fireTableDataChanged", Identifier expected after this token" – user1781232 Nov 22 '12 at 15:04
I would recommend you to first work it out yourself, if you can't figure it out post as a new question for help. But I am sure you can figure it out urself :) – PermGenError Nov 22 '12 at 15:08

score 0 · Answer 2 · answered Nov 22 '12 at 14:38

It happens because you are using the implicit type of INSERT statements. The server is expecting that you have provided all values for all columns but you don't that's why it threw an exception.

The best way to do is to explicitly declare the column name for which the values are bound to, ex.

INSERT INTO tableName (colA, colB, colC)
VALUES ('a','b','c')

you can only use this type of syntax when you have all specified values for all columns, for instance, you have a table which has 3 columns, you can then use this

INSERT INTO tableName  VALUES ('a','b','c')

score 0 · Answer 3 · answered Nov 22 '12 at 14:40

0

Just define the columns you insert explicitly

INSERT INTO table (col1,col2,coln) VALUES ( val1, val2, val3 )

Otherwise you have to set a value for each field

answered Nov 22 '12 at 14:40

Michel Feldheim

17,625
5
60
77

Column count doesn't match value count at row 1 - Java SQL

3 Answers3