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,694 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 Post an If Statement in JSP 3 65
throw exception 21 67
difference between sorce folder and folder in eclipise 3 42
import as existing maven project 3 37
No single Antivirus application (despite claims by manufacturers) will catch or protect you from all Virus / Malware or Spyware threats. That doesn't stop you from further protecting yourself however - and this article is to show you how.
Adults who share images on social media aren’t the only ones who need to worry about their privacy. Our culture’s tendency to share every move and celebration affects the privacy of our children, too.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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