CODEWITHSUNDEEP
javamysql
Prince
Prince

Reputation: 21

Column count doesn't match value count at row 1

Inserting data from java form into MySql database Details in table personal

but I getting the error Column count doesn't match value count at row 1

I'm using Netbeans 6.9, MySql 5.6

I have declared ID as int with primary key, Phone and mobile as int and all other as char.

 import java.sql.*;
 import javax.swing.*;

  public class Personal_Details extends javax.swing.JFrame{

   Connection conn;
   String url;
   Statement stmt;
   ResultSet rs;

  public javax.swing.table.DefaultTableModel tableview;
    public Personal_Details() {
    initComponents();
          tableview= new javax.swing.table.DefaultTableModel();
          jTable1 = new javax.swing.JTable(tableview);
           tableview.addColumn("ID");
           tableview.addColumn("Name");
           tableview.addColumn("Address");
           tableview.addColumn("Gender");
           tableview.addColumn("Education");
           tableview.addColumn("Phone");
           tableview.addColumn("Mobie");
           tableview.addColumn("DOB");
           jScrollPane1.setViewportView(jTable1);
      try{
      url = "jdbc:mysql:///Details";
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(url,"root","anand");
      stmt = conn.createStatement();

        }catch(Exception e){
     JOptionPane.showMessageDialog(null,e);
    }
    }

    private void btnSubmitActionPerformed(java.awt.event.ActionEvent evt) {                                          
    {
    try{
    stmt.executeUpdate("insert into
    personal(ID,Name,Address,Gender,Education,Phone,Mobile,DOB) values
    ("+tfIDNo.getText()+
         ",'"+tfName.getText()+"','"+tfAddress.getText()+"','"+tfGender.getText()+"','"
         +                                                                        
      "','"+tfEducation.getText()+"',"+tfPhone.getText()+","+tfMobile.getText()+",'"+
     tfDob.getText()+"')");


   rs = stmt.executeQuery("select * from personal where ID="+tfIDNo.getText()+" and   
   Name='"+tfName.getText()+"' and Addess='"+tfAddress.getText()+"'"
         + "' and Gender='"+tfGender.getText()+"' and  
    Education='"+tfEducation.getText()+"' and phone="+tfPhone.getText()+" "
         + " and Mobile="+tfMobile.getText()+" and DOB='"+tfDob.getText()+"'");
    }catch(Exception e){
   JOptionPane.showMessageDialog(null,e);
   }
   }
   }

   public static void main(String args[]) {
    java.awt.EventQueue.invokeLater(new Runnable() {
        public void run() {
            new Personal_Details().setVisible(true);
        }
    });
}

// Variables declaration - do not modify
private javax.swing.JButton btnClear;
private javax.swing.JButton btnClose;
private javax.swing.JButton btnSubmit;
private javax.swing.JScrollPane jScrollPane1;
private javax.swing.JTable jTable1;
private javax.swing.JLabel lblAddress;
private javax.swing.JLabel lblDob;
private javax.swing.JLabel lblDobFormat;
private javax.swing.JLabel lblEducation;
private javax.swing.JLabel lblEnterYourDetails;
private javax.swing.JLabel lblGender;
private javax.swing.JLabel lblIDNo;
private javax.swing.JLabel lblMobile;
private javax.swing.JLabel lblName;
private javax.swing.JLabel lblPhone;
private javax.swing.JTextField tfAddress;
private javax.swing.JTextField tfDob;
private javax.swing.JTextField tfEducation;
private javax.swing.JTextField tfGender;
private javax.swing.JTextField tfIDNo;
private javax.swing.JTextField tfMobile;
private javax.swing.JTextField tfName;
private javax.swing.JTextField tfPhone;
}


  Database - ID, Name, Address, Gender, Education, Phone, Mobile, DOB.

Upvotes: 1

Views: 4741

Answers (2)

Junaid Aslam
Junaid Aslam

Reputation: 84

@Prince Use PreparedStatement instead of simple Statement as this will increase performance of your program. Like this:

PreparedStatement Pstatement=Your_Connection_Object.prepareStatement("insert 
into mydb.Stock values(?,?,?,?,?,?,?,?)");

            //Specifying the values of it's parameter
Pstatement.setString(1,tfIDNo.getText());
Pstatement.setString(2,tfName.getText());
Pstatement.setString(3,tfAddress.getText());
Pstatement.setString(4,tfGender.getText());
Pstatement.setString(5,tfEducation.getText());
Pstatement.setString(6,tfPhone.getText());
Pstatement.setString(7,tfMobile.getText());
Pstatement.setString(8,tfDob.getText());

Pstatement.executeUpdate();

As you can see, we are passing parameter (?) for the values. Its value will be set by calling the setter methods of PreparedStatement.

Upvotes: 0

Simon Dorociak
Simon Dorociak

Reputation: 33515

Correct your INSERT statement with this:

String s = "insert into 
            personal(ID, Name,Address, Gender, Education, Phone, Mobile, DOB) 
            values (" + "'" + t.getText() + "','" + t.getText() + "','" 
            + t.getText() + "','" + t.getText() + "','" + t.getText() + "','"
            + t.getText() + "','" + t.getText() + "','" + t.getText() + "')";

replace my t.getText() with yours and now it should works.


Note: But i suggest to you to use PreparedStatement istead of Statement and use placeholders ?. This solution is more clean and more safe!(SQL injection) and more human-readable.

Upvotes: 1

Related Questions