Solved

JDBC; mySQL; ErrorMessage "Column count doesn't match value count at row 1" ; Cannot enter data into table

Posted on 2004-08-12
4
2,699 Views
Last Modified: 2009-12-16
Entering data into database table fails with following error message:

SQLException: General error,  message from server: "Column count doesn't match value count at row 1"
SQLState: S1000
VendorError: 1136

Shouldn't address_id increment automatically? Here is the description of the table:

mysql> DESCRIBE address;
+--------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null   | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| address_id| bigint(20) unsigned |      | PRI | NULL    | auto_increment |
| country      | varchar(20)         |      |     |         |                |
| district     | varchar(20)         | YES  |     | NULL    |                |
| constituency | varchar(20)         | YES  |     | NULL    |                |
| location     | varchar(20)         | YES  |     | NULL    |                |
| sublocation  | varchar(20)         | YES  |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
 
And here is my DBHandler:

public class DBHandlerAddress extends HttpServlet {
       
      public void doPost (HttpServletRequest request,
      HttpServletResponse response) {
      try { // OBTAIN A DATABASE CONNECTION
         
          // Step 1: Load the JDBC driver          
          Class.forName("com.mysql.jdbc.Driver");
             
          // Step 2: Establish a connection to the database          
       
          Connection conn = DriverManager.getConnection(
          "jdbc:mysql://localhost/rtpmKenyaDB","rmwijage", "nahumCh2");
          String sql = "INSERT INTO address( country, district," +
               "constituency, location, sublocation) VALUES (?, ?, ?, ?, ?)";
          PreparedStatement ps = conn.prepareStatement(sql);
         
          // Retreave the bean properties to be inserted into table rtpm_user
          AddressFormBean f = (AddressFormBean) request.getAttribute("formHandler");
         
          String cntry = f.getCountry();
          String dstrct = f.getDistrict();
          String cnstncy = f.getConstituency();
          String lcn = f.getLocation();
          String sblcn = f.getSublocation();
           
          // Set the parameters in the prepared statement
          ps.setString(1, id);
          ps.setString(2, cntry);
          ps.setString(3, dstrct);
          ps.setString(4, cnstncy);
          ps.setString(5, lcn);
          ps.setString(6, sblcn);
         
          // Insert the row in the address table
          ps.executeUpdate();

          // Close the connection
          ps.close();
          conn.close();    
      }
      catch (SQLException e) {
          // handle any errors
          System.out.println("SQLException: " + e.getMessage());
          System.out.println("SQLState: " + e.getSQLState());
          System.out.println("VendorError: " + e.getErrorCode());
          }
      catch (Exception e) {
      e.printStackTrace();
      }
     
      // Send the request back to success_registration.jsp
      try {
          getServletConfig().getServletContext().
            getRequestDispatcher("/success_address.jsp").
             forward(request, response);
      }
      catch (Exception e){}
     
   }
}


// I do appreciate your kind help.
0
Comment
Question by:Rrugemalira
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:bobbit31
bobbit31 earned 200 total points
ID: 11788006
yes... so remove this:

          ps.setString(1, id);

and update the rest of the indices accordingly
0
 
LVL 3

Accepted Solution

by:
haobaba1 earned 250 total points
ID: 11788242
for the auto increment field you need to call ps.setNull(1, Types.Int);

So for the table you describe you should have 6 parameters to insert a new record. lets say:

          String sql = "INSERT INTO address(address_id, country, district," +
               "constituency, location, sublocation) VALUES (?,?, ?, ?, ?, ?)";
          PreparedStatement ps = conn.prepareStatement(sql);
         
          // Retreave the bean properties to be inserted into table rtpm_user
          AddressFormBean f = (AddressFormBean) request.getAttribute("formHandler");
         
          String cntry = f.getCountry();
          String dstrct = f.getDistrict();
          String cnstncy = f.getConstituency();
          String lcn = f.getLocation();
          String sblcn = f.getSublocation();
           
          // Set the parameters in the prepared statement
          ps.setNull(1,Types.Int); //tell it the type of column that it is dealing with
          ps.setString(2, cntry);
          ps.setString(3, dstrct);
          ps.setString(4, cnstncy);
          ps.setString(5, lcn);
          ps.setString(6, sblcn);

0
 
LVL 3

Assisted Solution

by:haobaba1
haobaba1 earned 250 total points
ID: 11788272
Also to get the id that was just created you can use this query after your insert call:

queryLastInsertId = new String("SELECT LAST_INSERT_ID()");

This query will return the last primary that was created with teh connection that you currently have open so it is thread safe in teh sense that even other records are inserted into other tables with different connections you will still get the last primary key generated by teh connection you are using.

0
 
LVL 11

Assisted Solution

by:sompol_kiatkamolchai
sompol_kiatkamolchai earned 50 total points
ID: 11789884
Yes. bobbit31 is right. you use prepared statement with just 5 parameter but you try to set 6 parameter.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to exclude a file using regex 5 123
thymeleaf natural templating vs JSP 2 202
login jsp example 24 105
DTD and JAVA versions 1 57
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Invest in your employees with these five simple steps to improve employee engagement and retention.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question