Column count doesn't match value count at row 1 - mysql/JDBC

Question

I'm using java netbeans & mysql and i used "insert into select statement".

But when I run the program and input the data it always says "Column count doesn't match the value count at row 1"

Connection conn = null;
ResultSet rs = null;
PreparedStatement pst = null;

private void jButton5ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    String sql = "insert into tbl_addcharityroom1 values ('"+ jTextField10aw.getText() +"', (select charityWardID from tbl_addcharityward where diseaseCategory='"+ jComboBox1.getSelectedItem().toString() +"'))";
    try {
        pst = conn.prepareStatement(sql);  

        JOptionPane.showMessageDialog(null, "Saved");
        pst.execute();
    }
    catch(Exception e){
        JOptionPane.showMessageDialog(null, e);
    }
}

Can you output the SQL to see what is being sent, and also the table structure? — Teresa Carrigan, Jan 29 '14 at 12:02
You should always spell out the columns in the insert statement: `insert into foobar (col1, col2) values (1,2);` — , Jan 29 '14 at 12:04
@alya 3 columns ([PK]charityRoomID, charityRoomStatus, [FK]charityWardID) — Rima Touya, Jan 29 '14 at 12:07
no of columns in inserted table should match with the no.of columns from select stament or you have to specify the column names in the insert query in your case you can specify only one column in insert query as you are getting one value from select query — Aravind Kishore, Jan 29 '14 at 12:16

score 1 · Accepted Answer · answered Jan 29 '14 at 12:17

1

You have 3 column in your table and you need to enter in 2 columns, so try this:

INSERT INTO tbl_addcharityroom1 (charityRoomStatus, charityWardID)
VALUES ('"+ jTextField10aw.getText() +"', (select charityWardID from tbl_addcharityward where diseaseCategory='"+ jComboBox1.getSelectedItem().toString() +"'))";

answered Jan 29 '14 at 12:17

Alya'a Gamal

5,624
19
34

1

Or better yet: a prepared statement with placeholders instead of concatenating values into the query. – Mark Rotteveel Jan 29 '14 at 12:19
@Alya'a how about multiple FK ? 4 Columns ([PK]charityRoomID, charityRoomStatus, [FK]charityWardID, [FK]rateID) – Rima Touya Feb 01 '14 at 01:12
I'm not good in the Database ,but i have what i need to make a simple query ,and i know that you have to match the number of selected column in the statement with the data you enter , if you specify 2 column so each one need a value, and it's better to use a prepared statement – Alya'a Gamal Feb 02 '14 at 09:52

score 0 · Answer 2 · answered Jan 29 '14 at 12:02

0

The number of columns in the destination table does not match the number of columns you're selecting, which is 2.

That is what it says. So conclusion is that tbl_addcharityroom1 does not have 2 columns.

answered Jan 29 '14 at 12:02

Xabster

3,710
15
21

score 0 · Answer 3 · answered Jan 29 '14 at 12:12

MySQL is telling you that the number of columns in your query do not match the number of columns in the table. That should be a fairly easy fix but after you fix that you're still going to have a potentially nasty bug in this code.

What that bug is will depend on exactly how you have defined the tbl_addcharityroom1 table. I would assume by looking at this that you have multiple charity wards and those have a many to one relation with the categories table. This means that the select inside your insert statement may have multiple returned values. When this happens the query will fail. You are going to want to modify this code to handle that case. You can either look them all up ahead of time and perform an insert for each or you can use the CONCAT MySql command to put them all into a string which can be save as a discrete value.

Also, this code is very unsafe and not good enough for a production situation. AT LEAST, check to make sure jTextField10aw and jComboBox1 contain valid values before using them. — krowe, Jan 29 '14 at 12:17

Column count doesn't match value count at row 1 - mysql/JDBC

3 Answers3